good4solution

SQL SERVER – XML Data Type- SQL Queries 2012 Joes 2 Pros Volume 5 – XML Querying Techniques for SQL Server 2012

March 29, 2013 by pinaldave

This chapter is abstract from the Beginning SQL 2012 – Joes 2 Pros Volume 5.

Why buy this Book: SQL is full of relationship data with one to many relationships and so is XML. The Marriage between XML and SQL turns out to be far easier than people expected. People often tell me at the end of reading these 5 books they were surprised that this is their favorite.

What will I learn after reading this book: XML Data Type, Shredding XML, Parsing XML, XQuery Extensions, XPATH, and Binding XML to SQL tabular results, XML Namespaces, and XML Indexes.

All supporting files are available with a free download from thewww.Joes2Pros.com web site. This example is from the SQL 2012 series Volume 5 in the file SQLQueries2012Vo5Chapter5.0Setup.sql. If you need help setting up then look in the “Free Videos” section on Joes2Pros under “Getting Started” called “How to install your labs”

XML Data Type

Integers hold numbers with no decimal points, VARCHARs hold strings of varying length, and the Geography data type holds a position on the earth. Introduced in SQL Server 2005, the XML data type holds and understands valid XML strings.

If an XML document is essentially one long character string, then why should there be a separate XML data type? Like the Geography data type, XML has some built-in functions and methods to help with searching and querying the data inside. For example, the XML data type can detect the difference between valid XML strings (either a valid XML fragment or a well-formed XML document) versus strings which aren’t valid XML.

XML as a Field

You can declare a field as an XML data type at the time you create a table in SQL Server, or you can add an XML field later. This would potentially give each record in your table its own well-formed XML data.

How might this capability be a benefit? Suppose you need to store credit history data for each customer. Some customers have one credit reference, some have many, and a few have none at all. These one-to-many relationships could be defined with a new table called dbo.CreditHistory, since a single field does not implement multiple relationships very well. However, XML would allow for this through a series of related tags and without the need to create a separate table.

Our first example will add an XML field to an existing table. The CurrentProducts table (JProCo.dbo.CurrentProducts) has 7 fields and 485 records.

This ALTER TABLE statement code adds a nullable XML field named CategoryCodes to the CurrentProducts table.

ALTER TABLE CurrentProducts ADD CategoryCodes XML NULL GO

Messages

Command(s) completed successfully.

0 rows

If we re-run our SELECT statement, we can see the new field showing in the table. We want to add some data to the new column. Run this code to populate the CategoryCodes field for ProductID 1 with a well-formed XML.

UPDATE CurrentProducts SET CategoryCodes = '<Root> <Category ID = "1"/> <Category ID = "4"/> </Root>' WHERE ProductID = 1

Messages

(1 row(s) affected)

0 rows

By again running the SELECT statement, we see the newly populated record in the CategoryCodes field. An XML hyperlink shows for Product 1. If we click the XML hyperlink we see the table entity contains this XML result.

SELECT * FROM CurrentProducts

Recall the situation we described earlier where a credit history could be stored as one field within a customer’s record. Our results here in show how this data could appear and how adding an XML field to an existing table would save us from having to create a separate table to contain each customer’s credit history data.

Book On Amazon | Book On Flipkart

Kit on Amazon | Kit on Flipkart

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