Load XML File to SQL Server

• SQLback

  SSCrazy

  Points: 2897

  More actions

  June 9, 2010 at 12:26 pm

  #219765

  Hi

  I am trying to load a xml file into SQL Server.

  Here is my xml file

  - <tv>

  - <programme>

  <category lang="en">Newsmagazine</category>

  <category lang="en">Interview</category>

  <category lang="en">Public affairs</category>

  <category lang="en">Series</category>

  </programme>

  - <programme>

  <category lang="en">Anthology</category>

  <category lang="en">Mystery</category>

  <category lang="en">Series</category>

  </programme>

  </tv>

  I want to display the list of Categories. I tried using OpenXML and XML.Nodes() methods but can only display first category in each node.

  -- XML Nodes Method

  DECLARE @h INT,

  @X XML

  SELECT @X = c FROM OPENROWSET (

  BULK 'C:\Test.xml', SINGLE_BLOB) AS Temp(c)

  Select--C.value('@id','varchar(100)') AS id,

  C.value('category[1]','varchar(100)') AS category

  From @X.nodes('/tv/programme') T(C)

  -- OpenXML

  DECLARE @h INT,

  @X XML

  SELECT @X = c FROM OPENROWSET (

  BULK 'C:\Test.xml', SINGLE_BLOB) AS Temp(c)

  EXEC sp_xml_preparedocument @h OUTPUT, @X

  SELECT *

  --INTO CS_GTVRAW_EVENTS_Test

  FROM OPENXML( @h, '/tv/programme',1)

  WITH (

  category varchar(50) 'category'

  )

  EXEC sp_xml_removedocument @h

  The result of these two queries is just two records (Newsmagazine and Anthology). I want all the categories. Please assist.

  Thanks,

  Reddy

• Eralper

  SSCarpal Tunnel

  Points: 4438

  More actions

  June 10, 2010 at 6:58 am

  #1178573

  Hi,

  Please test the below t-sql code according to your settings

  Select

  C.value('.','varchar(100)') AS category

  From @xml.nodes('/tv/programme/category') T(C)

  Please note that I changed the category[1] to '.' and @X.nodes('/tv/programme') to @xml.nodes('/tv/programme/category')

  I hope that helps,

  Eralper
  SQL Server and T-SQL Tutorials and Articles
  Microsoft Certification and Certification Exams

• Eralper

  SSCarpal Tunnel

  Points: 4438

  More actions

  June 10, 2010 at 7:00 am

  #1178574

  By the way, you can also test the below OPENXML code statement

  DECLARE @h INT, @X XML

  SELECT @X = c FROM OPENROWSET (BULK 'C:\Test.xml', SINGLE_BLOB) AS Temp(c)

  EXEC sp_xml_preparedocument @h OUTPUT, @X

  SELECT *

  --INTO CS_GTVRAW_EVENTS_Test

  FROM OPENXML( @h, '/tv/programme/category',1)

  WITH (

  category varchar(50) '.'

  )

  EXEC sp_xml_removedocument @h

  Eralper
  SQL Server and T-SQL Tutorials and Articles
  Microsoft Certification and Certification Exams

• SQLback

  SSCrazy

  Points: 2897

  More actions

  June 17, 2010 at 8:20 am

  #1181589

  That works. Thanks for you help Eralper.

  Following is a good link on how to load xml into sql server.

  http://weblogs.sqlteam.com/peterl/archive/2008/09/01/Updated-XML-search-test-case-with-variables.aspx

  Reddy