Shredding XML using OPENXML: Convert XML to Rowset part II

In this previous blog post, we learned how to use the OPENXML function to covert XML nodes to a single column in tabular format. Today, we'll learn how to convert all the nodes in a XML document to tabular format. We'll use the same XML document on technet with some modifications for the examples below.

-- prepare XML document
DECLARE @XmlDoc XML =
N'<bookstore specialty="novel">
  <book style="autobiography">
    <title>Create a Vision</title>
    <author>
      <first-name>Joe</first-name>
      <last-name>Smith</last-name>
    </author>
    <price>12</price>
  </book>
  <book style="textbook">
    <title>XML Reference</title>
    <author first-name="Mary" last-name="Jane" />
    <price>55</price>
  </book>
  <book style="novel" id="myfave">
    <title>Paths to Success</title>
    <author>
      <first-name>Toni</first-name>
      <last-name>Wilson</last-name>
    </author>
    <price intl="Canada" exchange="0.7">6.50</price>
  </book>
</bookstore>';

--
--An integer for pointing to the internal 
-- representation(XML Document Object Model)
--  of the XML document above.
DECLARE @DocHandle INT; 

-- Parse the XML document to XML DOM tree representation 
-- and returns a handle
EXECUTE sys.sp_xml_preparedocument @DocHandle OUTPUT, @XmlDoc;

-- Select both authors first and last name
-- Note:
--  The @ symbol indicates select attribute nodes.
--  The | operator union the both attribute and element nodes.
--  The column pattern XPath expression in the WITh clause will overwrite 
--      the flag(3rd parameter) of the OPENXML function.
SELECT *
FROM OPENXML(@DocHandle, '/bookstore/book/author', 1)
WITH(AuthorFirstName VARCHAR(20) '@first-name | first-name'
    ,AuthorLastName VARCHAR(20) '@last-name | last-name');

-- Select all information for all books
-- Note: since we start at /bookstore/book, 
--    to get to author first and last name,
--    I modified the XPath expression to 
--    'author/@first-name | author/first-name'
SELECT *
FROM OPENXML(@DocHandle, '/bookstore/book', 3)
WITH(Style VARCHAR(20) '@style'
    ,Id VARCHAR(20) '@id'
    ,Title VARCHAR(30) '@title | title'
    ,AuthorFirstName VARCHAR(20) 'author/@first-name | author/first-name'
    ,AuthorLastName VARCHAR(20) 'author/@last-name | author/last-name'
    ,Price DECIMAL(6,2) 'price');

-- Change the columns display ordering.
SELECT Title, Style, Id, AuthorFirstName, AuthorFirstName, Price
FROM OPENXML(@DocHandle, '/bookstore/book', 3)
WITH(Style VARCHAR(20) '@style'
    ,Id VARCHAR(20) '@id'
    ,Title VARCHAR(30) '@title | title'
    ,AuthorFirstName VARCHAR(20) 'author/@first-name | author/first-name'
    ,AuthorLastName VARCHAR(20) 'author/@last-name | author/last-name'
    ,Price DECIMAL(6,2) 'price');

-- Removes the internal representation
--  and invalidate the document handle.
EXECUTE sys.sp_xml_removedocument @DocHandle;

Result for 3 queries above:

Next several posts, we'll learn how to use XQuery for querying XML.

Reference:
XPath Operators
OPENXML
OPENXML Function
OPENXML Examples
sp_xml_preparedocument
sp_xml_removedocument
XML Edge Table
XML Document Object Model(XML DOM)