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>

• Mark Cowne

  One Orange Chip

  Points: 26954

  More actions

  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

• UnionAll

  SSCrazy

  Points: 2102

  More actions

  April 27, 2011 at 9:24 am

  #1316695

  Perfect. Thankyou for the help!

• joe-871382

  SSC Enthusiast

  Points: 116

  More actions

  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

• Dwain Camps

  SSC Guru

  Points: 86978

  More actions

  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

• joe-871382

  SSC Enthusiast

  Points: 116

  More actions

  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