SQL SERVER – Tips from the SQL Joes 2 Pros Development Series – Shredding XML – Day 33 of 35

« SQLAuthority News – Programming & Development For Microsoft SQL Server 2008

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

SQL SERVER – Tips from the SQL Joes 2 Pros Development Series – Shredding XML – Day 33 of 35

September 2, 2011 by pinaldave

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

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

About these ads

Posted in Joes 2 Pros, Pinal Dave, PostADay, SQL, SQL Authority, SQL Query, SQL Scripts, SQL Server, SQL Tips and Tricks, T SQL, Technology | 71 Comments

71 Responses

on September 2, 2011 at 7:34 am | Reply Reddy

Correct answer is Option #1, Shredding

Thanks.

Country – India
on September 2, 2011 at 7:46 am | Reply Gopalakrishnan Arthanarisamy

Correct Answer is # 1.

1. Shredding

Streaming – Converting SQL Server Table Data into XML Format.
Shredding – Converting XML Data into SQL Server Tabular Result Set.

Operations which parse and consume XML data are known collectively by the term shredding XML.

Turning XML into another data format (e.g., into a SQL Server table) that process is called shredding XML.

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.

Gopalakrishnan Arthanarisamy
Unisys, Bangalore, India.
on September 2, 2011 at 7:53 am | Reply Naomi

The correct answer is #1 – Shredding.

Good article, I’m looking forward for the next one

I am from USA
on September 2, 2011 at 8:04 am | Reply vijaya

Transformation from Xml Data to Rowset is called shredding XML.
Country-India
on September 2, 2011 at 8:08 am | Reply Sudhir Chawla

Hi,

Option 1 is correct as its mentioned:

Anytime you turn XML into another data format (e.g., into a SQL Server table) that process is called shredding XML.

Thanks

Sudhir Chawla
New Delhi, India
on September 2, 2011 at 8:15 am | Reply Nikhil Mahajan

the correct answer is Option 1,i.e.

1. Shredding

as explained by you in this post…

India
on September 2, 2011 at 8:26 am | Reply Rene Alberto Castro Velasquez

Correct answer is No. 1: shredding XML is the process to turn XML into another data format (e.g., into a SQL Server table).
Rene Castro
El Salvador
on September 2, 2011 at 8:35 am | Reply Varinder Sandhu

Correct Answer is Option 1 as

1. Shredding

because anytime that convert XML into another data format that process is called shredding XML.

Varinder (India)
on September 2, 2011 at 8:38 am | Reply shatrughna kumar

Correct answer is option #1.
1.Shredding

New Delhi
India
on September 2, 2011 at 9:12 am | Reply Nikhildas

The Correct Answer option : 1 . Shredding

XML data turn into another data format (e.g., into a SQL Server table) that process is called shredding.

Nikhildas
Cochin
INDIA
on September 2, 2011 at 9:32 am | Reply Razeena

Answer is option #1 -shredding

Kerala , India
on September 2, 2011 at 9:40 am | Reply Kalyanasundaram.K

Correct Answer : Option 1

What process will transform XML data to a rowset?
1.Shredding

Anytime you turn XML into another data format (like SQL) that process is called shredding XML

Chennai, TamilNadu, India
on September 2, 2011 at 9:42 am | Reply vinay (@vinayprasadv)

Answer is:

1.Shredding

Vinay,

Pune, India
on September 2, 2011 at 9:45 am | Reply Aditya Bisoi (@AdityaBisoi07)

Question 33

Ans : #1 — Shredding

Chennai, INDIA
on September 2, 2011 at 9:53 am | Reply Ankit Kashyap

The answer is #1- Shredding, where we convert XML into another formats (parse and consume it)

Thanks
-Ankit (Hyderabad)
on September 2, 2011 at 10:04 am | Reply Shravan Yadav

Anytime you turn XML into another data format (e.g., into a SQL Server table) that process is called shredding XML

this line clearly shows that the correct option is
1> i.e shredding
on September 2, 2011 at 10:07 am | Reply Sunil (India)

Answer is Option :: 1
on September 2, 2011 at 10:28 am | Reply Mahmad Khoja

The correct answer to this question is option 1

That is Shredding

Mahmad Khoja
Country : INDIA
on September 2, 2011 at 10:32 am | Reply Riyas Vk

Correct Answer is # 1.

1. Shredding

Thanks and regards

RIYAS.V.K,India
on September 2, 2011 at 10:35 am | Reply P.Pranav Kumar

The correct answer is option1 Shredding.

from hyderabad,India.
on September 2, 2011 at 10:35 am | Reply Partha Pratim Dinda

Ans: 1.shredding

Operations which parse and consume XML data are known collectively by the term shredding XML.

Partha,
India
on September 2, 2011 at 10:39 am | Reply Nakul Vachhrajani

Correct answer is #1 – Shredding.

These series of posts on XML are nothing short of a goldmine of introductory learning material. Excellent stuff!

Country of residence: India
on September 2, 2011 at 10:43 am | Reply vishal patwardhan (@vishalpatwardha)

Correct Option is #1

Explanation :
The Process of Parsing the xml and load it into sql table is called xml shredding,for which we should focus on case sensitivity of xml elements with sql columns and need to store it into cache.

Vishal Patwardhan
Indore(India)
on September 2, 2011 at 10:43 am | Reply Geetanjali Agarwal

Correct Answer is 1.

1. Shredding

Anytime you turn XML into another data format (e.g., into a SQL Server table) that process is called shredding XML or in other words Operations which parse and consume XML data are known collectively by the term shredding XML.

Streaming – Converting SQL Server Table Data into XML Format.
Shredding – Converting XML Data into SQL Server Tabular Result Set.

Thanks,
Geetanjali Agarwal
India, Noida
on September 2, 2011 at 10:51 am | Reply tej narayan maurya

option one is correct that is shredding

new delhi(india)
on September 2, 2011 at 10:53 am | Reply Madhusmita Rout

Answer is option 1> Shredding

Madhusmita (India)
on September 2, 2011 at 11:00 am | Reply Kewal Krishan Khanna

Correct answer is option 1.

When we convert the xml data into another format the term is called Shredding.

When we convert the data in sql server table into xml format that term is called Streaming.

India
on September 2, 2011 at 11:00 am | Reply Kaushik Modi

Hi

Correct ans is 1st Shredding,

Because to transfer xml data into another data format is known as shredding.

I am from india.
on September 2, 2011 at 11:32 am | Reply GurjitSingh

Correct answer is option 1:
1.Shredding

City: Baroda
Country: India

Thanks
GurjitSingh
on September 2, 2011 at 11:41 am | Reply SIJIN KUMAR V P

Correct answer is Option #1, Shredding

The process that turn XML into another data format (e.g., into a SQL Server table) is called shredding XML.
on September 2, 2011 at 11:43 am | Reply SIJIN KUMAR V P

Correct answer is Option #1, Shredding

The process that turn XML into another data format (e.g., into a SQL Server table) is called shredding XML.

SIJIN KUMAR V P
INDIA
on September 2, 2011 at 11:53 am | Reply santosh.s

Hi

The correct answer is option 1) Shredding

XML shredding is the process of consuming XML data and parsing out the content to scalar elements.

WE can use nodes() to achieve this.
And if the data is huge, to improve the performance – having typed the XML using a schema and created an XML Index will give a performance improvement.

Thanks & Regards
Santosh.S
Bangalore,India
on September 2, 2011 at 11:58 am | Reply Rajneesh Verma

Hi,
As description given “Anytime you turn XML into another data format (e.g., into a SQL Server table) that process is called shredding XML.”

#Option 1 is the correct Answer “Shredding”.

Thanks…
Rajneesh Verma
(INDIA)
on September 2, 2011 at 12:07 pm | Reply sudeepta

The correct answer is:
1. Shredding

Sudeepta,
India.
on September 2, 2011 at 12:12 pm | Reply Deepali Bhende

The correct answer is #1 – Shredding.

Deepali Bhende
INDIA
on September 2, 2011 at 12:20 pm | Reply SQL SERVER – Win a Book a Day – Contest Rules – Day 0 of 35 Journey to SQLAuthority

[...] Q 33) SQL SERVER – Tips from the SQL Joes 2 Pros Development Series – Shredding XML – Day 33 of 35 [...]
on September 2, 2011 at 12:23 pm | Reply Richa Aggarwal

Shredding is the right answer.

Richa,
USA
on September 2, 2011 at 1:13 pm | Reply Sale A. A.

The correct option is 1.Shredding

(Sale, Nigeria )
on September 2, 2011 at 1:37 pm | Reply madhivanan

Option 1

1.Shredding

Country:India
on September 2, 2011 at 2:02 pm | Reply P.Anish Shenoy

Hi Sir,

Option 1 Shredding is the right answer

As said in the blog above “Operations which parse and consume XML data are known collectively by the term shredding XML”
shredding refers to extracting relevant data out of hierarchical XML and putting it into flat relational storage.

Regards,
P.Anish Shenoy,
INDIA, Bangalore, Karnataka
on September 2, 2011 at 2:04 pm | Reply Hiren Bavishi

The correct answer is:
1. Shredding

Hiren Bavishi
India.
on September 2, 2011 at 2:11 pm | Reply miteshmca

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)
on September 2, 2011 at 2:15 pm | Reply Raushan Kumar Jha

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)
on September 2, 2011 at 2:18 pm | Reply Alpesh Gorasia

Correct Answer # 1

1. Shredding

Alpesh Gorasia
(India)
on September 2, 2011 at 2:56 pm | Reply Tom Brown

Nice article: Correct answer is 1 – shredding.
Is your competition open to UK entires?
on September 2, 2011 at 3:19 pm | Reply Diljeet Kumari

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
on September 2, 2011 at 3:32 pm | Reply Hema Chaudhry

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
on September 2, 2011 at 3:43 pm | Reply Sushant Kumar Pradhan

The correct answer is option – 1 . Shredding

Sushant Kumar Pradhan
Technical Architect, Wipro Technologies
India
on September 2, 2011 at 3:45 pm | Reply Gourav Rampal

The correct answer is:
1. Shredding

Gourav Rampal
Chandigarh,India
on September 2, 2011 at 3:48 pm | Reply Vishal Shrivastav

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
on September 2, 2011 at 4:45 pm | Reply Rajesh Mohanrangan

The correct answer is #1 – Shredding

Regards
Rajesh
From india
on September 2, 2011 at 5:14 pm | Reply Robin thomas

Correct answer is option 1 Shredding
Robin Thomas
India
]
on September 2, 2011 at 5:54 pm | Reply Matt Nelson

Option 1, Shredding
Matt Nelson, USA
on September 2, 2011 at 6:59 pm | Reply Deb

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
on September 2, 2011 at 7:17 pm | Reply Uday Bhoopalam

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
on September 2, 2011 at 7:21 pm | Reply Ishan Shah

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
on September 2, 2011 at 7:27 pm | Reply Ramdas

The answer is option #1 Shredding.

Ramdas, NC
USA
on September 2, 2011 at 7:39 pm | Reply Bill Pepping

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
on September 2, 2011 at 8:04 pm | Reply Kevin Scharnhorst

Option 1 –> Shredding

Kevin Scharnhorst
United States
on September 2, 2011 at 8:42 pm | Reply Don

Option 1 is the correct answer.

Country: United States
on September 2, 2011 at 10:54 pm | Reply dilipkumarjena

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
on September 2, 2011 at 11:15 pm | Reply Gordon Kane

1.Shredding

Gordon Kane
Allen TX
USA
on September 3, 2011 at 12:51 am | Reply shekhar bahuguna

answer is option 1:Shredding

Thanks
Shekhar Bahuguna
Delhi India
on September 3, 2011 at 1:33 am | Reply Nikul Desai

Answer is Option 1: Shredding

Thanks Mr Pinal for this wonderful series. I have never worked with XML data in SQL server and thought of it as a very complex topic. But after reading your posts on XML it is pretty clear to me now.

Thanks
Nikul, USA
on September 3, 2011 at 10:01 am | Reply djottings

Shredding
USA
on September 3, 2011 at 12:37 pm | Reply pinaldave

A.) 1.Shredding

Winner from USA: Bill Pepping

Winner from India: Kewal Krishan Khanna

I thank you all for participating here. The permanent record of this update is posted on facebook page.
on September 5, 2011 at 3:18 pm | Reply vaishali

correct answer is “Shredding” which is first option.
Shredding is the process of transforming xml data into another format.
on September 5, 2011 at 8:21 pm | Reply Praveen Madupu

Hi Pinal,

whenever a transaction log is full, what is the primary step do we need to take inorder to continue the further transactions on the database?

please give reply to my question as soon as possible.
on September 7, 2011 at 7:43 am | Reply SQL SERVER – A Guide to Integrating SQL Server with XML, C#, and PowerShell – Book Available for SQL Server Certification Journey to SQLAuthority

[...] Q 33) SQL SERVER – Tips from the SQL Joes 2 Pros Development Series – Shredding XML – Day 33 of 35 [...]
on January 21, 2012 at 7:01 am | Reply SQL SERVER – Methods for Accessing SQL Server XML Datatype – Quiz – Puzzle – 20 of 31 « SQL Server Journey with SQL Authority

[...] Server Interview Questions and Answers ISBN: 1466405643 Page#137-139 SELECT * FROM XML Shredding XML Validate an XML document in TSQL using XSD Simple Example of Reading XML File Using T-SQL Simple [...]
on August 10, 2012 at 7:01 am | Reply SQL SERVER – Understanding XML – Contest Win Joes 2 Pros Combo (USD 198) – Day 5 of 5 « SQL Server Journey with SQL Authority

[...] SQL Joes 2 Pros Development Series – Shredding XML [...]