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?
- Shredding
- 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)
67 Comments. Leave new
ANS : 1
(1) Shredding
Explanation
==========
Anytime you turn XML into another data format (e.g., into a SQL Server table) that process is called shredding XML
Mitesh Modi
(India)
Ans: 1
1. Shredding
:India
How to achieve this :
————————————————————-
Declare @testXml xml ;
select @testXml =
(
select top(10) CustomerID, CustomerName, GeographyID from CustomersDetails
for xML path(‘Customer’), root(‘CustomerInformation’)
)
—————————
select
testXmlTable.value(‘CustomerID[1]’, ‘varchar(7)’) AS CustomerID
,testXmlTable.value(‘CustomerName[1]’, ‘varchar(100)’) AS CustomerName
FROM @testXml.nodes(‘//Customer’) AS R(testXmlTable)
————————————————————————————
————————————————————————————
if in your production database you already have XML column and want to perform Shredding on that use the “CROSS APPLY”
e.g.
select top(10)
testXml.value(‘cdm_ID[1]’,’varchar(7)’) AS CustomerID
,testXml.value(‘cdm_Name[1]’, ‘varchar(7)’) AS CustomerName
from CustomersDetails
CROSS APPLY CustomerXml.nodes(‘//dtCustomerDetails’) AS R(testXml)
Correct Answer # 1
1. Shredding
Alpesh Gorasia
(India)
Nice article: Correct answer is 1 – shredding.
Is your competition open to UK entires?
The correct answer for the above question is Option #1 – Shredding.
The process of converting XML data into a format that can be used by a relational database is called ‘Shredding”, or decomposition. One can either use the NODES method on an XML data type or, from a Document Object Model (DOM), use the OpenXML function. OpenXML is retained in SQL 2005, but the NODES method is generally preferable because of its simplicity and performance.
Diljeet Kumari
Country : INDIA
Hi,
The correct Answer is Option 1 i.e. Shredding
Explanation: Operations which parse and consume XML data are known collectively by the term shredding XML.
Thanks,
Hema Chaudhry
Delhi, India
The correct answer is option – 1 . Shredding
Sushant Kumar Pradhan
Technical Architect, Wipro Technologies
India
The correct answer is:
1. Shredding
Gourav Rampal
Chandigarh,India
Correct Answer is
1: Shredding
you turn XML into another data format that process is called shredding XML.XML support to make streaming and shredding XML more robust.
Preparing XML for SQL means storing the XML in a memory space where a query can retrieve and make use of the data.
Vishal
India
The correct answer is #1 – Shredding
Regards
Rajesh
From india
Correct answer is option 1 Shredding
Robin Thomas
India
]
Option 1, Shredding
Matt Nelson, USA
Answer is 1. Shredding
Shredding is preparing an XML stream so itcan be turned it into a table or some other tabular result set.
Looking forwared to learning more.
Deb
USA
Correct answer is option 1 shredding. Anytime you turn XML into another data format (e.g., into a SQL Server table) that process is called shredding XML
As you have already said above : Anytime you turn XML into another data format (e.g., into a SQL Server table) that process is called shredding XML.
Correct Answer is 1 – Shredding
Ishan Shah,
Gandhinagar,
India
The answer is option #1 Shredding.
Ramdas, NC
USA
Hi Pinal,
Challenge:
Question 33
What process will transform XML data to a rowset?
1. Shredding
2. Retrieving
Correct Answer:
The correct choice is #1: Shredding
Explanation:
Shredding is the process of parsing, consuming and transforming XML data into format suitable for inserting the data into SQL Server.
Country:
United States
Thanks for the knowledge!
Regards,
Bill Pepping
Option 1 –> Shredding
Kevin Scharnhorst
United States
Option 1 is the correct answer.
Country: United States
The correct option for the above question is Option 1 ) Shredding
Shredding process will transform XML data to a rowset.
AS very well explained by you.
Dilip Kumar Jena
Country : INIDA