Stored procedure Returning XML String

• david James Naylor

  Ten Centuries

  Points: 1026

  More actions

  September 8, 2004 at 7:10 am

  #89348

  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

  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

• Andrew Ebert

  SSC Veteran

  Points: 230

  More actions

  September 8, 2004 at 8:55 pm

  #522223

  I assume you are calling the procedure through ADO or using the SQLXML ISAPI.  The result is streamed out, so as long as the text streamed out looks like XML it will work.  Below, will give you the desired results, without going through the pain of tying in the start and end tags into a UNION.

  CREATE PROCEDURE sp_GetAudioScript

  AS

  SELECT '<AudioScriptXML>'

  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

  SELECT '</AudioScriptXML>'

  GO

• david James Naylor

  Ten Centuries

  Points: 1026

  More actions

  September 9, 2004 at 2:54 am

  #522249

  Thanks for the reply, The code you have provided me with only returns the first Select statement so when it is run the only thing that appears is  <AudioScriptXML>. I will be calling this SP from VB.NET if that is any help.

   

  Regards

   

  Dave

• Andrew Ebert

  SSC Veteran

  Points: 230

  More actions

  September 9, 2004 at 4:37 pm

  #522414

  Never having used ADO.NET I cannot comment on its behaviour.  What I can do though, is provide (hopefully) the correct non-lazy way of performing the UNION for you.

  Note that even though you are using the FOR XML statement SQL Server writes all the data to a single table (so all SELECT statements must have same number of columns) and SQL Server then parses into an XML document.  The first SELECT statement is always used to set the idenity of each column.  If columns are not used in further down SELECT statements you set them to NULL.

  I hope this works.

  SELECT

    1      AS Tag,

    NULL AS Parent,

    NULL AS [AudioScriptXML!1!element],

    NULL AS [Audio!2!ID],

    NULL AS [Audio!2!ActorID!Element],

    NULL AS [Audio!2!SpecialInstructions!Element],

    NULL AS [Audio!2!Script!Element]

  UNION ALL

  SELECT

    2         AS Tag,

    1         AS Parent,

    NULL,

    [ID],

    act,

    auI,

    ItemValue

  FROM CustomAudioTable

  WHERE auS = 1

  ORDER BY [ID]

  FOR XML EXPLICIT

• Luigi Visintin

  SSC Eights!

  Points: 822

  More actions

  September 9, 2004 at 4:52 pm

  #522417

  In VB, when you create a Recordset in fact it can contain SEVERAL recordsets, one appended after the other, so you can read all those data with:

  dim allXml as string

  allXml = ""

  set rs = conndb.Execute("yor stored proc")

  do while not(rs.eof)

     ' process first recordset (header)

     allXml = allXml & rs.fields(0)

     rs.movenext

  loop

  set rs = rs.NextRecordset

  do while not(rs.eof)

     ' process middle recordset (real XML data)

     allXml = allXml & rs.fields(0)

     rs.movenext

  loop

  set rs = rs.NextRecordset

  do while not(rs.eof)

     ' process last recordset (footer)

     allXml = allXml & rs.fields(0)

     rs.movenext

  loop

  HTH

• david James Naylor

  Ten Centuries

  Points: 1026

  More actions

  September 10, 2004 at 2:30 am

  #522463

  Sorry to be a pain, but the code does not work, it says 'Invalid Column name ID' I should have made this clearer. For testing purposes i have not implmented this SP in VB.NET yet and am testing in Enterprise manager. I know how to return the string, just not how to create it properly.

  Thanks again

  Dave

• david James Naylor

  Ten Centuries

  Points: 1026

  More actions

  September 10, 2004 at 2:46 am

  #522468

  It's ok now, I have it working, The Order By was breaking the SP.

   

  Thanks for the help

  Dave

• Andrew Ebert

  SSC Veteran

  Points: 230

  More actions

  September 10, 2004 at 2:48 am

  #522471

  Good to see you got it working.