Hi all;

This one has me stumped:


--I have a table:

CREATE TABLE [dbo].[MyTestTable] (
[RowID] [int] IDENTITY (1, 1) NOT NULL ,
[CompName] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL
) ON [PRIMARY]
GO

--It has data in it:

Insert into MyTestTable (CompName) Values ('Comp1')
Go
Insert into MyTestTable (CompName) Values ('Comp2')
Go
Insert into MyTestTable (CompName) Values ('Comp3')
Go
Insert into MyTestTable (CompName) Values ('Comp4')
Go
Insert into MyTestTable (CompName) Values ('Comp5')
Go

--Then I select the data that I need formatted for XML

Select
RowID as [id],
CompName as n
From
MyTestTable as r
For XML Auto




So far so good right? Here's the catch. They want me to limit the XML
returned to a specified size limit determined by an input parameter. I
think that in order to do this I will need to put the XML output into a
local variable and then truncate it at (or near) the file size limit.

I was hoping to do something like this:

Set VARIABLENAME = (
Select
RowID as [id],
CompName as n
From
MyTestTable as r
For XML Auto
)


But this generates an Syntax error near 'XML'. Then I tried this:

Select into #TempTable (
Select
RowID as [id],
CompName as n
From
MyTestTable as r
For XML Auto) as field1

But this also generates the same error.


Does anyone have any ideas as to how to proceed? Please?

The end result here is that when the Stored procedure is executed it can
generate an XML file that is <= to the file size requested.










MS Sql Server LazyDBA home page