The XQueryProcessor Class

The XQueryProcessor Class

The XQueryProcessor class provides a compiler and execution engine for the W3C XQuery language. The .NET Framework documentation has a list of the language features, functions, and operators that are supported for the current release.

The Properties and Methods of the XQueryProcessor Class

Before we look at how we can use the XQueryProcessor, this section contains a concise reference to the properties and methods of this class. There are a single constructor, three properties, and four methods, as documented in Figure through 8.3.

Dim xqp As New XQueryProcessor()
xqp.XmlViewSchemaDictionary.Add("Northwind",
 "nwind.xml")
xqp.Compile("map:view('Northwind')//Customers")

Dim xqp As New XQueryProcessor()
xqp.CompileView("for $i in /Customers/Customer
 return $i@City", "northwind.msd")

Dim xqp As New XQueryProcessor()
xqp.CompileView("for $i in /Customers/Customer
 return $i@City", "northwind.msd")
myConnection.Open()
xqp.ExecuteView(myConnection, Console.Out)
myConnection.Close()

The XmlCommand Class

The XmlCommand class represents an XML query that can be executed at runtime, either for XSLT or XQuery, and is used as a property of the XQueryProcessor and XsltProcessor classes. You provide the Xml Command with the data sources and runtime parameters necessary to execute the query. It can then be used to execute the same query multiple times over any data source. The single method is Execute, described in Figure.

Querying an XML Document with the XQueryProcessor Class

The example in Listing 8.1 uses the bookstore XML document introduced in Chapter 6 and queries for all the books using the XQueryProcessor class. First an XmlDataSourceResolver is created to resolve the document() function references in the query, in this case for the books.xml document. Here the XQuery is supplied as a string. A StreamWriter is then created for the output. The query is compiled and then executed over the data sources to produce the result.

Querying an XML Document with an XQueryProcessor

' initialize the XmlDataSourceResolver
Dim datasource As New XmlDataSourceResolver()
datasource.Add("bookstore", "books.xml")

Dim writer As New StreamWriter("output.xml")

' initialize the XQueryProcessor
Dim query As String = "<bookstore>" _
  & "{ for $b in document('bookstore')/bookstore/book" _
  & "where $b/@genre='philosophy' and $b/@publicationdate='1991'" _
  & "return $b/title }" _
  & "</bookstore>"
Dim xq As New XQueryProcessor()

' compile query and execute against the books.xml document
xq.Compile(query)
xq.Execute(datasource, writer)

writer.Close()

The XQuery used in this example, shown in Listing 8.2, finds all the books with a value of philosophy for the genre attribute and a publicationdate attribute value of 1991.

Listing 8.2 The XQuery Expression for Listing 8.1

<bookstore>
{ for $b in document('bookstore')/bookstore/book
where $b/@genre='philosophy' and $b/@publicationdate='1991'
return $b/title }
</bookstore>

In the XQuery, the document function is used to specify the context document against which the query will be executed, in this case, bookstore. The screenshot in Figure shows the results. The query returns the book titles, though in this case there is only a single title that matches.

Querying Multiple XML Documents with the XQueryProcessor Class

The XPathDocument2 class is an XML store designed to work efficiently with XQuery and XSLT. The next example, shown in Listing 8.3, queries over two bookstore documents. This example is identical to the previous one, except that in this case the books2.xml document is loaded into an XPathDocument2 instance and then added to the XmlDataSourceResolver. This kind of situation may arise when you want to edit the document with the XPathEditor before performing XQuery operations over it.

It is also worth noting that the XmlResolver must use a single Xml NameTable for all of its resources. This is generic behavior for all situations that use the XmlResolver class, not just the XmlDataSourceResolver. If the XmlDataSourceResolver is used to reference multiple documents, each document must share the same XmlNameTable (this is why the XPathDocument2 created in Listing 8.3 uses the XmlNameTable from the XmlData SourceResolver). Without a single XmlNameTable shared across documents, comparisons between element and attribute names are not possible without causing severe performance impacts on the query.

Querying across Two XML Documents

' initialize the XmlDataSourceResolver
Dim datasource As New XmlDataSourceResolver()
datasource.Add("bookstore", "books.xml")

Dim doc As New XPathDocument2(datasource.NameTable)
doc.Load("books2.xml")
datasource.Add("bookstore2", doc.CreateXPathNavigator2())

Dim writer As New StreamWriter("output.xml")

' initialize the XQueryProcessor
Dim query As String = "<bookstore>" _
  & "{ for $b in document('bookstore')/bookstore/book" _
  & "where $b/@genre='philosophy' and $b/@publicationdate='1991'" _
  & "return $b/title }" _
  & "{ for $b2 in document('bookstore2')/bookstore/book" _
  & where $b2/@genre='philosophy' and $b2/@publicationdate='2002'" _
  & "return $b2/title }" _
  & "</bookstore>"
Dim xq As New XQueryProcessor()

' compile query and execute against documents
xq.Compile(query)
xq.Execute(datasource, writer)

writer.Close()

The XQuery used in this example, shown in Listing 8.4, finds all the books with a value of philosophy for the genre attribute and a publicationdate attribute value of 1991 from the first XML document (bookstore.xml), plus all books with a value of philosophy for the genre attribute and a publicationdate attribute value of 2002 from the second XML document (bookstore2.xml), and returns their title attribute values.

Listing 8.4 The XQuery Expression for Listing 8.3

<bookstore>
{ for $b in document('bookstore')/bookstore/book
where $b/@genre='philosophy' and $b/@publicationdate='1991'
return $b/title }
{ for $b2 in document('bookstore2')/bookstore/book
where $b2/@genre='philosophy' and $b2/@publicationdate='2002'
return $b2/title }
</bookstore>

The screenshot in Figure shows the results of executing this query.

Using an XQueryProcessor with an XML View

XML views were discussed in the previous chapters. We used them to retrieve data from SQL Server and to push updates back into SQL Server via the XmlAdapter class. The next example, in Listing 8.5, uses the map:view() extension function to query SQL Server via an XML view.

This example is similar to querying over an XML document, except that the data source added to the XmlDataSourceResolver is a SqlConnection class rather than the XPathNavigator2 or XPathDocument2 we used for XML documents. For the XML view, a mapping file referenced with the name nwind is added to the XmlViewSchemaDictionary and then referenced by the map:view() function to execute the XQuery.

Remember that the XmlViewSchemaDictionary is a dictionary of Xml ViewSchema objects, where the Add methods provide the ability to add new XML views that can be used to issue queries and updates to a data source such as SQL Server. The Add method creates new XmlViewSchema instances. You can pass MSD file references to the Add method, whereupon it automatically creates a new XmlViewSchema object, compiles the mapping, and adds this to the dictionary. Also recall that the XmlViewSchema class represents a compiled XML view.

Using the XQueryProcessor Class with an XML View

Dim myConn As New SqlConnection("your-connection-string")
myConn.Open()

' create an XQueryProcessor to compile XQuery statement
Dim xq As New XQueryProcessor()

' add the XML view
xq.XmlViewSchemaDictionary.Add("nwind", New XmlTextReader("msd-file"))

' add the database connection
Dim datasource As New XmlDataSourceResolver()
datasource.Add("Northwind", myConn)

Dim writer As New StreamWriter("output.xml")

xq.Compile("your-query")
xq.Execute(datasource, writer)

writer.Close()
myConn.Close()

The XQuery, shown below, returns all of the Customers rows from the Northwind sample database, through the nwind XML view. This view is loaded into the XmlViewSchemaDictionary instance using the connection to SQL Server named Northwind:

map:view('nwind')/Customers

We saw in Chapter 7, when using the XmlAdapter class, how the name of the <DataSource> in the MSD file is used to reference this connection by name. The same applies to the XQueryProcessor class, for example:

<m:DataSource Name="Northwind" Direction="Source" Type="SQL Server">

The screenshot in Figure shows the results of executing this query.

Using an XQueryProcessor with Multiple XML Views

The combination of XQuery and XML Views provides a powerful technique for aggregating data from a number of different views. The next example uses multiple XML views, again using the map:view()extension function to query SQL Server. The data source added to the XmlData SourceResolver is still a SqlConnection reference named Northwind, which is used by all of the XML views. For each XML view, a mapping file is added to the XmlViewSchemaDictionary and referenced by name in the map:view() function when executing the XQuery.

The Three-Part Mapping Files

In this example, we have taken the nwind XML view from Chapter 7 and split it into two separate XML views: one for customers, called customersView, and one for orders, called ordersView. Listings 8.6 through 8.9 show the XML schemas and MSD mappings for these two XML views. The nwind RSD file remains the same because this represents the relational view—which is unchanged.

The XML Schema for the customersView

<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema">
  <xsd:element name="Customer">
    <xsd:complexType>
      <xsd:attribute name="name" type="xsd:string"/>
      <xsd:attribute name="CustomerID" type="xsd:string"/>
      <xsd:attribute name="Country" type="xsd:string"/>
      <xsd:attribute name="City" type="xsd:string"/>
    </xsd:complexType>
  </xsd:element>
</xsd:schema>

The MSD Mapping File for the customersView

<m:MappingSchema
    xmlns:m="http://schemas.microsoft.com/data/2002/09/28/mapping">
  <m:DataSources>
    <m:DataSource Name="XML" Direction="Target" Type="Xml">
      <m:Schema Location="customers.xsd"/>
    </m:DataSource>
    <m:DataSource Name="Northwind" Direction="Source"
                  Type="SQL Server">
      <m:Schema Location="nwind.rsd"/>
      <m:Variable Name="Customers" Select="Customers"/>
    </m:DataSource>
  </m:DataSources>
  <m:Mappings>
    <m:Map SourceVariable="Customers" TargetSelect="/Customer">
      <m:FieldMap SourceField="CompanyName" TargetField="@name"/>
      <m:FieldMap SourceField="CustomerID"
                  TargetField="@CustomerID"/>
      <m:FieldMap SourceField="Country" TargetField="@Country"/>
      <m:FieldMap SourceField="City" TargetField="@City"/>
    </m:Map>
  </m:Mappings>
</m:MappingSchema>

The XML Schema for the ordersView

<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema">
  <xsd:element name="Order">
    <xsd:complexType>
      <xsd:attribute name="CustomerID" type="xsd:string"/>
      <xsd:attribute name="orderid" type="xsd:string"/>
      <xsd:attribute name="OrderDate" type="xsd:string"/>
      <xsd:attribute name="ShipCity" type="xsd:string"/>
      <xsd:attribute name="ShipDate" type="xsd:string"/>
    </xsd:complexType>
  </xsd:element>
</xsd:schema>

The MSD Mapping File for the ordersView

<m:MappingSchema
    xmlns:m="http://schemas.microsoft.com/data/2002/09/28/mapping">
  <m:DataSources>
    <m:DataSource Name="XML" Direction="Target" Type="Xml">
      <m:Schema Location="orders.xsd"/>
    </m:DataSource>
    <m:DataSource Name="Northwind" Direction="Source"
                  Type="SQL Server">
      <m:Schema Location="nwind.rsd"/>
      <m:Variable Name="Orders" Select="Orders"/>
    </m:DataSource>
  </m:DataSources>
  <m:Mappings>
    <m:Map SourceVariable="Orders" TargetSelect="/Order">
      <m:FieldMap SourceField="orderid" TargetField="@orderid"/>
      <m:FieldMap SourceField="CustomerID"
                  TargetField="@CustomerID"/>
      <m:FieldMap SourceField="OrderDate"
                  TargetField="@OrderDate"/>
      <m:FieldMap SourceField="ShipCity" TargetField="@ShipCity"/>
      <m:FieldMap SourceField="ShippedDate"
                  TargetField="@ShipDate"/>
    </m:Map>
  </m:Mappings>
</m:MappingSchema>

The Example Code

The code for this example is shown in Listing 8.10. Multiple XML views are added to the XmlViewSchemaDictionary, which all use a single data source (database) connection called Northwind. A StreamWriter is created for output; then the XQuery is compiled and executed, with the output being sent to this stream.

Using the XQueryProcessor Class with Multiple XML Views

Dim myConn As New SqlConnection("your-connection-string")
myConn.Open()

' create an XQueryProcessor to compile XQuery statement
Dim xq As New XQueryProcessor()

' add multiple XML views
xq.XmlViewSchemaDictionary.Add("customersView", _
                           New XmlTextReader("customers-msd-file"))
xq.XmlViewSchemaDictionary.Add("ordersView", _
                           New XmlTextReader("orders-msd-file"))

' add the database connection
Dim datasource As New XmlDataSourceResolver()
datasource.Add("Northwind", myConn)

Dim writer As New StreamWriter("output.xml")

xq.Compile("your-query")
xq.Execute(datasource, writer)

writer.Close()
myConn.Close()

The XQuery used in Listing 8.10 is shown in Listing 8.11. It returns all the Customer elements through the customersView XML view and then loads all the Order elements through the ordersView XML view, but only where the CustomerID value of the Order matches the CustomerID value for the Customer in the query predicate.

Finally, a Customer element is constructed with the element keyword. The /@* expression adds all the attributes for this Customer to the new element, and the $orders variable inserts the list of orders for this particular customer as the element content. The element keyword is another syntax for creating elements in XQuery and is identical in behavior to the XML <Customer> syntax.

Listing 8.11 The XQuery Expression for Listing 8.10

<root>
{
for $customer in map:view('customersView')/Customer
let $orders := map:view('ordersView')/Order[@CustomerID
                                 = $customer/@CustomerID]
return element Customer {$customer/@*, $orders}
}
</root>

The screenshot in Figure shows the results of executing this query.

Using an XQueryProcessor with Multiple XML Views and Multiple Database Connections

The previous example showed XQuery execution with multiple XML views over the same connection to the same database. You can also create XML views over different connections, typically to different physical databases that can be on different machines. This shows the ability to aggregate data from a number of different views that are physically separated.

A new XML view, called productsView, is used in the next example. This view is again a subset of the nwind XML view, mapping the Products table from the Northwind sample database. The XML schema and MSD file for the productsView XML view are shown in Listings 8.12 and 8.13.

The XML Schema for the productsView

<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema">
  <xsd:element name="Product">
    <xsd:complexType>
      <xsd:attribute name="UnitQuantity" type="xsd:integer"/>
      <xsd:attribute name="ProductName" type="xsd:string"/>
      <xsd:attribute name="UnitPrice" type="xsd:decimal"/>
      <xsd:attribute name="QuantityPerUnit" type="xsd:string"/>
      <xsd:attribute name="ProductID" type="xsd:integer"/>
    </xsd:complexType>
  </xsd:element>
</xsd:schema>

The MSD Mapping File for the productsView

<m:MappingSchema
     xmlns:m="http://schemas.microsoft.com/data/2002/09/28/mapping">
  <m:DataSources>
    <m:DataSource Name="XML" Direction="Target" Type="Xml">
      <m:Schema Location="products.xsd"/>
    </m:DataSource>
    <m:DataSource Name="Northwind" Direction="Source"
                  Type="SQL Server">
      <m:Schema Location="nwind.rsd"/>
      <m:Variable Name="Products" Select="Products"/>
    </m:DataSource>
  </m:DataSources>
  <m:Mappings>
    <m:Map SourceVariable="Products" TargetSelect="/Product">
      <m:FieldMap SourceField="productname"
                  TargetField="@ProductName"/>
      <m:FieldMap SourceField="UnitPrice" TargetField="@UnitPrice"/>
      <m:FieldMap SourceField="QuantityPerUnit"
                  TargetField="@QuantityPerUnit"/>
      <m:FieldMap SourceField="ProductID" TargetField="@ProductID"/>
    </m:Map>
  </m:Mappings>
</m:MappingSchema>

The code itself (shown in Listing 8.14) is similar to that in the previous example, except that the XML view named productsView is added to the XmlViewSchemaDictionary as well as the two views we used in that example. And two data sources are added to the XmlDataSourceResolver—the Northwind data source shown in the previous example and a new one called productsDb. The query can then be executed, as shown earlier in Listing 8.10.

Using an XQueryProcessor with Multiple XML Views and Multiple Database Connections

...
... create XQueryProcessor, as in Listing 8.10
...

' add multiple XML views
xq.XmlViewSchemaDictionary.Add("customersView", _
                           New XmlTextReader("customers-msd-file"))
xq.XmlViewSchemaDictionary.Add("ordersView", _
                           New XmlTextReader("orders-msd-file"))
xq.XmlViewSchemaDictionary.Add("productsView", _
                           New XmlTextReader("products-msd-file"))

' add multiple database connections
Dim datasource As New XmlDataSourceResolver()
datasource.Add("Northwind", myConn)
datasource.Add("productsDb", productsConn)

' add OrderDetails.xml
datasource.Add("orderDetails", "orderdetails.xml")

...
... execute query, as in Listing 8.10
...

The XQuery used in this example, shown in Listing 8.15, returns all the Product elements through the productsView XML view. Then it loads all of the OrderDetails elements whose ProductID values match the ProductID value of the current Product. For each of these OrderDetails elements, it selects the matching Order, and from each Order it matches the Customer. This list of customers is returned in the $customers variable.

The output of the query is constructed within the outermost return section, consisting of a Product element for each Product found. We use the element keyword and add all the attributes of the Product (using the /@* expression). The content of each Product element is the list of customers for this particular product as referenced by the $customers variable. In effect, we have navigated the database relationships to create a list of products and the customers who purchased them.

Listing 8.15 The XQuery Expression for Listing 8.14

<root>
{
for $product in map:view('productsView')/Product
 return
  element Product
   {
    $product/@*,
    for $orderdetails in
        document('orderDetails')/details/orderdetail[@ProductID
                                        =  $product/@ProductID]
    for $orders in map:view('ordersView')/Order[@orderid
                               = $orderdetails/@OrderID]
    let $customers := map:view('customersView')/Customer[@CustomerID
                                              = $orders/@CustomerID]
    return $customers
   }
}
</root>

The screenshot in Figure shows the results of executing this query.

Using an XQueryProcessor with Multiple XML Views and XML Documents

You may have noticed that the previous query also used the document function to load the order details, rather than using an XML view. The order details were in fact stored in an XML document. In effect, a query join was performed between the XML document and the XML view to retrieve all the orders for a particular order detail.

This further shows the flexibility of the data aggregation process in that joins and predicates can be made between XML documents and XML views. The relevant code from the previous example is repeated in Listing 8.16, showing an XML file named orderdetails.xml being added to the XmlDataSourceResolver in addition to the XML views.

Adding XML Disk Files and XML Views to an XmlDataSourceResolver

Dim datasource As New XmlDataSourceResolver()
datasource.Add("Northwind", myConn)
datasource.Add("productsDb", productsConn)

' Add orderdetails.xml
datasource.Add("orderDetails", "orderdetails.xml")