Returning Multiple Unrelated Tables in Single XML Output From Stored Proc

  • 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

  • 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/

  • 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

  • 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')

  • 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.

  • 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/

  • 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

  • Thank you for your quick reply. This has been very helpful.

    Sharon

  • 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')

  • 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

  • #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)

    );

  • 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

  • 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


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • 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'.

  • 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