Stored Procedure, Creating and Returning XML

Last Post 08 Sep 2006 06:54 AM by ajm4961. 1 Replies.
AddThis - Bookmarking and Sharing Button Printer Friendly
  •  
  •  
  •  
  •  
  •  
Sort:
PrevPrev NextNext
You are not authorized to post a reply.
Author Messages
davenaylor2000
New Member
New Member

--
08 Sep 2004 04: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.


<AudioScriptXML>
<Audio ID=""0"">
<ActorID>1</ActorID>
<SpecialInstructions>Speak in an Irish Accent</SpecialInstructions>
<Script>hello</Script>
</Audio>
<Audio ID=""1"">
<ActorID>1</ActorID>
<SpecialInstructions>Wee bonnie scot talk</SpecialInstructions>
<Script>david</Script>
</Audio>
</AudioScriptXML>



I have the following stored procedure at present.....

CREATE PROCEDURE sp_GetAudioScript
AS
SELECT 1 AS Tag,
null 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 ID
FOR XML EXPLICIT
GO

However this does not create a root node that opens and the start and closes at the end.
So i am trying this
CREATE PROCEDURE sp_GetAudioScript
AS
SELECT 1 AS Tag,
null AS Parent
union all
SELECT
2 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 ID
FOR XML EXPLICIT
GO

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
ajm4961
New Member
New Member

--
08 Sep 2006 06: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>'

GO
You are not authorized to post a reply.

Acceptable Use Policy