SQL Server XML Shredding Part 1


We can import XML files into SQL Server tables. This process is often called shredding. In this first post we have an xml document in memory. Here we are not importing the xml data to tables. Also, we do not begin with an external XML file to import; we insert the XML document right into a variable. In future we could include this functionality as it would more closely resemble something you would need to do.

We will start with a very simple in-memory XML document that contains bookstore information. We used this data in a previous post on shredding. We will write a script that uses variables to hold the XML data. We could have imported this XML from a file, but for now we will just put it right into our sql script.

Things to notice about this script:

  • The XML is in memory
  • We could import the XML from a file if we wanted to (see Part 2)
  • We can run SELECT statements on only part of the XML
  • We could copy part of or all of the XML into another table using SELECT INTO

Below is our sql script and our output beneath that.

DECLARE @bookXML varchar(1000);  -- holds raw unprocessed XML
DECLARE @xmlInMemory INT;  -- the handle to the newly created document.

SET @bookXML =
'<?xml version="1.0" encoding="UTF-8"?>
<bookstore>
	<header>
		<store>I Love Books Inc.</store>
	</header>
	<book>
		<category>cooking</category>
		<title>Everyday Italian</title>
		<author>Giada De Laurentiis</author>
		<yearpub>2005</yearpub>
		<price>30.00</price>
	</book>
	<book>
		<category>children</category>
		<title>Harry Potter</title>
		<author>J K. Rowling</author>
		<yearpub>2005</yearpub>
		<price>29.99</price>
	</book>
	<trailer>
		<numberofbooks>2</numberofbooks>
	</trailer>
</bookstore>';

EXEC sp_xml_preparedocument @xmlInMemory OUTPUT, @bookXML

SELECT *
	FROM OPENXML (@xmlInMemory, '/bookstore/book', 2)
	WITH (category varchar(20),
	title varchar(20),
	author varchar(20),
	yearpub varchar(4),
	price varchar(8));

SELECT *
	FROM OPENXML (@xmlInMemory, '/bookstore/header', 2)
	WITH (store varchar(20));

EXEC sp_xml_removedocument @xmlInMemory;  -- clean up

Here is the output in the Results pane of SSMS, in text format.

category             title                author               yearpub price
-------------------- -------------------- -------------------- ------- --------
cooking              Everyday Italian     Giada De Laurentiis  2005    30.00
children             Harry Potter         J K. Rowling         2005    29.99

(2 row(s) affected)

store
--------------------
I Love Books Inc.

(1 row(s) affected)
Series NavigationSQL Server XML Shredding Part 2 >>

Leave a comment

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