In previous post we saw how we can get XML out of SQL Server.
In this post we will see the reverse of same i.e. how we can put XML back to SQL server.
Over to Pinal Dave's original post below
Namaste !
Anugrah Atreya
From: Journey to SQLAuthority [mailto:no-reply@wordpress.com]
Sent: Saturday, September 03, 2011 12:21 PM
To: Anugrah Atreya
Subject: Daily digest for September 3, 2011
SQL SERVER - Tips from the SQL Joes 2 Pros Development Series - Preparing XML in Memory - Day 34 of 35 pinaldave | September 3, 2011 at 7:00 am | Categories: Joes 2 Pros, Pinal Dave, PostADay, SQL, SQL Authority, SQL Query, SQL Scripts, SQL Server, SQL Tips and Tricks, T SQL, Technology | URL: http://wp.me/p2NUQ-3GE |
Consuming XML
Anytime you turn XML into another data format (e.g., into a SQL Server table) that process is called shredding XML. Before you can shred your XML, you must first prepare it. Preparing XML for SQL means storing the XML in a memory space where a query can retrieve and make use of the data. SQL Server 2008 provides native XML support to make streaming and shredding XML more robust.
Think of XML as a big meal for SQL. While we essentially added just one line of code to our Select query to turn tabular data into an XML document, going the other direction is more complex. Behind the scenes, SQL Server uses additional memory when consuming XML. Our preparation will include a step to parse and store the XML document in memory. And the same way a big feast requires additional cleanup, we must include a step to remove the XML document from the memory cache once it is no longer needed.
Now that we understand the essential rules and components of well-formed XML documents, we will turn our attention toward consuming XML data. In the next posts we will see an advanced method (nodes) for shredding XML. Right now we want you to be familiar with the terms used when going back and forth from SQL to XML.
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
| WordPress.com | Thanks for flying with WordPress! |
Trouble clicking? Copy and paste this URL into your browser: http://subscribe.wordpress.com
Posts
No comments:
Post a Comment