SQL Server XML Shredding Part 6


We have an external file that we receive periodically that needs to be shredded into tables into SQL Server. In this example, we have a bookstore. We order from book manufacturers and they send us an XML file listing the books we ordered.

This part is similar to Part 5, except that we have added namespaces to our incoming XML file.

Our XML file has three sections. We need to import it into three related tables in SQL Server. These tables will be used to keep track of our inventory. Our external file, C:\Data\temp\bookprefix.xml is shown below. It is the same one as in Part 5 but has the namespace bko.

<?xml version="1.0" encoding="UTF-8"?>
<bko:bookorder xmlns:bko="http://porterwebsites.com/bookorders" 
xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
  <bko:header>
    <bko:supplier>Cookbooks Galore</bko:supplier>
	<bko:orderid>1029</bko:orderid>
  </bko:header>
  <bko:books>
    <bko:book>
      <bko:category>Cooking</bko:category>
      <bko:title>Cooking With Ketchup</bko:title>
    </bko:book>
    <bko:book>
      <bko:category>Cooking</bko:category>
      <bko:title>Mustard Madness!</bko:title>
    </bko:book>
  </bko:books>
  <bko:trailer>
    <bko:numberofbooks>2</bko:numberofbooks>
  </bko:trailer>
</bko:bookorder>

Below is the T-SQL code used to import the xml file into three tables.

use myDatabase
GO
DECLARE @xml XML;
DECLARE @OrderID VARCHAR(50);

SET @xml =  (SELECT * FROM OPENROWSET(BULK 'C:\data\temp\bookprefix.xml', SINGLE_BLOB) AS data)
SET @OrderID =  @xml.value('declare namespace bko="http://porterwebsites.com/bookorders"; (/bko:bookorder/bko:header/bko:orderid)[1]', 'varchar(50)')
PRINT @OrderID;

WITH XMLNAMESPACES ('http://porterwebsites.com/bookorders' as bko)
INSERT INTO BookOrder (Supplier, OrderID)
SELECT Supplier = xc.value('(bko:supplier)[1]', 'varchar(50)'),
    OrderID = xc.value('(bko:orderid)[1]', 'varchar(50)')
	FROM  @xml.nodes('/bko:bookorder/bko:header') AS XT(XC);

WITH XMLNAMESPACES ('http://porterwebsites.com/bookorders' as bko)
INSERT INTO Books (Category, Title)
SELECT Category = xc.value('(bko:category)[1]', 'varchar(50)'),
    Title = xc.value('(bko:title)[1]', 'varchar(50)')
	FROM  @xml.nodes('/bko:bookorder/bko:books/bko:book') AS XT(XC);

WITH XMLNAMESPACES ('http://porterwebsites.com/bookorders' as bko)
INSERT INTO BookTrailer (NumberOfBooks)
SELECT NumberOfBooks = xc.value('(bko:numberofbooks)[1]', 'int')
	FROM @xml.nodes('/bko:bookorder/bko:trailer') AS XT(XC);

UPDATE Books SET [OrderID] = @OrderID;
UPDATE BookTrailer SET [OrderID] = @OrderID;
Series Navigation<< SQL Server XML Shredding Part 5

Leave a comment

Your email address will not be published. Required fields are marked *