Geek Answers Handbook

Create a single XML query from multiple SQL Server tables

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

I know that I can query several tables in a stored procedure and get a DataSet in my .NET application, which can be easily saved as XML. However, I am trying to do something similar in 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
+4
source share
3 answers

If you want them only one by one, 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')

This returns an XML something like:

 <root> <YElements> <Y> .... </Y> <Y> .... </Y> ...... </YElements> <AElements> <A> .... </A> <A> .... </A> ...... </AElements> </root>
+10
source 
 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
+1
source

Convert XML table to 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') )

XML Return How is it

  <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>
0
source

More articles:


All Articles