Take Five with SQL Server 2005

New XML Capabilities in SQL Server 2005
Kevin S. Goff, Microsoft MVP 2007 

This is the first installment in a monthly, ten-part series on new developer features in SQL Server 2005. The objective of this series is to provide software developers with quick information and meaningful examples that demonstrate new capabilities in SQL 2005. Many developers can testify that being able to effectively utilize the tools in SQL Server can increase the value they bring to a software project. This series will cover the following:

• New XML capabilities (subject of this article)
• A three-part series on new T-SQL 2005 language features
• SQL Server 2005 Service Broker
• SQL Server 2005 Isolation Levels
• A two-part series on writing procedures and functions in the .NET CLR, and in-process data access
• SQL Server 2005 Reporting Services
• ADO.NET

The title of this series ("Take Five with SQL 2005") reflects the goal of providing useful programming examples in a short period of time. Despite a number of great SQL 2005 books, some developers don’t always have time to study the material in a book. So these articles will focus on code samples that developers can adapt to their applications.

So let’s begin! Microsoft beefed up support of XML in SQL 2005. Here are the items we’ll cover:

1. The new XML datatype in SQL 2005, and the new associated methods  (query, exists, value, and nodes)
2. A reusable T-SQL UDF to convert XML to a table variable
3. New SELECT FOR….XML capabilities
4. Performing partial text searches inside an XML DataColumn

---

 1- XML Datatype and associated methods:

Prior to SQL Server 2005, if developers wanted to convert XML data to relational data, they had to use combinations of the stored procedure sp_xml_preparedocument and the OPENXML function. While still valid, this methodology introduces some overhead. SQL 2005 provides native support for the XML data type, and new methods to directly parse and read the data. 

Listing A provides a demonstration.  The code creates a basic XML string of names and cities, and uses the nodes method to create a row for each instance of the Customer node. We must provide a table/column name combination to use the results, so we’ll call it tab(col).  In the SELECT statement, the code will use the value method to strip out pieces of the tab(col) structure.  Finally, the query uses the exist method, which evaluates a comparison condition and returns either a 0 or 1. Note that the syntax for exist doesn’t allow us to concatenate a search variable – but we can use the sql:variable  syntax to embed any variable or parameter.

 Listing A
(using nodes to convert XML to relational data)

-- using the XML Nodes methd to turn XML data

-- into Relational Data

DECLARE @XMLString XML

SET @XMLString ='

<Customers>

  <Customer>

    <FirstName>Kevin</FirstName>

    <LastName>Goff</LastName>

    <City>Camp Hill</City>

  </Customer>

  <Customer>

   <FirstName>Steve</FirstName>

    <LastName>Goff</LastName>

    <City> Philadelphia </City>

  </Customer>

</Customers>'

-- use the basic query method

SELECT @XMLString.query('/Customers/Customer')

-- Now query using the nodes method

-- Read the columns using the value method, and set a condition using exist

-- Set a search condition

DECLARE @cLookup VARCHAR(50)

SET @cLookup ='Camp Hill'

SELECT

  tab.col.value('FirstName[1]','VARCHAR(20)')AS FirstName,

  tab.col.value('LastName[1]','VARCHAR(20)')AS LastName,

  tab.col.value('City[1]','VARCHAR(20)')AS City

FROM @XMLString.nodes('//Customer') tab(col)

   WHERE  tab.col.exist('City/text()[.= sql:variable("@cLookup")] ')= 1 

2 - A reusable T-SQL UDF to convert XML to a table variable

Many database applications execute queries based on a variable number of selections. For example, a product report might prompt the user with a menu of dozens or even hundreds of products. The user selects any number of products, and the database must query against the information for those products.

Over the years, developers have come up with different ways to write a stored procedure that accepts a variable number of selections.  Some developers will pass a comma-delimited string of integer PK values representing the selections, write a UDF in T-SQL to convert the string to a table variable, and then query against the table variable.  Other developers will pass an XML string of selections, and then use sp_xml_preparedocument and OPENXML to convert the XML selections to a table variable.  The problem with the latter method is that SQL Server doesn’t allow the use of sp_xml_preparedocument inside a UDF, making it difficult to write a generic, reusable method.

Once again, the new XML features in SQL 2005 make this task easier, faster, and less resource intensive.  If you create an XML string [or an .NET Dataset/object that can serialize as XML] with a column called PKValue that stores user-selections, you can write a UDF like the one in Listing B, to convert the XML string to a table variable, using the node method previously covered in this article.  Listing B contains both a UDF as well as a sample to utilize it.

