October 26, 2012 at 3:25 pm
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
October 26, 2012 at 3:39 pm
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 Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
October 26, 2012 at 3:49 pm
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
October 27, 2012 at 1:14 pm
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')
November 19, 2012 at 10:48 am
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.
November 19, 2012 at 10:56 am
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 Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
November 19, 2012 at 11:33 am
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
November 19, 2012 at 2:52 pm
Thank you for your quick reply. This has been very helpful.
Sharon
February 27, 2016 at 4:16 am
I'm looking for a similar solution, but instead of the 3 select statements, the values will come from 3 stored procs. I tried replacing the procs, get error.
INSERT INTO #TempExportTable VALUES
EXEC ('
EXEC [dbo].[spGetProductsDesc] @tId FOR XML AUTO, ELEMENTS;
EXEC [dbo].[spGetColoursDesc] @tId FOR XML AUTO, ELEMENTS;
EXEC [dbo].[spGetSizesDesc] @tId FOR XML AUTO, ELEMENTS;
');
Any help?
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')
February 27, 2016 at 4:33 am
Tech Bang (2/27/2016)
I'm looking for a similar solution, but instead of the 3 select statements, the values will come from 3 stored procs. I tried replacing the procs, get error.INSERT INTO #TempExportTable VALUES
EXEC ('
EXEC [dbo].[spGetProductsDesc] @tId FOR XML AUTO, ELEMENTS;
EXEC [dbo].[spGetColoursDesc] @tId FOR XML AUTO, ELEMENTS;
EXEC [dbo].[spGetSizesDesc] @tId FOR XML AUTO, ELEMENTS;
');
Any help?
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')
Hi Tech Bang,
I would suggest either changing the Stored Procs to a User-Defined-Function which returns a Table:
e.g.
CREATE FUNCTION MyProc()
RETURNS TABLE AS
RETURN (SELECT * FROM MyTable)
Or using OpenQuery:
e.g.
INSERT INTO @TempExportTable VALUES
(
(SELECT * FROM OPENQUERY(YOURSERVERNAME, 'EXEC spGetProductsDesc @tId') FOR XML AUTO, ELEMENTS),
(SELECT * FROM OPENQUERY(YOURSERVERNAME, 'EXEC spGetColoursDesc @tId') FOR XML AUTO, ELEMENTS),
(SELECT * FROM OPENQUERY(YOURSERVERNAME, 'EXEC spGetSizesDesc @tId') FOR XML AUTO, ELEMENTS)
)
...
I haven't tested the code but should put you on the right track 🙂
Regards,
Riaan
February 27, 2016 at 4:59 am
#Grasshopper, the compiler isnt happy. It fails at the first param of OPENQUERY itself.
DECLARE @server_name VARCHAR(100);
SELECT @server_name = @@SERVERNAME;
DECLARE @temp TABLE
(
Gaps XML,
Issues XML
)
INSERT INTO @temp VALUES
(
(SELECT * FROM OPENQUERY(@@SERVERNAME, 'EXEC spGetCriticalGapsForATransition @transitionId') FOR XML AUTO, ELEMENTS),
(SELECT * FROM OPENQUERY(@@SERVERNAME, 'EXEC spGetCriticalIssuesForATransition @transitionId') FOR XML AUTO, ELEMENTS)
);
February 27, 2016 at 5:27 am
Tech Bang (2/27/2016)
#Grasshopper, the compiler isnt happy. It fails at the first param of OPENQUERY itself.
DECLARE @server_name VARCHAR(100);SELECT @server_name = @@SERVERNAME;
DECLARE @temp TABLE
(
Gaps XML,
Issues XML
)
INSERT INTO @temp VALUES
(
(SELECT * FROM OPENQUERY(@@SERVERNAME, 'EXEC spGetCriticalGapsForATransition @transitionId') FOR XML AUTO, ELEMENTS),
(SELECT * FROM OPENQUERY(@@SERVERNAME, 'EXEC spGetCriticalIssuesForATransition @transitionId') FOR XML AUTO, ELEMENTS)
);
Hi Tech Bang,
Apparently you cannot use variables as the server name so @@SERVERNAME will not work. Instead use it's actual name and instance enclosed in square operands
e.g. SELECT * FROM OPENQUERY([SERVERNAME\SQLEXPRESS], 'EXEC spGetCriticalGapsForATransition @transitionId')
If you get an error stating "Server 'SERVERNAME\SQLEXPRESS' is not configured for DATA ACCESS." then run the following:
exec sp_serveroption @server = 'SERVERNAME\SQLEXPRESS'
,@optname = 'DATA ACCESS'
,@optvalue = 'TRUE'
Regards,
Riaan
February 27, 2016 at 6:34 am
Sean Lange (10/26/2012)
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.
Perhaps a view of the joined data instead of individual tables would do the trick?
--Jeff Moden
Change is inevitable... Change for the better is not.
February 27, 2016 at 8:05 am
Hi Riaan,
INSERT INTO @temp VALUES
(
(SELECT * FROM OPENQUERY([INTL-DBSERVER], 'EXEC spGetCriticalGapsForATransition @transitionId = 1') FOR XML AUTO, ELEMENTS),
(SELECT * FROM OPENQUERY([INTL-DBSERVER], 'EXEC spGetCriticalIssuesForATransition @transitionId = 1') FOR XML AUTO, ELEMENTS)
);
select * FROM @temp;
results in the following compiler error. However, the SPs can be executed separately without any problem, they return value. Couldn't succeed in getting the exact cause of this error.
Msg 11529, Level 16, State 1, Procedure sp_describe_first_result_set, Line 8
The metadata could not be determined because every code path results in an error; see previous errors for some of these.
Msg 2812, Level 16, State 62, Procedure sp_describe_first_result_set, Line 8
Could not find stored procedure 'spGetCriticalGapsForATransition'.
February 27, 2016 at 10:04 am
Tech Bang (2/27/2016)
Hi Riaan,INSERT INTO @temp VALUES
(
(SELECT * FROM OPENQUERY([INTL-DBSERVER], 'EXEC spGetCriticalGapsForATransition @transitionId = 1') FOR XML AUTO, ELEMENTS),
(SELECT * FROM OPENQUERY([INTL-DBSERVER], 'EXEC spGetCriticalIssuesForATransition @transitionId = 1') FOR XML AUTO, ELEMENTS)
);
select * FROM @temp;
results in the following compiler error. However, the SPs can be executed separately without any problem, they return value. Couldn't succeed in getting the exact cause of this error.
Msg 11529, Level 16, State 1, Procedure sp_describe_first_result_set, Line 8
The metadata could not be determined because every code path results in an error; see previous errors for some of these.
Msg 2812, Level 16, State 62, Procedure sp_describe_first_result_set, Line 8
Could not find stored procedure 'spGetCriticalGapsForATransition'.
Hi Tech Bang,
"The metadata could not be determined..." is because your return columns aren't explicitly declared in your stored procs. You could try using "WITH RESULT SET" in the EXEC query, or explicitly declare your columns in the Stored Procs.
"Could not find stored procedure..." is because you may need to declare the full stored proc name with the database name and schema: e.g. YOURDBNAME.dbo.spGetCriticalGapsForATransition
@params are also going to be tricky.
The code I provided above was meant to be a guideline only; It may be best to read up on OPENQUERY or even OPENROWSET from MSDN.
My advice, however, would be to rather go with the User-Defined-Function RETURNS TABLE AS route as SSIS (OPENQUERY) is very finicky with complicated stored procs especially with "Select *" and temp tables...
Hope this helps.
Regards,
Riaan
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply