SQL and XML Exporting

• alanm-735246

  Grasshopper

  Points: 12

  More actions

  May 5, 2008 at 9:33 pm

  #206962

  I have an SQL database, and I was wandering how to export data from the tables to an XML file – do you know how to do this?

  Also, I would also like to know if you can import XML files into tables of an SQL database?

  Any help would be appreciated!

  cheers

  alan

• RBarryYoung

  SSC Guru

  Points: 143329

  More actions

  May 5, 2008 at 11:20 pm

  #811011

  alanm (5/5/2008)

  ---

  I have an SQL database, and I was wandering how to export data from the tables to an XML file – do you know how to do this?

  Look at the FOR XML clause of the SELECT statement.

  Also, I would also like to know if you can import XML files into tables of an SQL database?

  Look at the OPENXML statement.

  [font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
  Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

• alanm-735246

  Grasshopper

  Points: 12

  More actions

  May 6, 2008 at 5:11 pm

  #811520

  Thanks - managed to use this:

  Select *

  From dbo.Database_User_Profiles

  For XML AUTO

  and it worked fine.

  Question is, where does the file end up on the server? I ran a search but cannot find it!

• tbeadle

  SSCommitted

  Points: 1635

  More actions

  May 7, 2008 at 1:23 pm

  #812029

  Hello,

  The Select statement returns the output to the output screen.

  'Assuming' you want to get the XML output to the operating system, you can load a table with the ouput in a single column and then use bcp to export it.

  Example table with single column.

  create output_tbl

  ( outrec varchar(1000) null )

  Then use your select statment to load a insert to the output_tlb.

  Then in the operating system .bat use the bcp out command to export it to the sub-directory of your choice.

  Regards,

  Terry