Querying an XML list in sql server using Xquery?

XQuery 1.0 in turn is based on XPath 2.0 and adds ordering, reshaping, construction, and validation capabilities to the navigational and filtering aspects of XPath 2.0. XQuery is a declarative, typed, functional language designed from scratch by the XML Query Working Group specifically for the purpose of querying data stored in XML format. XQuery shares the same data model and the same XML Schema-based type system with other members of the XML standards family such as XPath 2.0 and XSLT 2.0.

XQuery is designed to work with XML documents that are untyped (no schema associated with the data), typed with XML schemas, or a combination of both. As previously mentioned, XQuery 1.0 is basically a superset of XPath 2.0. Adds an order by clause to the FLWOR clause to sort in non-document order.

Adds a let clause to the FLWOR clause to name results of expressions for further use (not supported in SQL Server 2005). Provides a way to specify static context items in the query prolog (such as namespace prefix bindings). Provides the ability to construct new nodes.

Provides the ability to define user-defined functions (not supported in SQL Server 2005). Provides the ability to create modules/libraries (not supported in SQL Server 2005). It is easy to learn if knowledge of SQL and XPath is present.

When queries are written in XQuery, they require less code as compared to queries written in XSLT. XQuery can be used as a strongly typed language when the XML data is typed, which can improve the performance of the query by avoiding implicit type casts and provide type assurances that can be used when performing query optimization. XQuery can be used as a weakly typed language for untyped data to provide high usability.

SQL Server 2005 implements static type inferencing with support for both strong and weak type relationships. Because XQuery requires less code to perform a query than does XSLT, maintenance costs are lower. XQuery is going to be a W3C recommendation and will be supported by major database vendors.

The XQuery specification is currently under development and may change in the future. SQL Server 2005 implements a stable part of the W3C working draft. XQuery for query/analysis: XQuery is excellent for querying huge chunks of data and provides the capability to filter, sort, order, and repurpose the required information.

Typical applications include querying XML documents that represent semi-structured information, name-value pair property bags, analysis of application logs, transaction logs and audit logs to identify potential application errors and security issues, and so on. XQuery for application integration: As organizations move away from proprietary application integration approaches and start adopting standards based application integration approaches, the need for transforming data from internal application-specific formats to standard exchange formats is gaining more focus. Because of its ability to construct and transform XML data, XQuery caters to this need.

One typical use of XQuery in the application-integration domain is translating the vocabulary used by one application that uses native XML database/relational data source into a language used by another application that uses XML/relational data format. Performing XML processing at the server using XQuery has many advantages compared to client-side XML processing. Reduced traffic on the network: When XML data is processed on the server, only the results are forwarded to the client.

This results in reduced traffic on the network. More security: Only the data that is required is sent to the client thereby avoiding the risk of exposing the entire data to the network as is the case when using client-side XML processing. Better maintainability: Processing XML on the server results in browser independent code on the client, which leads to better maintainability on the client side.

Improved performance: Queries written using XQuery on the server are subjected to optimization by the SQL query engine. This results in improved performance when compared to retrieving the entire data and filtering the data at the client. Furthermore, indexes can be created on the XML data type column to achieve enhanced performance.

To validate the XML instances during insertion operations. To validate the XML instances during modification operations. Type information contained in the XML schema is used during static type checking for early error detection and for query performance improvements by generating better query plans and avoiding many runtime inspections.

Type information present in the XML schema is used by the SQL Server to optimize storage. An XQuery expression in SQL Server 2005 consists of two sections—a prolog and a body. A prolog can in turn contain a namespace declaration subsection.

Namespace declarations are used to define a mapping between prefix and namespace URI thereby enabling you to use the prefix instead of the namespace URI in the query body. You can also refer to element names without the prefix by binding a default namespace for element names, using the declare default namespace declaration. The body of an XQuery expression contains query expressions that define the result of the query.

It can for example be the signature FLWOR expression (see The FLWOR Statement in this paper), an XPath 2.0 expression (see XPath 2.0 Expressions in this paper), or another XQuery expression such as a construction or arithmetic expression. The following query selects all Employment nodes for a candidate whose JobCandidateID is 3. SQL Server also supports the SQL-2003 standard extension that allows a user to declare XML namespace bindings in the SQL WITH clause on a per SQL query basis, thus avoiding repetitive declarations in multiple XML data type method invocations.

The following query shows the modified version of the query shown in the previous example. XQuery uses XPath 2.0 expressions to locate nodes in a document and to navigate from one location to another within a single document or across documents. Navigation paths defined using XPath consist of a sequence of steps separated by /.

A single step comprises an axis, a node test, and zero or more step qualifiers. The axis specifies the direction of movement, relative to the context node. Supported axes in SQL Server 2005 are child, descendant, parent, attribute, self and descendant-or-self.

A node test specifies a condition that all the nodes that are selected by a step must satisfy. The node condition can be specified based on node name or node type. Step qualifiers can be defined by using predicates or dereferences.

A predicate is an expression that acts as a filter on a node sequence and is specified within square brackets. A dereference maps the elements and/or attributes nodes in a node sequence to the nodes that they reference.