Create single XML from SQL Server multiple tables query

Question

I would like to query multiple tables from SQL Server 2005 and create a single XML document and do this in a stored procedure.

I know that I can query multiple tables within a stored procedure and get a DataSet in my .NET application that can be easily saved as XML. However, I'm trying to do something similar within the context of a stored procedure.

Essentially I want to do something like this:

declare @x xml
select @x = x.result
from (select y.* from tabley y for xml path('y')
      union
      select a.* from tablea a for xml path('aa')
     ) as x
select @x

marc_s · Accepted Answer · 2012-08-09 16:10:55Z

18

If you want them just one after the other, you can try something like this:

SELECT
    (SELECT y.* FROM dbo.TableY FOR XML PATH('y'), TYPE) AS 'YElements',
    (SELECT a.* FROM dbo.TableA FOR XML PATH('aa'), TYPE) AS 'AElements'
FOR XML PATH(''), ROOT('root')

That return an XML something like:

<root>
   <YElements>
     <Y>
       ....
     </Y>
     <Y>
       ....
     </Y>
      ......
   </YElements>
   <AElements>
     <A>
       ....
     </A>
     <A>
       ....
     </A>
      ......
   </AElements>
</root>

answered Aug 9, 2012 at 16:10

marc_s

760k186 gold badges1.4k silver badges1.5k bronze badges

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

1 Comment

zurebe-pieter Over a year ago

What if I wanted to have them in the same sequence?

max pleaner · Accepted Answer · 2017-03-14 19:54:52Z

SELECT                                                               -- Root Starts
   (SELECT  '1' AS ErrorCode FOR XML PATH(''), TYPE) AS 'Results',   -- Level 1 Starts
       (select                                                       -- Level 2 Starts
             (select      '1'         CustomerID,                    -- Level 2 Detail Starts
                          'John'      CustomerName,
                          'Doe'       CustomerLastname,
                          'Y'         Active
              for xml path('Customers'), type) AS 'Customer'         -- Level 2 Detail Ends
       for xml path(''), TYPE) AS 'Response'                         -- Level 2 Ends
   FOR XML PATH('')                                                  -- Level 1 Ends
,ROOT('BaseXML')                                                     -- Root Ends

Pinaki Mukherjee · Accepted Answer · 2014-02-24 11:39:12Z

Convert Table XML in sql server.

Declare @RESULTXML XML
Declare @SMS_REGISTER TABLE([id] VARCHAR(30),[status] VARCHAR(30))
Declare @EMAIL_REGISTER TABLE([id] VARCHAR(30),[status] VARCHAR(30))
Declare @ODP_REGISTER TABLE([id] VARCHAR(30),[status] VARCHAR(30))

Select @RESULTXML =( 
SELECT (SELECT * FROM @SMS_REGISTER FOR XML PATH('sms'), TYPE) AS 'smss',
         (SELECT * FROM @EMAIL_REGISTER FOR XML PATH('email'), TYPE) AS         'emails',
         (SELECT * FROM @ODP_REGISTER FOR XML PATH('odp'), TYPE) AS 'odps'
    FOR XML PATH('subroot'), ROOT('root') )

Return XML Like this

    <root>
       <subroot>
        <smss>
          <sms>
            <id>NT0000000020</id>
            <status>registered</status>
          </sms>
          <sms>
            <id>NT0000000021</id>
            <status>registered</status>
          </sms>
          <sms>
            <id>NT0000000022</id>
            <status>registered</status>
          </sms>
          <sms>
            <id>NT0000000023</id>
            <status>registered</status>
          </sms>
        </smss>
        <emails>
          <email>
            <id>NT0000000024</id>
            <status>registered</status>
          </email>
          <email>
            <id>NT0000000025</id>
            <status>registered</status>
          </email>
        </emails>
      </subroot>
     </root>

Collectives™ on Stack Overflow

Create single XML from SQL Server multiple tables query

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related