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.

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

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.

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

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.

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

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)

,
Previous Post
SQLAuthority News – Programming & Development For Microsoft SQL Server 2008
Next Post
SQL SERVER – Tips from the SQL Joes 2 Pros Development Series – Preparing XML in Memory – Day 34 of 35

Related Posts

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)

    Reply
  • Raushan Kumar Jha
    September 2, 2011 2:15 pm

    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)

    Reply
  • Correct Answer # 1

    1. Shredding

    Alpesh Gorasia
    (India)

    Reply
  • Nice article: Correct answer is 1 – shredding.
    Is your competition open to UK entires?

    Reply
  • 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

    Reply
  • 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

    Reply
  • Sushant Kumar Pradhan
    September 2, 2011 3:43 pm

    The correct answer is option – 1 . Shredding

    Sushant Kumar Pradhan
    Technical Architect, Wipro Technologies
    India

    Reply
  • The correct answer is:
    1. Shredding

    Gourav Rampal
    Chandigarh,India

    Reply
  • Vishal Shrivastav
    September 2, 2011 3:48 pm

    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

    Reply
  • Rajesh Mohanrangan
    September 2, 2011 4:45 pm

    The correct answer is #1 – Shredding

    Regards
    Rajesh
    From india

    Reply
  • Correct answer is option 1 Shredding
    Robin Thomas
    India
    ]

    Reply
  • Option 1, Shredding
    Matt Nelson, USA

    Reply
  • 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

    Reply
  • 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

    Reply
  • 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

    Reply
  • The answer is option #1 Shredding.

    Ramdas, NC
    USA

    Reply
  • 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

    Reply
  • Kevin Scharnhorst
    September 2, 2011 8:04 pm

    Option 1 –> Shredding

    Kevin Scharnhorst
    United States

    Reply
  • Option 1 is the correct answer.

    Country: United States

    Reply
  • 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

    Reply

Leave a Reply

Menu