How do I use multiple select statements in a output in a stored procedure?

Question

I have a stored procedure that I declared a variable as an output. In this procedure, I need to have multiple select statements. I have no problem creating multiple select statements when they are NOT in the same output, but I cannot get this one figured out.

After the part of the procedure that reads: SET @XmlOutput = (, I would like to add more select statements.

Thank you for your help!

ALTER PROC [dbo].[uspCreateGeoRssFile]
AS
  DECLARE @XmlOutput xml
  SET @XmlOutput = (
      SELECT     
         CustomerName AS Title, esn, longitude, latitude, Device_Alias AS Description
      FROM         
         LS_Variable_Listing AS item
      WHERE     
         (triggers <> N'No Event Recorded') 
         AND (CustomerName = N'Hiland Partners, LLC')
      ORDER BY timestamp_utc DESC
      FOR XML AUTO, ROOT('channel'), ELEMENTS)

  SELECT @XmlOutput

GSerg · Accepted Answer · 2011-12-28 16:41:32Z

1

Not sure what you want to do here, but you can append one xml result to another:

declare @x xml;
declare @some_more_xml xml;

....

set @x = (select @x, @some_more_xml for xml path(''), type);

This can also be inline:

set @x = (
  select
    (select 1 as foo for xml raw, root('root1'), type),
    (select 2 as bar for xml raw, root('root2'), type)
  for xml path(''), type
);

If you want to add elements "inside" some existing xml, then the easiest way to do so is to not introduce the root clause until very end:

set @x = (
  select
    (select ... for xml auto, type),
    (select ... for xml auto, type)
  for xml path(''), root('channel'), type
);

edited Dec 28, 2011 at 16:41

answered Dec 28, 2011 at 16:32

GSerg

78.8k18 gold badges173 silver badges377 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

John Over a year ago

I need to add a couple more elements to the xml file, that are at the same level as the "item" element above. These are just static elements that will not change. I am sorry for not makng more since.

Collectives™ on Stack Overflow

How do I use multiple select statements in a output in a stored procedure?

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related