Exporting table data into a XML File

• Esteban-786736

  SSC-Addicted

  Points: 400

  More actions

  June 26, 2008 at 7:33 am

  #96051

  Hi everyone,

  I need to create a stored proc to export table datas into a XML file, there will be a scheduled job calling the stored proc, so I need to flag the rows as *exported* to avoid exporting them many times.

  So the stored proc creates a cursor, fetch all rows where EXPORTED=FALSE

  Export some columns into a XML file (Tag name = Col name).

  Update the EXPORTED field to TRUE.

  Next row...

  I know, there is a lot of details in all this, but if anybody can help me with my proc i would be appreciated.

  Thanks in advance.

• Michael Earl-395764

  SSC Guru

  Points: 53873

  More actions

  June 26, 2008 at 7:59 am

  #834677

  Why do you want o use a cursor?

  Wouldn't something like this work?

  [font="Courier New"]BEGIN TRAN

  SELECT

  *

  FROM

  MyTable

  WHERE

  Exported = 0

  FOR XML RAW

  UPDATE

  MyTable

  SET

  Exported = 1

  WHERE

  Exported = 0

  COMMIT TRAN[/font]

• Esteban-786736

  SSC-Addicted

  Points: 400

  More actions

  June 26, 2008 at 8:27 am

  #834707

  Thanks for your quick reply.

  It could be easier that way in deed, but I need to be more specific about what I have to do:

  1) Each row must be saved into a separated XML file.

  2) The XML file will have additional header tags (Maybe a XML Schema would do it) with the data itself.

  3) Each XML file is saved in a folder as 'uniquename'.xml.

  Actually the name of the XML file could be the primary key of each row in order to be unique.

  Can you enhance your code to do that?

  Thanks again for your help.