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 http://blog.sqlauthority.com which is next day GTM+2.5.

Reference:  Pinal Dave (http://blog.SQLAuthority.com)

About these ads

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

  1. Correct answer is No. 1, because we need two variables: the XML variable where we’ll send an XML document to be tailored into memory and an INT variable which will be the the handle to acces that tailored data in memory (as a claim ticket).
    Rene Castro
    El Salvador

    Like

  2. Correct ans is No. 1 “The handle as an INT”

    DECALRE @Data NVARCHAR(MAX)
    DECLARE @iDocument INTEGER

    EXEC SP_XML_PREPAREDOCUMENT @iDocument OUTPUT,@Data
    INSERT INTO @TableVariable SELECT * FROM OPENXML (@iDocument, ‘/XmlDocument/XmlValue’,2)
    WITH
    (
    ID INTEGER,
    Marks INTEGER
    )
    EXEC SP_XML_REMOVEDOCUMENT @iDocument

    Amit Malhtora
    India

    Like

  3. Correct Answer is # 1.

    1.The handle as an INT

    Because, If you want to take XML data and create a result set in SQL Server, you must first store the XML in memory and processing the XML so that all the data and metadata is ready and available to query. For this we have to declare two variables.

    An XML variable for SQL – To store XML Document
    An integar variable for you – To handle claim number (called Handle)

    Gopalakrishnan Arthanarisamy
    Unisys, Bangalore, India

    Like

  4. Correct answer is option 1

    1. The handle as an INT

    sp_XML_PrepareDocument, a system-supplied stored procedure which reads our XML document, 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” which we need later when referring to that document.

    Country – INDIA (Gujarat)

    Like

  5. Pingback: SQL SERVER – Win a Book a Day – Contest Rules – Day 0 of 35 Journey to SQLAuthority

  6. Ans is:
    1.The handle as an INT

    To send the XML we need a variable which data type is XML.
    and we will declare the variable which will be an integer because it is the variable which will act as our document handle (i.e., our “claim ticket”).

    Partha,
    India

    Like

  7. 1.The handle as an INT is the correct answer

    Explanation :

    sp_XML_PrepareDocument which is a system supplied stored procedure reads the xml which is given as a input, parses it and stores it in the internal cache of SQL Server and returns a reference number which is the Handle to the newly created xml document.

    This handle number is used as a reference.

    More information on this is available in the link below :

    http://msdn.microsoft.com/en-us/library/ms187367.aspx

    Thanks for this post.

    Best Regards,
    P.Anish Shenoy,
    INDIA.

    Like

  8. ANS : 1
    (1) The handle as an INT

    As According To MSDN :
    /*
    sp_xml_preparedocument
    hdoc
    OUTPUT
    [ , xmltext ]
    [ , xpath_namespaces ]

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

    Declare @hdoc integer
    Declare @Doc xml
    –Set @Doc with required xml
    EXEC sp_xml_preparedocument @hdoc OUTPUT, @Doc

    when you send an XML document to be tailored into memory, SQL Server gives you a claim number (called a handle) in the form of an integer which you need later when referring to that document.

    Mitesh Modi
    (India)

    Like

  9. AS per description”: “We will send our XML document into memory and in exchange we will get back the handle in the form of an integer. ”

    Option 1 is the correct Answer “The handle as an INT”

    Thanks…
    Rajneesh Verma
    (INDIA)

    Like

  10. Correct Answer : option 1

    33) 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

    First parameter integer holds the handle value of XML data passed in second parameter. using this integer value in openXml function to get rowset of records.

    Chennai, TamilNadu, India

    Like

  11. Option 1 – is correct answer

    Since we need to get the handle number(i.e claim ticket) of the xml saved in the memory, and our handle will be an integer and even to open the ticket we need the int handle

    Hyderabad,
    India

    Like

  12. 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

    Like

  13. 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

    Like

  14. 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

    Like

  15. 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.

    Like

  16. 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

    Like

  17. Pingback: SQL SERVER – A Guide to Integrating SQL Server with XML, C#, and PowerShell – Book Available for SQL Server Certification Journey to SQLAuthority

  18. 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?

    Like

  19. Pingback: SQL SERVER – Understanding XML – Contest Win Joes 2 Pros Combo (USD 198) – Day 5 of 5 « SQL Server Journey with SQL Authority

  20. Pingback: SQL SERVER – Weekly Series – Memory Lane – #045 | Journey to SQL Authority with Pinal Dave

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s