Focus on Microsoft Technologies - Tutorials, Articles, Code Samples.

Sunday, March 29, 2009

XML Indexes in SQL Server 2005

Although not everyone would agree, one of the main reasons for the success of the relational database has been the inclusion of the SQL language. SQL is a set-based declarative language. As opposed to COBOL (or most .NET-based languages for that matter), when you use SQL, you tell the database what data you are looking for, rather than how to obtain that data. The SQL query processor determines the best plan to get the data you want and then retrieves the data for you. As query-processing engines mature, your SQL code will run faster and better (less I/O and CPU) without the developer making changes to the code. What development manager wouldn't be pleased to hear that programs will run faster and more efficiently with no changes to source code because the query engine gets better over time?

XQuery and the XML Data Type

You use XQuery in SQL Server 2005 through a series of built-in SQL methods on the XML data type. The XML data type is a new native type that can be used as a column in a table, procedure parameters, or as T-SQL variables. The built-in methods may be used with any instance of the XML data type. Table 1 contains a list of the five methods, their signatures, and what they do.

Table 1. XML Data Type Functions

Name Signature Usage
exist bit = X.exist(string xquery) Checks for existence of nodes, returns 1 if any output returned from query, otherwise 0
value scalar = X.value(

string xquery, string SQL type)

Returns a SQL scalar value from a query cast to specified SQL data type
query XML = X.query(string xquery) Returns an XML data type instance from query
nodes X.nodes(string xquery) Table-value function used for XML to relational decomposition. Returns one row for each node that matches the query.
modify X.modify(string xml-dml) A mutator method that changes the XML value in place

Note that each of these methods require XQuery (or XML DML in the case of modify) as a (n)varchar SQL input parameter. Each of these methods is used as a part of an "ordinary" SQL statement as in the following examples. more

Post a Comment