SQL SERVER – Tips from the SQL Joes 2 Pros Development Series – Shredding XML – Day 33 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.

Shredding XML

Our introduction to XML in the last 3 days of posts thus far has focused on seeing tabular data taken from SQL Server and streamed into well-formed XML instead of the rowset data we typically work with.  The next two posts will focus on the reverse process.  Our starting point will be data which is already in XML and which we will turn into table data, or which we will query in order to answer questions.

Our previous posts focused on streaming data into XML and we produced XML using the modes Raw, Auto, and Path. We used familiar data from the JProCo database (Employee, Location, Grant, and Customer data).  We refined and manipulated our XML queries in order for the XML result to appear according to our precise specifications.

Operations which parse and consume XML data are known collectively by the term shredding XML. The next few posts will focus on the steps to prepare an XML stream so you can turn it into a table or some other tabular result set. XML is case-sensitive so in order for your examples and exercises to run properly be sure that your XML fragments precisely match the figures with respect to uppercase and lowercase.

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.

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 33

What process will transform XML data to a rowset?

1. Shredding
2. Retrieving

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)