Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 


 
     
Recent Posts
   
Popular Topics
Home   
Search
    Members    Calendar    Who's On

Home » Programming » XML » Returning Multiple Unrelated Tables in Single...
Add to briefcase

Returning Multiple Unrelated Tables in Single XML Output From Stored Proc Expand / Collapse
Rate Topic
Display Mode
Topic Options
Author
Message
blugecko
Posted Friday, October 26, 2012 3:25 PM


Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, February 05, 2013 6:01 AM
Points: 11, Visits: 33
Hi There,

I'm quite inexperienced in returning XML from SQL and have only been using single tables to return normal XML in the past.
Now, however, I need to return multiple tables (related or unrelated) in a single XML File for Exporting to another program.

Something like:

SELECT * FROM Products
SELECT * FROM Colours
SELECT * FROM Sizes
FOR XML AUTO, ROOT, ELEMENTS

Obviously the UNION operator is not going to work here so how would I need to write my T-SQL statement to return the following XML Hierarchy?

<root>
   <Products>
      <...>
   </Products>
   <Colours>
      <...>
   </Colours>
   <Sizes>
      <...>
   </Sizes>
</root>

Any help will be appreciated. I've scoured the internet using keywords I think would return some result but, alas, clearly I'm not using the right keywords so I'm hoping someone could point me in the right direction or perhaps even be so kind as to post the solution and save me from forced medical early retirement...

Thanks in advance.

Kind Regards,
Riaan
Post #1377879
Sean Lange
Posted Friday, October 26, 2012 3:39 PM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Friday, May 17, 2013 8:46 AM
Points: 8,547, Visits: 8,204
riaan-777462 (10/26/2012)
Hi There,

I'm quite inexperienced in returning XML from SQL and have only been using single tables to return normal XML in the past.
Now, however, I need to return multiple tables (related or unrelated) in a single XML File for Exporting to another program.

Something like:

SELECT * FROM Products
SELECT * FROM Colours
SELECT * FROM Sizes
FOR XML AUTO, ROOT, ELEMENTS

Obviously the UNION operator is not going to work here so how would I need to write my T-SQL statement to return the following XML Hierarchy?

<root>
   <Products>
      <...>
   </Products>
   <Colours>
      <...>
   </Colours>
   <Sizes>
      <...>
   </Sizes>
</root>

Any help will be appreciated. I've scoured the internet using keywords I think would return some result but, alas, clearly I'm not using the right keywords so I'm hoping someone could point me in the right direction or perhaps even be so kind as to post the solution and save me from forced medical early retirement...

Thanks in advance.

Kind Regards,
Riaan


I would split this into three procs instead of trying to do this in a single one.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Post #1377886
blugecko
Posted Friday, October 26, 2012 3:49 PM


Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, February 05, 2013 6:01 AM
Points: 11, Visits: 33
Thanks for the quick reply Sean.

The above is an example only, there are 14 tables in total and the end-user will be dragging and dropping the xml file into a VB application.

I couldn't imagine sending 14 XML files and the user dropping 14 files onto the app on every update.

I could join the xml files during the export, but I'm very sure there must be a simpler solution from SQL.

I appreciate your input

Kind Regards,
Riaan
Post #1377887
blugecko
Posted Saturday, October 27, 2012 1:14 PM


Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, February 05, 2013 6:01 AM
Points: 11, Visits: 33
Came right with guidance from various sources. Below the solution in case anyone ever wants to do something similar...

DECLARE @TempExportTable TABLE
(
  Products XML,
  Colours XML,
  Sizes XML
)

INSERT INTO @TempExportTable VALUES
(
	(SELECT * FROM Products FOR XML AUTO, ELEMENTS),
	(SELECT * FROM Colours FOR XML AUTO, ELEMENTS),
	(SELECT * FROM Sizes FOR XML AUTO, ELEMENTS)
)

SELECT 
       Products as '*',
       Colours as '*',
       Sizes as '*' 
from @TempExportTable 
FOR XML PATH('ExportList')
Post #1377977
sharon.chapman7
Posted Monday, November 19, 2012 10:48 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, November 19, 2012 2:52 PM
Points: 2, Visits: 3
Hi,
Can you tell me how to write this code if I only want certain fields from each table? I get errors when I try to select a few fields to insert into the temp table.

Thanks,
Sharon

PS thanks for posting this code, it was really helpful and I didn't see where anyone else posted code to combine two unrelated tables into one xml file.
Post #1386504
Sean Lange
Posted Monday, November 19, 2012 10:56 AM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Friday, May 17, 2013 8:46 AM
Points: 8,547, Visits: 8,204
sharon.chapman7 (11/19/2012)
Hi,
Can you tell me how to write this code if I only want certain fields from each table? I get errors when I try to select a few fields to insert into the temp table.

Thanks,
Sharon

PS thanks for posting this code, it was really helpful and I didn't see where anyone else posted code to combine two unrelated tables into one xml file.


I was able to change the select with no problem. All you have to do is change the columns for any of the select statements. What is the error you are getting?

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Post #1386509
blugecko
Posted Monday, November 19, 2012 11:33 AM


Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, February 05, 2013 6:01 AM
Points: 11, Visits: 33
sharon.chapman7 (11/19/2012)
Hi,
Can you tell me how to write this code if I only want certain fields from each table? I get errors when I try to select a few fields to insert into the temp table.

Thanks,
Sharon

PS thanks for posting this code, it was really helpful and I didn't see where anyone else posted code to combine two unrelated tables into one xml file.


Hi Sharon,
I would simply list the columns in each of the SELECT statements.

For instance (taking my example):

DECLARE @TempExportTable TABLE
(
  Products XML,
  Colours XML,
  Sizes XML
)

INSERT INTO @TempExportTable VALUES
(
	(SELECT ProductID, PR_Description FROM Products FOR XML AUTO, ELEMENTS),
	(SELECT ColourID, CO_Description FROM Colours FOR XML AUTO, ELEMENTS),
	(SELECT SizeID, SI_Description FROM Sizes FOR XML AUTO, ELEMENTS)
)

SELECT 
       Products as '*',
       Colours as '*',
       Sizes as '*' 
from @TempExportTable 
FOR XML PATH('ExportList')

I would, of course use the column names you assigned to your tables as they would not be the same

Kind Regards,
Riaan
Post #1386520
sharon.chapman7
Posted Monday, November 19, 2012 2:52 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, November 19, 2012 2:52 PM
Points: 2, Visits: 3
Thank you for your quick reply. This has been very helpful.

Sharon
Post #1386586
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse

 
 
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.