Listing B – UDF XML2TableVar
Converts an XML string with a node of PKValue to a table variable

CREATEFUNCTION [dbo].[XML2TableVar]

(  @XMLString XML)

RETURNS

@tPKList TABLE  ( PKValue int)

AS

BEGIN

            INSERTINTO @tPKList

              SELECT Tbl.Col.value('.','int')

                  FROM   @XMLString.nodes('//PKValue') Tbl( Col )

   RETURN

END

-- Example Usage

-- Create an XML string to use in a subsequent join against a customer table

DECLARE @XMLString XML

SET @XMLString ='<Customers>

                                      <Customer>

                                                <PKValue>2</PKValue>

                                               <PKValue>5</PKValue>

</Customer>

                             </Customers>'

-- Show the contents

SELECT  *FROM [dbo].[XML2TableVar] (@XMLString) CustList

-- Perform a JOIN using the UDF

SELECT Customers.*FROM Customers

                        JOIN [dbo].[XML2TableVar] (@XMLString) CustList

                                    ON Customers.CustomerPK = CustList.PKValue

3 - New SELECT FOR….XML capabilities

Listing C shows examples of querying to XML format, including the ability to specify an attribute-centric or element-centric format.

Listing C
(SELECT…FOR XML capabilities)

-- attribute-centric schema

SELECT*FROM orders   WHERE OrderID = 10447

FORXMLAUTO,XMLSCHEMA

-- element-centric schema

SELECT*FROM orders   WHERE OrderID = 10447

FORXMLAUTO, ELEMENTS ,XMLSCHEMA

4 - Performing partial text searches inside an XML DataColumn

Last, but certainly not least – some database store information in an XML format. For example, some will store address information inside an XML column.  It can become a bit of a challenge to query that data, especially if end users want the ability to perform partial text searches.

Once again, we can use the exist method and clauses within the exist method to perform these types of queries. Listing D creates a simple address XML column, populates some sample data, and then performs a partial text query as follows:

WHERE Address.exist('/Address/AddressRecord [contains(@City,"burg")]')= 1

Listing D
(Performing partial text searches)

DECLARE @tTestAddress TABLE(Address XML)

INSERTINTO  @tTestAddress VALUES('<Address >

   <AddressRecord

       CustomerID = "1"

       Street=" 1945 14th Ave " City=" Atlantic City " State="NJ" Zip="91912" />

   </Address>')

insertinto @tTestAddress values('<Address >

   <AddressRecord

       CustomerID = "2"

       Street=" 27 Main Street " City=" Harrisburg " State="PA" Zip="44555"/>

   </Address>')

SELECT*FROM @tTestAddress WHERE

 Address.exist('/Address/AddressRecord [contains(@City,"burg")]')= 1

 Recommended Reading :

For every piece of information that an article covers, there are always multiple items that aren’t covered.  That’s a testament to how much XML functionality exists in SQL 2005.  There are new capabilities for OPENROWSET, a modify method in the XML data type to manipulate XML data, and so much more.  The objective of this article was to provide a starting point with some examples of the new XML.  Here are some additional resources that provide additional information.

• Shawn Wildermuth, Making Sense of the XML DataType in SQL Server 2005,  May/June 2006 issue of CoDe Magazine.
    
• Bob Beauchemin and Dan Sullivan, A Developer's Guide to SQL Server 2005 (Microsoft .NET Development Series) – there are multiple chapters on XML  

Remember…GOOGLE is your friend. Sometimes just taking thirty minutes to Google some XML keywords will lead to more valuable information than you ever thought possible.

About the author: 

Kevin S. Goff has been a Microsoft C# .NET MVP for the last three years, from 2005 through 2007.  He is a regular columnist for CoDe Magazine, for the Baker’s Dozen Productivity Tips.  He is also the author of Pro VS 2005 Reporting using SQL Server and Crystal Reports, published by Apress.  He is the principal consultant for Common Ground Solutions, and specializes in solutions in .NET, SQL Server, and Crystal Reports.  His main site is www.commongroundsolutions.net, and his blog is www.TheBakersDozen.net. 

Email this Author!

Related Links

• www.commongroundsolutions.net
• www.TheBakersDozen.net
• Kevin's Code Magazine Articles

Want to learn more about Microsoft SQL Server 2005?  

• View SetFocus' SQL Server Training options.

Add the above content to:

Google   Y! MyWeb   Furl   Simpy   Spurl   Blink  Del.icio.us   Digg   Technorati