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 ='
'
-- 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(
RETURN
END
-- Example Usage
-- Create an XML string to use in a subsequent join against a customer table
DECLARE @XMLString XML
SET @XMLString ='
'
-- 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('
CustomerID = "1"
Street="
')
insertinto @tTestAddress values('
CustomerID = "2"
Street="
')
SELECT*FROM @tTestAddress WHERE
Address.exist('/Address/AddressRecord [contains(@City,"burg")]')= 1
Recommended
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.
No comments:
Post a Comment