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. To keep things simple, our file in this example only has two books in it and it only has a few data elements. 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.
Part 5 continues on from Part 4, where we learned that the OPENXML function did not work for us. In Part 6 we will add a namespace to our XML file and change the code below to handle that.
Below is the file C:\data\temp\book.xml.
<bookstore> <header> <supplier>Cookbooks Galore</supplier> <orderid>1029</orderid> </header> <books> <book> <category>Cooking</category> <title>Cooking With Ketchup</title> </book> <book> <category>Cooking</category> <title>Mustard Madness!</title> </book> </books> <trailer> <numberofbooks>2</numberofbooks> </trailer> </bookstore>
DECLARE @xml XML; DECLARE @OrderID VARCHAR(50); SET @xml = (SELECT * FROM OPENROWSET(BULK 'C:\data\temp\book.xml', SINGLE_BLOB) AS data) SET @OrderID = @xml.value('(/bookorder/header/orderid)', 'varchar(50)'); INSERT INTO BookOrder (Supplier, OrderID) SELECT Supplier = xc.value('(supplier)', 'varchar(50)'), OrderID = xc.value('(orderid)', 'varchar(50)') FROM @xml.nodes('/bookorder/header') AS XT(XC); INSERT INTO Books (Category, Title) SELECT Category = xc.value('(category)', 'varchar(50)'), Title = xc.value('(title)', 'varchar(50)') FROM @xml.nodes('/bookorder/books/book') AS XT(XC); INSERT INTO BookTrailer (NumberOfBooks) SELECT NumberOfBooks = xc.value('(numberofbooks)', 'int') FROM @xml.nodes('/bookorder/trailer') AS XT(XC); UPDATE Books SET [OrderID] = @OrderID; UPDATE BookTrailer SET [OrderID] = @OrderID;
After running the above script against the xml file we get the following results in our three tables. SSMS screenshots of the contents of the three tables are shown below. The three tables in SQL Server are: BookOrder, Books and BookTrailer.