Load XML file contents in SQL Server table.

• irfanshaukat999

  SSC Enthusiast

  Points: 123

  More actions

  April 1, 2011 at 5:38 am

  #234867

  Hi all

  I have to load the data from multiple xml files into Sql Server tables.

  I have imported complete XML file in a column. Then using sp_xml_preparedocument and OPEN xml to view/insert the data in a table.

  But the problem here is that the data is not being inserted completely. There is a tag 'Header' below the main/root tag whose data is not being inserted properly and shows NULL data ; when inserted with the other tags. but works fine when just 'header' data is inserted.

  Is this the problem related to the Insert statement used or the path used in the OPENXML?

  Kindly advice or any other possible solution.

  Regards

• LutzM

  SSC Guru

  Points: 107049

  More actions

  April 1, 2011 at 5:58 am

  #1306419

  Is this the problem related to the Insert statement used or the path used in the OPENXML?

  It depends.

  What is the output if you run a SELECT YorXmlColumn FROM YourTable and click on the resulting xml data tag?

  It should return your original xml structure in a new query window.

  If so, then it's an issue of the OPENXML statement.

  Btw: Since you seem to use SS2K8 (based on the forum you posted in), why sdon't you use XQuery instead?

  ---

  
  Lutz
  A pessimist is an optimist with experience.

  How to get fast answers to your question[/url]
  How to post performance related questions[/url]
  Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

• irfanshaukat999

  SSC Enthusiast

  Points: 123

  More actions

  April 1, 2011 at 6:07 am

  #1306422

  Dear LutzM

  Thanks for your responce:)

  yes, when I select and click on the column it does open the complete XML file in a new window. (while I want to store that file's contents in SQL tables).

  Moreover after loading the file I am using the OPENXML to view/insert the file's data in table. The actual problem is there(in insertion,as described in the orignal post).

  Yes I am using SS2K8. but I don't have the idea about XQUERY...kindly guide me about it or provide some link if it is a better approcah for the (mentioned) problem.

  Regards

• LutzM

  SSC Guru

  Points: 107049

  More actions

  April 1, 2011 at 1:54 pm

  #1306681

  Sorry for the late reply...

  Regarding XQuery: You could have a look at Jacob Sebastians blog or post a sample of your XML code and your target table structure to get a coded and tested solution.

  ---

  
  Lutz
  A pessimist is an optimist with experience.

  How to get fast answers to your question[/url]
  How to post performance related questions[/url]
  Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]