Storing the table data in the XML format into a variable

[manohars]

Is it possible to get the table's data in the XML format, using FOR XML, and store it in a variable. How can this be done.

Thanks in Advance,
Manohar

 

[philhege]

Sure it is, but what technology are you using to store the variable?

PH

 

[manohars]

I want to declare a variable in stored procedure and assign the XML to this variable.

Thanks,
Manohar

 

[philhege]

Provided the XML comes out in one field (and I believe it does), you could DECLARE a variable and SELECT its value from the SELECT...FOR XML statement. But what if the result is larger than 8K?

Phil Hegedusich
Senior Programmer/Analyst
IIMAK

http://www.iimak.com

-----------
I'm not as think as you confused I am.
-----------
Flabbergasted (a.): Amazed at how much weight one has gained.
-----------
Oyster (n.): One who sprinkles their conversation with Yiddish expressions.

 

[manohars]

Since the row size will not go more that 8 K, we shall assume that it is less than 8 K. What is the syntax for this?

Thanks,
Manohar

 

[philhege]

OK, upon further review (and straight from BOL, which you should check out):
<QUOTE>
FOR XML cannot be used in a selection that requires further processing in a stored procedure.

FOR XML cannot be used with cursors.

Generally, FOR XML cannot be used for any selections that do not produce direct output to the Microsoft® SQL Server™ 2000 client.
</QUOTE>
Meaning that you can't process the result in a stored procedure. You have to process it in a client (.NET, VB, whatever) that connects to the SQL instance.

Good luck,


Phil Hegedusich
Senior Programmer/Analyst
IIMAK

http://www.iimak.com

-----------
I'm not as think as you confused I am.
-----------
Flabbergasted (a.): Amazed at how much weight one has gained.
-----------
Oyster (n.): One who sprinkles their conversation with Yiddish expressions.

 

[manohars]

Is it possible to pass the select statement with FOR XML as parameter to sp_xml_preparedocument? I tried this but couldn't execute the SQL Statement?

Thanks,
Manohar

 

[philhege]

Read my last post. Then read Books On Line. Any result of SELECT ... FOR XML is UNAVAILABLE for further processing in the SQL memory space. You can't use the result in an SP. You can't pass the result to an SP call as a parameter. You have to call, retrieve, and process the result with a client application like .NET.

Hopefully more internal XML processing will be available with Yukon or the next buzzword project name version of SQL Server. You just can't do it now.

Phil Hegedusich
Senior Programmer/Analyst
IIMAK

http://www.iimak.com

-----------
I'm not as think as you confused I am.
-----------
Flabbergasted (a.): Amazed at how much weight one has gained.
-----------
Oyster (n.): One who sprinkles their conversation with Yiddish expressions.