Read XML into a table using sp_xml_preparedocument

sp_xml_preparedocument is a Microsoft shipped extended stored procedure to parse xml document in SQL Server. In this blog post we will learn about XML shredding in SQL Server. Previously we saw how to convert tabular data into xml document. I would recommend you to read the previous post if you are new to XML in SQL Server.

Syntax:

sp_xml_preparedocument hdoc OUTPUT, xmltext, xpath_namespaces
hdoc: This is an output parameter of INT datatype, this generates a handle of parsed XML.
Xmltext: XML document to parse.
xpath_namespaces: The namespace of XML, if you are parsing an untyped XML then this parameter is not mandatory. Default is <root xmlns:mp=”urn:schemas-microsoft-com:xml-metaprop”>

OPENXML: This is the next step after parsing and getting a handle from sp_xml_preparedocument procedure. OPENXML is a function to read the xml into a table.

SELECT * FROM OPENXML (@hdoc, rowpattern, flag) WITH (Column patterns)
@hdoc
: Is an output variable of hdoc (XML handle)

ROWPATTERN: An xml can have many tags like root tags, top-level tags and many child tags. So row pattern defines an XPATH, telling your query, from where to start looking for the data in XML while shredding.

COLUMNPATTERN: we know row pattern is useful for targeting a level and start searching the data from the target level, but what if some of the data are out of the targeted row pattern level and are present in one upper or lower level? You can’t change the row pattern, so you will have to use column pattern to reach out one upper or lower level to fetch the required data.

Flag: We will be discussing about the flags later in this post.

sp_xml_removedocument @hdoc
This is another Microsoft shipped extended stored procedure to remove the xml handle and release the memory that was generated by sp_xml_preparedocument while parsing and storing the xml document. It’s important to release the memory after shredding the XML otherwise this will keep the memory allocated until the session is closed.

sp_xml_preparedocument-02

I will be using the above sample XML in all the examples here, so make sure you copy the above piece of code before executing any examples because these two variables are needed in all the examples.

sp_xml_preparedocument-01

Example 01:

Here in above query, row pattern is “Catalog/Book”, that means, the starting point is “Book” tag. For each column we have different different column patterns defined, “id” and “author” comes under “Book” element, so we did not have to traverse any level up or down to get the data. But “BookName”, “Genre” and “Price” are under another element called “Details” which is a level deeper than the current row pattern, hence we have to traverse a level down from the current row pattern to get those data.

Note:
@: Defines the xml attribute.

“./[tagname]” : One level down from the current row pattern. (eg: “./Details/@BookName”)

“.//” : This is same like above, traversing one level down, only difference is that  this does not require the tag name of the next level element. (eg: “.//Price”)

“.//.//”: Like wise, it jumps 2 level down and so forth.

Example 02:

Here in above query, I have changed the row pattern a little bit. Now the row pattern is “/Catalog/Book/Details” , which means, the starting point is now “Details” tag instead of “Book”. Now “BookName”, “Genre”, “Price” and “PublishDate” are being fetched directly just by their element and attribute names in the column pattern. Remember, for  “id” and “author” we have to traverse a level up to get their data.

“../”: One level up from the defined row pattern
“../../”: Two level up and so forth.

sp_xml_preparedocument-03

OPENXML Flag (Optional):

SELECT * FROM OPENXML (@hdoc, rowpattern, flag) WITH (Column patterns)

So far we have just used OPENXML with default parameter.

If FLAG parameter is passed into the OPENXML function then column pattern becomes optional.
Flag 1: Searches for attributes.
Flag 2: Searches for elements.
Flag 3: Searches for attributes and elements.
Default: As per the mapped column pattern.

Flag (1):

In our XML document we have two attributes “BookName” and “Genre” under “Details” tag, hence only these two attribute data are visible in below result set. Other data are NULL because they do not fall under the defined row pattern area, however the can be accessed by defining their column pattern.

sp_xml_preparedocument-04

Flag (2):

In our XML document we have only two elements named “Price” and “PublishDate” under “Details” tag, so only these two elements data are visible in the result set below, other data are NULL because they do not fall under the defined row pattern area, however the can be accessed by defining their column pattern.

sp_xml_preparedocument-05

Flag (3):

In our XML document we have two elements named “Price” and “PublishDate” and two attributes named “BookName” and “Genre” under “Details” tag, hence all the elements and attribute data are visible in this case except “id” and “author” because they do not fall under the defined row pattern area, however “id” and “book” can be accessed by going a level up from the current row pattern.

sp_xml_preparedocument-06

Prasad Sahoo

Prasad Sahoo, is an enthusiast of Microsoft technologies in general and a passionate database professional, mainly focusing on SQL Server performance tuning and business intelligence. He has contributed a long time of his career working on SQL Server and other RDBMS. If he is not busy with SQL stuff then he must be watching some Hollywood movies.

8 thoughts on “Read XML into a table using sp_xml_preparedocument

  • October 1, 2015 at 9:30 pm
    Permalink

    Great explanation, so simple even I could understand it.

    Reply
  • February 1, 2016 at 10:28 pm
    Permalink

    Amazing tutorial. Amazing explanation!

    Reply
  • May 31, 2016 at 12:17 am
    Permalink

    how to count number of books available or number of child nodes available under parent node.

    Reply
  • June 6, 2016 at 11:05 am
    Permalink

    useful tutorial !

    Reply
  • June 14, 2016 at 4:26 pm
    Permalink

    Excellent article .. short and simple

    Reply
  • February 10, 2017 at 9:08 pm
    Permalink

    such fantastic article. It took me a while to find something so good. I will keep it as a treasure. Many thanks!

    Reply

Leave a Reply

%d bloggers like this: