InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comSat, 18 May 2013 23:57:47 GMT20http://www.sqlservercentral.com/Forums/Topic1377879-21-1.aspxThank you for your quick reply. This has been very helpful.SharonMon, 19 Nov 2012 14:52:53 GMTsharon.chapman7http://www.sqlservercentral.com/Forums/Topic1377879-21-1.aspx[quote][b]sharon.chapman7 (11/19/2012)[/b][hr]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,SharonPS 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.[/quote]Hi Sharon,I would simply list the columns in each of the SELECT statements.For instance (taking my example):[code="sql"]DECLARE @TempExportTable TABLE( Products XML, Colours XML, Sizes XML)INSERT INTO @TempExportTable VALUES( (SELECT [b]ProductID, PR_Description[/b] FROM Products FOR XML AUTO, ELEMENTS), (SELECT [b]ColourID, CO_Description[/b] FROM Colours FOR XML AUTO, ELEMENTS), (SELECT [b]SizeID, SI_Description[/b] FROM Sizes FOR XML AUTO, ELEMENTS))SELECT Products as '*', Colours as '*', Sizes as '*' from @TempExportTable FOR XML PATH('ExportList')[/code]I would, of course use the column names you assigned to your tables as they would not be the same :-DKind Regards,RiaanMon, 19 Nov 2012 11:33:46 GMTblugeckohttp://www.sqlservercentral.com/Forums/Topic1377879-21-1.aspx[quote][b]sharon.chapman7 (11/19/2012)[/b][hr]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,SharonPS 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.[/quote]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?Mon, 19 Nov 2012 10:56:14 GMTSean Langehttp://www.sqlservercentral.com/Forums/Topic1377879-21-1.aspxHi,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,SharonPS 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.Mon, 19 Nov 2012 10:48:00 GMTsharon.chapman7http://www.sqlservercentral.com/Forums/Topic1377879-21-1.aspxCame right with guidance from various sources. Below the solution in case anyone ever wants to do something similar...[code="sql"]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')[/code]Sat, 27 Oct 2012 13:14:32 GMTblugeckohttp://www.sqlservercentral.com/Forums/Topic1377879-21-1.aspxThanks 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,RiaanFri, 26 Oct 2012 15:49:00 GMTblugeckohttp://www.sqlservercentral.com/Forums/Topic1377879-21-1.aspx[quote][b]riaan-777462 (10/26/2012)[/b][hr]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:[code="sql"]SELECT * FROM ProductsSELECT * FROM ColoursSELECT * FROM SizesFOR XML AUTO, ROOT, ELEMENTS[/code]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?[code="xml"]<root> <Products> <...> </Products> <Colours> <...> </Colours> <Sizes> <...> </Sizes></root>[/code]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[/quote]I would split this into three procs instead of trying to do this in a single one.Fri, 26 Oct 2012 15:39:43 GMTSean Langehttp://www.sqlservercentral.com/Forums/Topic1377879-21-1.aspxHi 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:[code="sql"]SELECT * FROM ProductsSELECT * FROM ColoursSELECT * FROM SizesFOR XML AUTO, ROOT, ELEMENTS[/code]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?[code="xml"]<root> <Products> <...> </Products> <Colours> <...> </Colours> <Sizes> <...> </Sizes></root>[/code]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,RiaanFri, 26 Oct 2012 15:25:11 GMTblugecko