Post reply

Create XML file using TSQL

  • April 27, 2011 at 8:34 am

    #249464

    I am trying to create a xml file from a sql table. Please see the sample data below. Any help is appreciated 🙂

    Sample table create scripts:

    -- DROP TABLE #Temp

    CREATE TABLE #Temp

    (LastName VARCHAR(100), FirstName VARCHAR(100))

    INSERT #Temp (LastName, FirstName)

    SELECT 'Richards','Gary'

    UNION ALL

    SELECT 'Smith','Andy'

    SELECT * FROM #Temp

    The XML file should look like the following.

    <INDIVIDUALS_LIST>

    <INDIVIDUALS>

    <LASTNAME>Richards</LASTNAME>

    <FIRSTNAME>Gary</FIRSTNAME>

    <LASTNAME>Smith</LASTNAME>

    <FIRSTNAME>Andy</FIRSTNAME>

    </INDIVIDUALS>

    </INDIVIDUALS_LIST>

  • April 27, 2011 at 8:50 am

    #1316656

    SELECT

    (SELECT LastName AS "LASTNAME",

    FirstName AS "FIRSTNAME"

    FROM #Temp

    FOR XML PATH(''),ROOT('INDIVIDUALS'),TYPE)

    FOR XML PATH('INDIVIDUALS_LIST')

    ____________________________________________________

    Deja View - The strange feeling that somewhere, sometime you've optimised this query before

    How to get the best help on a forum

    http://www.sqlservercentral.com/articles/Best+Practices/61537

  • April 27, 2011 at 9:24 am

    #1316695

    Perfect. Thankyou for the help!

  • June 20, 2012 at 7:43 am

    #1503420

    Hi,

    I know I am late to the conversation, but I can't seem to figure out how to actually create the file on a drive somwhere that I can FTP somewhere else.

    In this example, where is the file being saved?

    Thanks,

    Joe

  • June 20, 2012 at 7:43 pm

    #1503738

    joe-871382 (6/20/2012)

    Hi,

    I know I am late to the conversation, but I can't seem to figure out how to actually create the file on a drive somwhere that I can FTP somewhere else.

    In this example, where is the file being saved?

    Thanks,

    Joe

    In the example above, the XML is simply displayed to the Results pane in SSMS. You can assign it to an XML variable for further processing (e.g., can be CAST to VARCHAR(MAX) to write to a file).

    OPENROWSET is one way to get the XML out to a file (e.g., to Excel). Google it and if you can't figure it out, return and I'm sure someone will be able to help you.

    My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]

    My thought question: Have you ever been told that your query runs too fast?

    My advice:
    INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
    The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

    Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
    Since random numbers are too important to be left to chance, let's generate some![/url]
    Learn to understand recursive CTEs by example.[/url]
    [url url=http://www.sqlservercentral.com/articles/St

  • June 21, 2012 at 6:18 am

    #1503884

    Thanks, I was able to figure out how to get an SSIS package to create the file. I need to SFTP it as well so I figured it was better to put the solution into SSIS.

    Thanks again for your response.

    Joe

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

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