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:
-
The new XML datatype in SQL 2005, and the new associated methods (query,
exists, value, and nodes)
-
A reusable T-SQL UDF to convert XML to a table variable
-
New SELECT FOR….XML capabilities
-
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.
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
Want to learn more about Microsoft SQL Server 2005?