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

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

About these ads

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

  1. 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.

    Like

  2. 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

    Like

  3. 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

    Like

  4. 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

    Like

  5. 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

    Like

  6. 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

    Like

  7. 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

    Like

  8. 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.

    Like

  9. 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

    Like

  10. 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

    Like

  11. 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)

    Like

  12. Pingback: SQL SERVER – Win a Book a Day – Contest Rules – Day 0 of 35 Journey to SQLAuthority

  13. 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

    Like

  14. 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)

    Like

  15. 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

    Like

  16. 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

    Like

  17. 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

    Like

  18. 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

    Like

  19. 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

    Like

  20. 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

    Like

  21. 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

    Like

  22. 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.

    Like

  23. Pingback: SQL SERVER – A Guide to Integrating SQL Server with XML, C#, and PowerShell – Book Available for SQL Server Certification Journey to SQLAuthority

  24. Pingback: SQL SERVER – Methods for Accessing SQL Server XML Datatype – Quiz – Puzzle – 20 of 31 « SQL Server Journey with SQL Authority

  25. Pingback: SQL SERVER – Understanding XML – Contest Win Joes 2 Pros Combo (USD 198) – Day 5 of 5 « SQL Server Journey with SQL Authority

  26. Pingback: SQL SERVER – Weekly Series – Memory Lane – #045 | Journey to SQL Authority with Pinal Dave

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s