SQL SERVER – Tips from the SQL Joes 2 Pros Development Series – Preparing XML in Memory – Day 34 of 35

Answer simple quiz at the end of the blog post and –

Every day one winner from India will get Joes 2 Pros Volume 5.

Every day one winner from United States will get Joes 2 Pros Volume 5.

Preparing XML in Memory

If you want to take XML data and create a result set in SQL Server, you must first store the XML in memory. The process of preparing XML in SQL includes storing the XML in memory and processing the XML so that all the data and metadata is ready and available for you to query.

Recall that element levels in your XML document appear in the same order that tables appear in your SELECT list and are named according to any aliases you may have chosen (e.g., cust, Order, OrderDetail).

A related query from the figure below helps to illustrate our root element (cust) having a top-level element (Order) and a child-level element (OrderDetail).  There is a 1:Many (One-to-Many) relationship between the root node and the lower elements.

Copy your XML result into a brand new query window and enclose it in single quotes (see figure below). Let’s briefly digress for another comparison which may be helpful for our next step. Preparing to have your XML document shredded by SQL Server is a bit like the steps you take when having your tailor create a custom garment for you. One of my students recently underwent this process.  Her favorite tailor, Kim, had the design for a poncho she liked.  The tailor sent my student to the fabric store to select the particular fabric and trim she wanted for her poncho.When she brought the fabric and trim to Kim’s shop, Kim took the fabric, wrote up an order slip, and then gave my student a claim ticket and said her poncho would be done in a week. You stored your garment at the tailor and when you want to use it you must show your claim ticket.

This tailor runs a small neighborhood shop but is always very busy due to the high quality of her work. While Kim could eventually have located the order without the benefit of the claim ticket, my conscientious student made very sure to bring her ticket when she returned the following week. She submitted her claim ticket and in exchange she was handed her lovely new hand-made garment.

Much the same way, when you send an XML document to be tailored into memory, SQL Server gives you a claim number (called a handle) which you need later when referring to that document.  We will send our XML document into memory and in exchange we will get back the handle in the form of an integer. To send our document into memory, we first need to declare an XML variable. In order for this variable to contain our XML document, we will set it equal to our XML.

The @Doc variable’s data type is XML. Next we will declare the variable @hDoc, which we know will be an integer because it is the variable which will act as our document handle (i.e., our “claim ticket”). We will also use sp_XML_PrepareDocument, a system-supplied stored procedure which reads our XML document (@Doc), parses it, and makes it available for SQL’s use.

When we send our XML to SQL Server’s internal cache, we will receive a number which functions as our “claim ticket.” Run all of the code together, including a SELECT statement to display the document handle (i.e., our “claim ticket” which SQL Server provides in exchange for the XML document). Run all of the code together and make note of the handle number, which is 1.

Be sure to run this code only once, otherwise you will create multiple handles and instances of your XML document. Avoid accidentally creating unneeded instances of your document and handle.

Using the OpenXML Function

We just sent our prepared XML into the SQL Server’s internal cache so that we may pull out the data we want. The OpenXML function provides a rowset view of your XML data. It works with the in-memory copy of the XML document you’ve stored and provides a view of the data, no longer formatted as XML but with all its parts separated into a large grid. This allows you to query just the data that you need.

We know the key to accessing the stored XML document is the document handle (like a claim ticket). The first argument needed by the OpenXML function is this value expressed as an integer. The second argument is the rowpattern hint for the data we wish to see.

After declaring an integer variable and setting it equal to 1 (i.e., the value of our document handle, from 2 figures ago), we can use a SELECT statement to query the result set of the OpenXML function. The variable @iNum is the first parameter. The second parameter ‘/cust/Order/OrderDetail’ specifies that we wish to see data for the OrderDetail element level.

Rowpattern

Since XML can have root tags, top level tags, and many levels of child tags, rowpatterns are needed to figure out which level represent your row data.  A rowpattern is an XPath pattern telling your query where to look for the data that you want to see in your result.

In our current example, the rowpattern hint (‘/cust/Order/OrderDetail’) narrows our query to the attributes found at the OrderDetail element level (see figure above). While the surrounding data isn’t immediately interpretable, we can see the text for the ProductID attribute shows a 72, and the text for the Quantity attribute shows a 4 (see lower right corner of the figure above).

Shredding One Level

Adding the WITH clause to our existing query allows us to pick just the values we wish to see.  Our query specifies that we are still interested in data from the OrderDetail element level (see figure below). Our WITH clause lists the field names we want (ProductID, Quantity) from this element level and that these values should be expressed as integer data. In other words, ProductID and Quantity are both integers. The WITH clause allows us to shred data at a single element level.

Note: If you want to setup the sample JProCo database on your system you can watch this video. For this post you will want to run the SQLInteropChapter1.0Setup.sql script from Volume 5.

Question 34

The sp_XML_PrepareDocument stored procedure requires a parameter that is an XML data type. What is the output parameter for?

  1. The handle as an INT
  2. The handle as an XML
  3. The handle as a Varchar

Rules:

Please leave your answer in comment section below with correct option, explanation and your country of resident.
Every day one winner will be announced from United States.
Every day one winner will be announced from India.
A valid answer must contain country of residence of answerer.
Please check my facebook page for winners name and correct answer.
Every day one winner from India will get Joes 2 Pros Volume 5.
Every day one winner from United States will get Joes 2 Pros Volume 5.
The contest is open till next blog post shows up at which is next day GTM+2.5.

