Load XML data into SQL Server table using XQUERY

• chandrasekhar.ms

  SSC-Addicted

  Points: 412

  More actions

  July 31, 2010 at 6:23 am

  #224780

  Hi,

  I am new to XML in SQL Server. I have a scenario where i need to load an XML file with namespace into a SQL Server table.

  Here is the sample XML.

  <C xmlns:xsi="http://myschema-instance" xmlns:xsd="myxmlSchema" xmlns="urn:cs" >

  <A B="0" D="Sample1"/>

  <A B="1" D="Sample1"/>

  <A B="2" D="Sample1"/>

  <A B="3" D="Sample1"/>

  <A B="4" D="Sample1"/>

  <A B="5" D="Sample1"/>

  </C>

  I need to load this into a table S(A INT, D VARCHAR(10)). Can someone assist me on this? It would be great if you can direct me to some quick start tutorial on XML with SQL Server.

  Regards,

  -Chandra.

• LutzM

  SSC Guru

  Points: 107049

  More actions

  August 1, 2010 at 2:12 am

  #1200732

  Here's what I came up with. Since I didn't know if you need the values for attribute B or the element name A I included both.

  I don't know of a good tutorial, but I recommend to have a look atJacob Sebastians blog for a list of sample codes.

  DECLARE @xml XML

  SET @xml='<C xmlns:xsi="http://myschema-instance" xmlns:xsd="myxmlSchema" xmlns="urn:cs" >

  <A B="0" D="Sample1"/>

  <A B="1" D="Sample1"/>

  <A B="2" D="Sample1"/>

  <A B="3" D="Sample1"/>

  <A B="4" D="Sample1"/>

  <A B="5" D="Sample1"/>

  </C>'

  ;

  WITH XMLNAMESPACES

  (

  'http://myschema-instance' AS xsi,

  'myxmlSchema' AS xsd,

  DEFAULT 'urn:cs'

  )

  SELECT

  c.value('local-name(.)','VARCHAR(10)') AS A,

  c.value('@B[1]','INT') AS B,

  c.value('@D[1]','VARCHAR(10)') AS D

  FROM @xml.nodes('C/A') T(c)

  ---

  
  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]