XML Import

• ianM

  SSC Journeyman

  Points: 91

  More actions

  July 25, 2006 at 3:15 am

  #173399

  I am sent on a weekly basis an XML File with Order Data

  I need to select certain nodes and import into SQL 2K and/OR SQL 2k5

  So far everything I try fails, mostly due to my little knowledge of XMl

  I do know I don't want to import as XML but from XML

  Any sites??  and code ?? any help appreciated

  Ian

• Stephen Turner

  Mr or Mrs. 500

  Points: 530

  More actions

  July 25, 2006 at 7:54 am

  #651339

  I had to figure out how to do something like this in SQL2000, there may be a better way now so someone will be along RSN to point it out...

  I use the following to open the XML, the actual text of the XML is already in the variable @xmlDoc

  EXEC

  sp_xml_preparedocument @xmlDocPointer OUTPUT, @xmlDoc

  Then I can extract a list of items with:

  SELECT Char(9) + fieldkey + ':' + Char(9) + isnull(value, 'Null') + char(13) + char(10) FROM OPENXML(@xmlDocPointer, N'//field[@key][@infoType="risk"]') WITH(fieldkey nvarchar(50) 'attribute::key/text()', value nvarchar(50) 'node()')

  Or an individual populated row with:

  SELECT BusinessName, Salutation, Surname FROM OPENXML (@xmlDocPointer, N'/') WITH ( BusinessName nvarchar(255) '//field[@key = "Business"]', Salutation nvarchar(5) '//field[@key = "Title"]', Surname nvarchar(50) '//field[@key = "Surname"]')

  Then free up memory with:

  EXEC

  sp_xml_removedocument @xmlDocPointer

  'field', 'key', 'infoType' and 'risk' are all bits of my xml document

  This may not be best for you, I needed to deal with one record at a time so it was fine for me.

• bledu

  SSChampion

  Points: 12335

  More actions

  July 26, 2006 at 9:51 am

  #651684

  that's the same thing i used on 2000

  ---

  Everything you can imagine is real.