Post reply

Creating XML from tables

  • August 20, 2008 at 5:51 am

    #382338

    Hi

    Hope somebody can help, I am new to XML and need to export data in the following format.

    The following SQL returns the two distinct sections of the XML but I am unsure how to combine them so that I get one routing section and multple InspRqst sections

    Select DocumentName, SourceOrganization, DestinationOrganization, NotificationFTP, NotificationEmail from Routing Where Processed = 0 FOr XML auto, Elements

    Select RegNum, Vin, DOR, Make, Model, Derivative, Location, InspCAT, DateAvailable, InspRef, VehicleRef from Inspections where Processed = 0 FOr XML auto, Elements

    Hope somebody can help

    regards

    Tom

  • August 20, 2008 at 5:59 am

    #860554

    Tom,

    Can you post the XML as an attachment?

    Without seeing what you're looking for, take a look at examples that use FOR XML PATH instead of FOR XML AUTO. That gives you more control for how the XML output.

  • August 20, 2008 at 6:04 am

    #860560

    Looks like the forumn deleted my XML from previous post!

    Attachment uploaded as requested, saved as .txt as forum does nto allow uploading of .xml types.

    regards

    Tom

  • August 20, 2008 at 6:54 am

    #860585

    Combining those two XML fragments could be done like this.

    DECLARE @xmlresult1 XML

    DECLARE @xmlresult2 XML

    SET @xmlresult1 = (SELECT DocumentName FROM Routing FOR XML AUTO, ELEMENTS)

    SET @xmlresult2 = (SELECT Regum FROM Inspections FOR XML AUTO, ELEMENTS)

    SELECT CAST(CAST(@xmlresult1 as VARCHAR(MAX)) + CAST(@xmlresult2 as VARCHAR(MAX)) AS XML)

  • August 20, 2008 at 6:58 am

    #860590

    Thanks for that, was hoping to do it as one bit of SQL but can do it that way.

    cheers for your help

    regards

    Tom

  • August 20, 2008 at 12:44 pm

    #860837

    I can see where a single statement would seem cleaner, how about something like this?

    SELECT DocumentName as "Routing/DocumentName",

    ( SELECT Regum FROM Inspections WHERE Processed=0 FOR XML PATH('InspRqst'),TYPE )

    FROM Routing

    WHERE Processed=0

    FOR XML PATH('root')

Viewing 6 posts - 1 through 6 (of 6 total)

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