Loading data from an XML file

• Adam Greifer

  Valued Member

  Points: 55

  More actions

  February 28, 2007 at 1:56 pm

  #69526

  Hi all,

  My first post. I know some xquery and OPENXML but would like to hear your opinions on what you consider the best way to load data into relational tables from an XML file. If I store the XML in one big column it means I'll have to use xquery every time I want to see data in relation form. What I really want to do is convert the XML data so that it will be loaded in relational form into database tables. For example, here's a snippet of the XML file:

  <

  provider>

  <

  OrgID></OrgID>

  <

  OrgName></OrgName>

  <

  OrgAbbrev></OrgAbbrev>

  <

  AlsoKnownAs></AlsoKnownAs>

  <

  Description></Description>

  <

  Keyword></Keyword>

  <

  Category></Category>

  <

  AAACategory></AAACategory>

  <

  Matching>

  <

  Source></Source>

  <

  RefID></RefID>

  </

  Matching>

  <

  Matching>

  <

  Source></Source>

  <

  RefID></RefID>

  </

  Matching>

  <

  RelatedOrg></RelatedOrg>

  <

  Address>

  <

  Type></Type>

  <

  Street1></Street1>

  <

  Street2></Street2>

  <

  City></City>

  <

  County></County>

  <

  CountyCode></CountyCode>

  <

  State></State>

  <

  Zip></Zip>

  <

  Zip4></Zip4>

  </

  Address>

  </

  provider>

  I've created a Provider, Address and Matching table. Now what? Thanks in advance for your help.

  Adam

  adam.greifer@gmail.com

• March 5, 2007 at 8:00 am

  This was removed by the editor as SPAM

• bledu

  SSChampion

  Points: 12335

  More actions

  March 6, 2007 at 9:10 am

  #693343

  DECLARE @idoc int

  DECLARE @doc varchar(4000)

  SET @doc ='

  <provider>

      <OrgID>Test1</OrgID>

      <OrgName>Test Org 1</OrgName>

      <OrgAbbrev>TO1</OrgAbbrev>

      <AlsoKnownAs>Org1</AlsoKnownAs>

      <Description>This is a test organisation</Description>

      <Keyword>test the organisation</Keyword>

      <Category>TestCat</Category>

      <AAACategory>1</AAACategory>

      <Matching>

          <Source>Bank1</Source>

          <RefID>TO1</RefID>

      </Matching>

      <Matching>

          <Source>Bank2</Source>

          <RefID>TO1</RefID>

      </Matching>

      <RelatedOrg>TestingOrg1</RelatedOrg>

      <Address>

          <Type>Office</Type>

          <Street1>1 Test Street</Street1>

          <Street2>Test Corner</Street2>

          <City>Harare</City>

          <County>Zimbabwe</County>

          <CountyCode>ZW</CountyCode>

          <State>HRE</State>

          <Zip>0000</Zip>

          <Zip4>0000</Zip4>

      </Address>

  </provider>'

  --Create an internal representation of the XML document.

  EXEC sp_xml_preparedocument @idoc OUTPUT, @doc

  -- Execute a SELECT statement that uses the OPENXML rowset provider.

  --INSERT INTO Provider

  --Blah blah blah

  SELECT    OrgID, OrgName

  FROM       OPENXML (@idoc, '/provider',2)

              WITH (OrgID  varchar(10),

                    OrgName varchar(20))

  --INSERT INTO Matching

  --Blah blah blah

  SELECT    Source,RefID

  FROM       OPENXML (@idoc, '/provider/Matching',2)

              WITH (Source  varchar(10),

                    RefID varchar(20))

  --INSERT INTO Address

  --blah blah blah

  SELECT    *

  FROM       OPENXML (@idoc, '/provider/Address',2)

              WITH (Type  varchar(10),

                    Street1 varchar(20),

        City VARCHAR(30),

        County VARCHAR(30))

  EXEC sp_xml_removedocument @idoc

   

  note that the row pattern is case sensitive i.e. '/provider/Address' and '/provider/address' are read differently

  ---

  Everything you can imagine is real.