SQL Server Shredding XML with XQuery


Do you need to know the basics of importing an XML document into a table in SQL Server? Perhaps you are receiving periodic XML files from a client and you need to import them into a table for further processing. In this example we will use the previous XML file mentioned in another post at this site, SQL Server and XML Introduction, that has three books in it.

Here is our input XML file that we need to get into a SQL Server table. Suppose our client/customer has given us this file and nothing else. That is fine, this is all we need. We ask them however to make one change to the file for us to make it easier for us to process all of these future incoming files. We ask them to make the file all elements-based instead of a mixed-attributes-elements-based file. To do this we ask them to change category into its own element. Also we don’t need the language attribute for now. They said okay. This will make it easier for us.

<?xml version="1.0" encoding="UTF-8"?>
<bookstore>
  <book category="cooking">
    <title lang="en">Everyday Italian</title>
    <author>Giada De Laurentiis</author>
    <year>2005</year>
    <price>30.00</price>
  </book>
  <book category="children">
    <title lang="en">Harry Potter</title>
    <author>J K. Rowling</author>
    <year>2005</year>
    <price>29.99</price>
  </book>
</bookstore>

Below is the all-elements based XML file that we will import into SQL Server with XQuery.

<?xml version="1.0" encoding="UTF-8"?>
<bookstore>
  <book>
    <category>cooking</category>
    <title>Everyday Italian</title>
    <author>Giada De Laurentiis</author>
    <year>2005</year>
    <price>30.00</price>
  </book>
  <book>
    <category>children</category>
    <title>Harry Potter</title>
    <author>J K. Rowling</author>
    <year>2005</year>
    <price>29.99</price>
  </book>
</bookstore>

Below is the broken code to import the file. The file is stored on the C: drive in the xml folder. The file is called bookstoresample.xml. Why won’t this work? The table is created but the data does not come into the table. No errors are presented, only that zero rows were affected.

/*
     Change 'C:\xml\' to the folder containing 'bookstoresample.xml.'
*/
DECLARE @Doc XML
SET @Doc = (SELECT * FROM OPENROWSET(BULK 'C:\xml\bookstoresample.xml',SINGLE_BLOB) AS x)
BEGIN TRY
    DROP TABLE Bookstore
END TRY
BEGIN CATCH
END CATCH

SELECT * INTO Bookstore FROM (
SELECT
    X.Book.query('category').value('.','VARCHAR(20)') AS 'Category',
    X.Book.query('title').value('.','VARCHAR(20)') AS 'Title',
    X.Book.query('author').value('.','VARCHAR(20)') AS 'Author',
    X.Book.query('year').value('.','VARCHAR(20)') AS 'Year',
    X.Book.query('price').value('.','VARCHAR(20)') AS 'Price'
FROM @Doc.nodes('Bookstore/Book') AS X(Book)
) sub
SELECT * FROM Bookstore ORDER BY Category

The code below works! XML is case-sensitive. In the XML file, both bookstore and book are small case.

/*
     Change 'C:\xml\' to the folder containing 'bookstoresample.xml.'
*/
DECLARE @Doc XML
SET @Doc = (SELECT * FROM OPENROWSET(BULK 'C:\xml\bookstoresample.xml',SINGLE_BLOB) AS x)

BEGIN TRY
    DROP TABLE Bookstore
END TRY
BEGIN CATCH
END CATCH

SELECT * INTO Bookstore FROM (
SELECT
    X.book.query('category').value('.','VARCHAR(40)') AS 'Category',
    X.book.query('title').value('.','VARCHAR(40)') AS 'Title',
    X.book.query('author').value('.','VARCHAR(40)') AS 'Author',
    X.book.query('year').value('.','VARCHAR(40)') AS 'Year',
    X.book.query('price').value('.','VARCHAR(40)') AS 'Price'
FROM @Doc.nodes('bookstore/book') AS X(book)
) sub

SELECT * FROM Bookstore ORDER BY Category

XQuery is to XML what SQL is to databases. XQuery was designed to query XML data.

Leave a comment

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