Reference:  Pinal Dave (https://blog.sqlauthority.com)

Joes 2 Pros, SQL Scripts
Previous Post
SQL SERVER – Tips from the SQL Joes 2 Pros Development Series – Shredding XML – Day 33 of 35
Next Post
SQL SERVER – Tips from the SQL Joes 2 Pros Development Series – OpenXML Options – Day 35 of 35

Related Posts

36 Comments. Leave new

  • Hi,

    Option 1 is correct.

    Thanks
    Sudhir Chawla
    New Delhi, India

    Reply
  • Answer is #1 The handle as an INT

    The handle serves as our “claim ticket” for getting the data back.

    Pretty cool, thanks!

    USA

    Reply
  • dilipkumarjena
    September 3, 2011 8:15 pm

    The correct option for the above question is Option 1)
    The handle as an INT

    Explanation
    Reads the Extensible Markup Language (XML) text provided as input, then parses the text using the MSXML parser (Msxml2.dll), and provides the parsed document in a state ready for consumption. This parsed document is a tree representation of the various nodes (elements, attributes, text, comments, and so on) in the XML document.

    sp_xml_preparedocument returns a handle that can be used to access the newly created internal representation of the XML document. This handle is valid for the duration of the connection to Microsoft® SQL Server™ 2000, until the connection is reset, or until the handle is invalidated by executing sp_xml_removedocument.

    Note A parsed document is stored in the internal cache of SQL Server 2000. The MSXML parser uses one-eighth the total memory available for SQL Server. To avoid running out of memory, run sp_xml_removedocument to free up the memory.

    Syntax

    sp_xml_preparedocument hdoc OUTPUT
    [, xmltext]
    [, xpath_namespaces]

    Arguments

    hdoc

    Is the handle to the newly created document. hdoc is an integer.

    [xmltext]

    Is the original XML document. The MSXML parser parses this XML document. xmltext is a text (char, nchar, varchar, nvarchar, text, or ntext) parameter. The default value is NULL, in which case an internal representation of an empty XML document is created.

    [xpath_namespaces]

    Specifies the namespace declarations that are used in row and column XPath expressions in OPENXML. The default value is .
    xpath_namespaces provides the namespace URIs for the prefixes used in the XPath expressions in OPENXML by means of a well-formed XML document. xpath_namespaces declares the prefix that must be used to refer to the namespace urn:schemas-microsoft-com:xml-metaprop, which provides meta data about the parsed XML elements. Although you can redefine the namespace prefix for the metaproperty namespace using this technique, this namespace is not lost. The prefix mp is still valid for urn:schemas-microsoft-com:xml-metaprop even if xpath_namespaces contains no such declaration. xpath_namespaces is a text (char, nchar, varchar, nvarchar, text, or ntext) parameter.

    Dilip Kumar Jena
    Country : INDIA

    Reply
  • Basavaraj Biradar
    September 3, 2011 9:27 pm

    Correct Answer is: Option 1

    Thanks,
    Basavaraj
    India

    Reply
  • correct option is #1

    Reply
  • Hi Pinal,

    Challenge:
    Question 34
    The sp_XML_PrepareDocument stored procedure requires a parameter that is an XML data type. What is the output

    parameter for?

    1.The handle as an INT
    2.The handle as an XML
    3.The handle as a Varchar

    Correct Answer:
    The correct choice is #1: The handle as an INT

    Explanation:
    sp_XML_PrepareDocument requires two parameters. The first parameter is an INTEGER that is declared as OUTPUT. This parameter will be the handle to use in further queries to retrieve the XML data you want. The second
    parameter for sp_XML_PrepareDocument is the variable defined as type XML, with contains the XML data you want to store in SQL Server’s memory.

    Country:
    United States

    Thanks for the knowledge!

    Regards,

    Bill Pepping

    Reply
  • Sudeepta Ganguly
    September 4, 2011 1:59 am

    The correct answer is:
    1.The handle as an INT

    Sudeepta,
    India

    Reply
  • As well explained in the article, the correct answer to this question is #1

    The handle as an INT

    I am from USA

    Thanks for sharing the knowledge

    Reply
  • Nakul Vachhrajani
    September 4, 2011 1:09 pm

    The other parameter is #1 – The handle as an INT – which is the correct answer to the question.

    Country of residence: India

    Reply
  • Hi,

    Option 1 is correct.

    Thanks

    Sudhir Chawla
    New Delhi, India

    Reply
  • Rajesh Mohanrangan
    September 5, 2011 1:04 pm

    correct answer:Option 1

    Regards
    Rajesh
    From india

    Reply
  • Option 1

    1.The handle as an INT

    Country : India

    Reply
  • sp_XML_PrepareDocument stored procedure requires a parameter that is an XML data type. What is the output parameter for?

    Correct answer is :The handle as an INT which represents in memory location of the parsed XML document in SQL server.

    Reply
  • sp_XML_PrepareDocument stored procedure requires a parameter that is an XML data type. What is the output parameter for?

    Correct answer is :The handle as an INT which represents in memory location of the parsed XML document in SQL server.

    my previous post doesnot include country of residence.

    Vaishali Jain
    Hyderabad, india

    Reply
  • A.) (1) The handle as an INT

    Winner from USAUday Bhoopalam

    Winner from India: Mitesh Modi

    I thank you all for participating here. The permanent record of this update is posted on facebook page.

    Reply
  • I ran the below command many times:

    [quote]
    Declare @hdoc int
    exec sp_xml_preparedocument @hdoc output, @xmlFile

    select @hdoc
    [/quote]

    Resulted in the value of @hdoc variable has been set to 72…
    Can you please tell me how can I reset it?

    Reply

Leave a Reply

Menu
Exit mobile version