Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

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


Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, August 30, 2013 2:39 AM
Points: 12, Visits: 38
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
Posted Friday, October 26, 2012 3:39 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 2:32 PM
Points: 13,302, Visits: 12,168
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
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1377886
Posted Friday, October 26, 2012 3:49 PM


Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, August 30, 2013 2:39 AM
Points: 12, Visits: 38
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
Posted Saturday, October 27, 2012 1:14 PM


Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, August 30, 2013 2:39 AM
Points: 12, Visits: 38
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
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
Posted Monday, November 19, 2012 10:56 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 2:32 PM
Points: 13,302, Visits: 12,168
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
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1386509
Posted Monday, November 19, 2012 11:33 AM


Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, August 30, 2013 2:39 AM
Points: 12, Visits: 38
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
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