Stored Procedure, Creating and Returning XML

Last Post 08 Sep 2006 07:54 AM by ajm4961. 1 Replies.

Author

Messages

davenaylor2000New Member

08 Sep 2004 05:09 AM

Hi All, I have looked at this discussion for an answer to a problem i have with Stored Procedures and returning XML. I have particularly tried ti implment what Kreynolds said in his thread.So here is the problem,I wish to create an XML string that looks something like the following from my stored procedures.

ID AS [Audio!1!ID],act AS [Audio!1!ActorID!Element],auI AS [Audio!1!SpecialInstructions!Element],ItemValue as [Audio!1!Script!Element]FROM CustomAudioTable WHERE auS = 1 ORDER BY IDFOR XML EXPLICITGO

However this does not create a root node that opens and the start and closes at the end.So i am trying thisCREATE PROCEDURE sp_GetAudioScriptASSELECT 1 AS Tag,null AS Parentunion allSELECT2 AS Tag,1 as Parent,ID AS [Audio!1!ID],act AS [Audio!1!ActorID!Element],auI AS [Audio!1!SpecialInstructions!Element],ItemValue as [Audio!1!Script!Element]FROM CustomAudioTable WHERE auS = 1 ORDER BY IDFOR XML EXPLICITGO

and i am told in an error message that the Column ID is invalid.I have never used xml within stored procedures before and am a little confused as to how to get the desired results.Any help would be great.

Thanks, Dave

ajm4961New Member

08 Sep 2006 07:54 AM

Hi

You need to use the following format . . .
CREATE PROCEDURE mysproc @state varchar(2) AS
select '<root>';
select * from Customers
where Region=@state
for xml auto;
select '</root>'