Export (0) Print
Expand All

Using XPath Queries

SQL Server 2000

The Microsoft® SQL Server™ 2000 support for annotated XDR schemas allows you to create XML views of the relational data stored in the database. You can use a subset of the XPath language to query the XML views created by an annotated XDR schema.

The XPath query can be specified as part of a URL or within a template. The mapping schema determines the structure of this resulting fragment, and the values are retrieved from the database. This process is conceptually similar to creating views using the CREATE VIEW statement and writing SQL queries against them.

Note  To understand XPath queries, you must be familiar with the concepts of templates (for more information, see Using XML Templates), HTTP access to SQL Server (for more information, see Accessing SQL Server Using HTTP), mapping schema (for more information, see Creating XML Views Using Annotated XDR Schemas), and the XPath standard defined by the World Wide Web Consortium (W3C).

An XML document consists of nodes such as an element node, attribute node, text node, and so on. For example, consider this XML document:

<root>
  <Customer cid= "C1" name="Janine" city="Issaquah">
      <Order oid="O1" date="1/20/1996" amount="3.5" />
      <Order oid="O2" date="4/30/1997" amount="13.4">Customer was
          very satisfied</Order>
   </Customer>
   <Customer cid="C2" name="Ursula" city="Oelde" >
      <Order oid="O3" date="7/14/1999" amount="100" note="Wrap it blue white red">
          <Urgency>Important</Urgency>
      </Order>
      <Order oid="O4" date="1/20/1996" amount="10000"/>
   </Customer>
</root>

In this document, Customer is an element node, cid is an attribute node, and Important is a text node.

XPath (XML Path Language) is a graph navigation language. XPath is used to select a set of nodes from an XML document. Each XPath operator selects a node-set based on a node-set selected by a previous XPath operator. For example, given a set of <Customer> nodes, XPath can select all <Order> nodes with the date attribute value 7/14/1999. The resulting node-set contains all the orders with order date 7/14/1999.

Note  XPath language is defined by the W3C as a standard navigation language. The XPath language specification, XML Path Language (XPath) version 1.0 W3C Proposed Recommendation 8 October 1999, can be found at the W3C Web site at http://www.w3.org/TR/1999/PR-xpath-19991008.html. A subset of this specification is implemented in SQL Server 2000. For more information, see XPath Guidelines and Limitations.

Supported Functionality

The table shows the features of the XPath language that are implemented in SQL Server 2000.

Feature Item Link to sample queries
Axes attribute, child, parent, and self axes Specifying Axes in XPath Queries
Boolean-valued predicates including successive and nested predicates   Specifying Arithmetic Operators in XPath Queries
All relational operators =, !=, <, <=, >, >= Specifying Relational Operators in XPath Queries
Arithmetic operators +, -, *, div Specifying Arithmatic Operators in XPath
Explicit conversion functions number(), string(), Boolean() Specifying Explicit Conversion Functions in XPath Queries
Boolean operators AND, OR Specifying Boolean Operators in XPath Queries
Boolean functions true(), false(), not() Specifying Boolean Functions in XPath Queries
XPath variables   Specifying XPath Variables in XPath Queries

Unsupported Functionality

The table shows the features of the XPath language that are not implemented in SQL Server 2000.

Feature Item
Axes ancestor, ancestor-or-self, descendant, descendant-or-self (//), following, following-sibling, namespace, preceding, preceding-sibling
Numeric-valued predicates  
Arithmetic operators mod
Node functions ancestor, ancestor-or-self, descendant, descendant-or-self (//), following, following-sibling, namespace, preceding, preceding-sibling
String functions string(), concat(), starts-with(), contains(), substring-before(), substring-after(), substring(), string-length(), normalize(), translate()
Boolean functions lang()
Numeric functions sum(), floor(), ceiling(), round()
Union operator |

Specifying an XPath Query

XPath queries can be specified directly in the URL or in a template that is specified in the URL. Parameters can be passed to the XPath queries specified directly in the URL or in the template using XPath variables.

XPath Queries in a URL

XPath queries can be directly specified in the URL, for example:

http://IISServer/VirtualRoot/SchemaVirtualName/SchemaFile/XPathQuery[?root=ROOT]

The root parameter is specified to provide a single top-level element. Any value can be specified for this parameter. If the query returns only one element (or if you want to receive a collection of top-level nodes), you do not have to specify this parameter.

The SchemaVirtualName in the URL is a virtual name of schema type created using the IIS Virtual Directory Management for SQL Server utility. For more information, see IIS Virtual Directory Management for SQL Server.

When you specify XPath queries in the URL, note the following URL-specific behavior:

  • XPath may contain characters such as # or + that have special meanings in the URLs. Escape these characters using the URL percent encoding, or specify the XPath in a template. For example, the URL http://IISServer/VirtualRoot/VirtualName/SchemaFile/Customers[@CustomerID="#"] is truncated at the # symbol, resulting in an invalid XPath.

  • XPath expressions such as .. or // that resemble special file paths are interpreted by some browsers and modified before passing the URL to the server. Consequently, XPaths containing these expressions may not work as expected from the URL. For example:
    • The URL http://IISServer/VirtualRoot/VirtualName/SchemaFile/Customers/.. may be transformed by the browser to http://IIServer/VirtualRoot/VirtualName/SchemaFile/, which is invalid XPath.

    • The URL http://IISServer/VirtualRoot/VirtualName/SchemaFile//Customers may be transformed by the browser to http://IISServer/VirtualRoot/VirtualName/SchemaFile/Customers, which is different XPath.
XPath Queries in a Template

You can write the XPath queries in a template and specify the template in the URL. For example, this is a template with an XPath query:

<ROOT xmlns:sql="urn:schemas-microsoft-com:xml-sql">
  <sql:xpath-query mapping-schema="FilePath/AnnotatedSchemaFile.xml">
    Specify the XPath query
  </sql:xpath-query>
</ROOT>

This template file is stored in the directory specified at the time a virtual name of type template is created. For more information about creating virtual names, see Using IIS Virtual Directory Management for SQL Server Utility.

This URL executes the template:

http://IISServer/VirtualRoot/VirtualName/TemplateFile.xml

The VirtualName specified in the URL is of template type.

Note  There is no namespace support for XPath queries specified directly in the URL. If you want to use a namespace in an XPath query, template should be used. For more information about templates, see Executing Template Files Using a URL.

When you specify XPath queries in a template, note the following behavior:

  • XPath may contain characters such as < or & that have special meanings in XML (and template is an XML document). You must escape these characters using XML &-encoding, or specify the XPath in the URL.

See Also

Retrieving XML Documents Using FOR XML

Accessing SQL Server Using HTTP

IIS Virtual Directory Management for SQL Server

XML Error Messages

Show:
© 2014 Microsoft