SQLXML XPath Queries

One slight change to the last approach to buy us an extra edge on the performance side would be to replace the SqlCommand with the SqlXmlCommand from the SQLXML library. The SqlXmlCommand uses the SQLXML OLE DB provider which performs better than the SQL Server library used by the SqlCommand class when it comes to retrieving XML. Combined with the either the StreamReader or the XmlTextWriter this combination will outperform all other approaches we examine in this section. Switching to the SqlXmlCommand also enables client-side XML formatting, i.e. we can move the load associated with the transformation of the results to XML from the data base server to the application server. We’ll examine client-side formatting more detailed in section 15.3.3.1.

In the next approach we replace SQL queries with XPath expressions and annotated XML schemas. This approach is a great compromise between ease-of-development, speed, flexibility and maintainable code. The code is easy to maintain because all the mapping logic is maintained in the annotated schema outside the compiled code reducing the cost of changes to the mapping logic to a minimum. XPath queries are also significantly simpler to develop than FOR XML EXPLICIT queries. Sounds great already, but there is more. The annotations allow a great deal of flexibility how the SQL Server table structure maps to XML and we are still using the fast SQLXML OLE DB provider under the hood. If you are concerned about the impact of interpreting the schema and transforming the XPath expression to a FOR XML EXPLICIT query, you might feel better knowing that SQLXML caches the result of a schema’s analysis. After the initial hit parsing the schema, XPath queries are similar in performance to the first approach, manually transforming results of a “regular” SQL query from the returned DataReader. The code example below shows that executing an XPath query is just as simple as in the previous example.

public static void ExecuteXPathQuery( string xpathQuery, string

  connectionString, string schemaPath, string rootName,

  ref Stream stream )

{

  SqlXmlCommand cmd = new SqlXmlCommand(connectionString);     

  cmd.CommandText = xpathQuery;

  cmd.CommandType = SqlXmlCommandType.XPath;

  cmd.RootTag = rootName;

  cmd.SchemaPath = schemaPath;

  cmd.ExecuteToStream( stream );

}

With only a few generic lines we can execute the XPath expression and return the results in XML. The example below makes use of the SqlXmlCommand class’ ExecuteToStream() method, which can speed up returning the results another notch. The XML transformation of the results happens now straight into the stream that is returned to the application, whereas the first two techniques required copying the query results to a separate output stream (wrapped by an XmlTextWriter). Just like the previous approach the SqlXmlCommand allows client-side formatting, which reduces the load we put on the database server.