Submit My Question - Recent Questions - Recent Answer

Passing XML from Sql Server 2000 in stored procedure

Hi,
I m wanting a stored procedure to pass an XML string from a stored procedure to my application. Whats the best way to achieve this?

Hope you can help

Thanks

Paul

[179 byte] By [shax] at [2008-1-7]

«« Software Testing Training
»» dataset and transaction

# 1

Give a look to FOR XML in books online. Here is an example using FOR XML AUTO:

Code Snippet

declare @aTable table
( rowId int,
name varchar(25)
)
insert into @aTable
select 1, 'Herman Rubble' union all
select 2, 'Fred Munster' union all
select 3, 'Barney Flintstone'

select rowId,
Name
from @aTable
for xml auto

/*
XML_F52E2B61-18A1-11d1-B105-00805F49916B
--
<_x0040_aTable rowId="1" Name="Herman Rubble"/><_x0040_aTable rowId="2" Name="Fred Munster"/><_x0040_aTable rowId="3" Name="Barney Flintstone"/>
*/

XML handling in SQL Server 2005 is significantly better than SQL Server 2000. If XML is going to play a major part of your database implementation I would suggest upgrading.

KentWaldropJl07 at 2007-10-2 >

top of Msdn Tech,SQL Server,Transact-SQL...

# 2

Hi,
Thanks for that however my question should have been how do I actually pass the XML as a parameter

e.g. if I want to pass a string as an output parameter i can simply write:

.... @MyString varchar(10) output.......

in teh stored procedure declaration and set the output value appropriately.

How does this wotk when using For XML Auto ?

Thanks

shax at 2007-10-2 >

# 3

In SQL Server 2000 you must pass XML either as NVARCHAR(someLength) or NTEXT and then use stored procedures to process your XML. Here is an example from a question asked earlier today:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1898927&SiteID=1

KentWaldropJl07 at 2007-10-2 >

# 4

Hi,
I need the information to be passed from the SQL Server to the Application. In the example you gave the XML string is being passed into the stored procedure. I need the XML to be passed from the stored procedure to the applicatio by using an outout paramater or other means?

Thanks

shax at 2007-10-2 >

# 5

The limit of what I know for SQL Server 2000 is that I only know how to return the information as part of a result set; I do not know how to load the information into an output parameter. Hopefully, someone else can answer this question.

KentWaldropJl07 at 2007-10-2 >

# 6

I am trying to use FOR XML AUTO clause in Sql Server 2000.

My requirement is to use this clause and assign the result set into a parameter.Like

Declare @ss XML

set @ss=(SELECT * FROM TB_TMGroup WHERE TM_GroupID = 29 FOR XML AUTO,ELEMENTS, BINARY BASE64)
print @ss

So that i can use this paramer "@ss" inside a insert statement.

Can any one help on this ?

Thanks.

JagabandhuSahoo at 2007-10-2 >

# 7

check this post : http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1917124&SiteID=1

Manivannan.D.Sekaran at 2007-10-2 >

# 8

This is not possible in SQL server 2000 but there are a few workarounds.

(1) Return your result to your .NET application using a select statement. In your app create a dataset. There is a method on he dataset called .getXML (or something like that) which will return a string. Use this string in your insert statement.

(2) Use your select statement "SELECT * FROM TB_TMGroup WHERE TM_GroupID = 29 FOR XML AUTO,ELEMENTS" etc and use a xn XMLDataReader within your app to loop through the generated XML to create a string (Havent quite mustered how to do this yet but its a starting point)

shax at 2007-10-2 >

# 9

it is not a work-around, it is a perfect & solid solution when you work with SQL Server 2000 & ADO.NET.

In ADODB it is different approach to get the XML data (stream & etc.).

I recommand to use the XMLDataReader, which is faster than the DATASET's GetXML.

You got the perfect solution..! Smile