Post reply

Returning Multiple Unrelated Tables in Single XML Output From Stored Proc

  • February 27, 2016 at 10:30 am

    #1861639

    Hi Riaan,

    After a little struggle, I fixed the "The metadata could not be determined..." . I had to prefix the complete db and user name before the stored proc name like [database_name].[dbo].[spGetCriticalGapsForATransition] . This error disappeared, but there were other errors. Besides it wasn't taking the param variable, I have to hardcode the param value. Lack of time to research further, I dumbed this idea. Going with table return or multi-valued function.

    Thanks for your help so far.

  • April 15, 2016 at 6:28 am

    #1872300

    blugecko (10/27/2012)

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

    I serached for a solution more than a week. Thank you for your question and answer.:-)

Viewing 2 posts - 16 through 16 (of 16 total)

You must be logged in to reply to this topic. Login to reply