Load Relational XML data in SQL Server 2005

When question comes to play with XML, even seasoned programmer try to get away from it, not because it is difficult task but lack of knowledge. I promise, loading XML data into SQL Server 2005 as a relational data, is not going to be as difficult as you are thinking.
Let me show you one example which will fill two relational tables in SQL Server from XML data. Let us first create one XML file at C:\vendor.XML with following data in it.
   <vendor>
<c>
  <venID>101</venID>
  <first_name>James</first_name>
  <last_name>Butler</last_name>
<po>
  <poid>1001</poid>
  <venID>101</venID>
  <po_date>2008-04-01T00:00:00</po_date>
  <price>400.00</price>
  </po>
<po>
  <poid>1002</poid>
  <venID>101</venID>
  <po_date>2008-05-01T00:00:00</po_date>
  <price>299.0000</price>
 </po>
<po>
  <poid>1002</poid>
  <venID>101</venID>
  <po_date>2009-04-01T00:00:00</po_date>
  <price>600.0000</price>
</po>
 </c>

</vendor>

<

Now, create two tables to feed vendor details and purchase order detail, than we will load XML file into both tables.
–create table to hold Vendor information
CREATE TABLE vendor (
        venid INT NOT NULL
        , first_name VARCHAR(50)
        , last_name VARCHAR(50)
        )
–create table for holding Purchase order information.
CREATE TABLE PurcharOrder (
        poID INT NOT NULL
        , venid INT NOT NULL
        , po_date DATETIME
        , price MONEY
        )
GO
–hold xml from file
DECLARE @x XML
SET @x= (
SELECT xm.Col1 FROM OPENROWSET(BULK ‘c:\vendor.xml’,SINGLE_BLOB) AS xm(Col1)
         )
— show file contents
select @x
INSERT INTO vendor
SELECT
ven.value(‘./venID[1]’, ‘INT’) as vid
, ven.value(‘./first_name[1]’, ‘VARCHAR(50)’) as first_name
, ven.value(‘./last_name[1]’, ‘VARCHAR(50)’) as last_name
–, C.query(‘.’) 
FROM @x.nodes(‘/vendor/c’) tab(ven)
INSERT INTO PurcharOrder
SELECT
ven.value(‘./poid[1]’, ‘INT’) as POID
, ven.value(‘./venID[1]’, ‘INT’) as VenID
, ven.value(‘./po_date[1]’, ‘DATETIME’) as po_date
, ven.value(‘./price[1]’, ‘MONEY’) as price
–,ven.query(‘.’)
FROM @x.nodes(‘/vendor/c/po’) tab(ven)
GO
–check both table, whether data comes or not.
select * FROM Vendor
Select * from PurcharOrder


Reference: Ritesh Shah
http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of
http://www.SQLHub.com
Advertisements

2 Responses to “Load Relational XML data in SQL Server 2005”

  1. Anonymous Says:

    too simple.very lame.I wonder why your blog is allowed to be online.Adam

  2. Anonymous Says:

    too simple.very lame.I wonder why your blog is allowed to be online.Adam


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: