Home Forums Programming XML Returning Multiple Unrelated Tables in Single XML Output From Stored Proc RE: Returning Multiple Unrelated Tables in Single XML Output From Stored Proc

  • 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