Create XML file using TSQL

Question

Post reply

Create XML file using TSQL

UnionAll

SSCrazy

Points: 2102
More actions
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>

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

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

Mark Cowne One Orange Chip Points: 26954 More actions · Answer 1

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

UnionAll SSCrazy Points: 2102 More actions · Answer 2

UnionAll

SSCrazy

Points: 2102

April 27, 2011 at 9:24 am

#1316695

Perfect. Thankyou for the help!

joe-871382 SSC Enthusiast Points: 116 More actions · Answer 3

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

Dwain Camps SSC Guru Points: 86978 More actions · Answer 4

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

joe-871382 SSC Enthusiast Points: 116 More actions · Answer 5

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