.NET XPath Queries

With SQL Server and SQLXML we can query data with XPath queries to navigate around XML documents. Together with the query we have to specify a mapping schema to define how the format of the query maps to the database schema. SQLXML first inspects the mapping schema and transforms the XPath expression into a SELECT … FOR XML EXPLICIT statement. It will then send the FOR XML EXPLICIT query to SQL Server to retrieve the data in the XML format defined in the mapping schema.

 There are several advantages to define your DB-to-XML mappings using annotated schemas when we need to retrieve data from the database and convert it to a fixed format. First of all, they relieve us from writing any code to convert data returned from the database into the XML format. We simply point SQLXML to the mapping schema and the conversion happens behind the scenes. Furthermore, all the information about how your XML maps to your database resides in one place outside of the compiled code. This helps to further reduce development time as you can quickly propagate changes to the mapping during development or if you find any bugs after you deployed your application to your production environment. Also, querying object hierarchies with XPath expressions is much simpler and more readable than using SQL statements joining several tables. Don’t you agree that querying the database for an XML structure of Orders with nested OrderItem elements like this

<OrderHeader>

      <OrderID />

      <OrderDate />

      <OrderItem>

            <ProductID>1<ProductID>

            <Quantity>10</Quantity>

      </OrderItem>

</OrderHeader>

with the XPath expression

/OrderHeader/OrderLine[ProductID=1]

is much easier to read than:

SELECT     OrderLine.*, OrderHeader.*

FROM       OrderHeader INNER JOIN

           OrderLine ON OrderHeader.OrderID = OrderLine.OrderID

WHERE      OrderLine.ProductID = 1

Finally, I prefer XPath queries over SQL statements, especially when it comes to writing re-usable components, because the fields returned by the query are defined by the type definition of the type you are retrieving, not by the query itself. So much for retrieving XML from the database, now let’s see how we can modify a SQL Server database when have an XML document and a mapping schema.

WARNING: SQLXML 3.0 does not support the XPath functions and operators: xmlns, descendant-or-self, preceding-sibling, preceding, namespace, following-sibling, followingdescendant, ancestor-or-self, ancestor, ceiling(), mod, concat(), floor(),  count(), contains(), id(), translate(), sum(), substring-before(), substring-after(), substring(), string-length(), starts-with(), round(), normalize-space(), namespace-uri(), name(), local-name(),  position(), last(), lang() as well as node tests with the * wildcard.