Using a Mapping Schema to Retrieve Data

Schemas can be used to retrieve data over ADO or HTTP connections. The client application merely needs to specify the schema to be used, the name of the root element used to contain the resulting XML fragment, and an XPath expression determining which rows should actually be returned. XPath expressions are used with schemas to specify the following:

  • The location in the XML hierarchy to begin retrieving data
  • Criteria to determine the rows that should be returned

Using an XPath Expression with a Schema

The most straightforward XPath is simply the name of the top-level element defined in the schema. Using this expression returns an XML fragment containing all the mapped elements and attributes in the schema. For example, you could use the following XPath expression to retrieve all the data mapped in the catalog schema described earlier:

 Category 

The XML fragment returned by this XPath expression is shown here. Later in this chapter, I’ll explain how to use XPath expressions to retrieve XML data from a database.

 <Category Category CategoryName="Beverages">     <Product ProductCode="1">         <Description>Chai</Description>         <Price>18</Price>     </Product>     <Product ProductCode="2">         <Description>Chang</Description>         <Price>19</Price>     </Product>      </Category> <Category Category CategoryName="Condiments">     <Product ProductCode="3">         <Description>Aniseed Syrup</Description>         <Price>10</Price>     </Product>      </Category>  

If you want to retrieve an XML fragment listing all the products without any category information, you could use the following XPath expression to retrieve data in the Northwind catalog schema:

     Category/Product 

This expression would return the following XML fragment:

 <Product ProductCode="1">     <Description>Chai</Description>     <Price>18</Price> </Product> <Product ProductCode="2">     <Description>Chang</Description>     <Price>19</Price> </Product>  

You could also specify an XPath expression that contains criteria to limit the rows returned. For example, the following XPath expression could be used to access the catalog schema and retrieve only the products in category 2:

     Category[@CategoryID=‘2’]  

This expression returns the following XML fragment:

 <Category Category CategoryName="Condiments">     <Product ProductCode="3">         <Description>Aniseed Syrup</Description>         <Price>10</Price>     </Product>     <Product ProductCode="4">         <Description>Chef Anton’s Cajun Seasoning</Description>         <Price>22</Price>     </Product>      </Category> 

Using Mapping Schemas with ADO

ADO 2.6 supports the use of mapping schemas to retrieve XML data. To use a mapping schema with ADO, you instantiate a Command object to submit the query and a Stream object to receive the results. Your code must also specify the schema to be used, the root element for the resulting XML, and an XPath expression determining the data returned.

Creating the Command and Stream Objects

The code to create the Command and Stream objects and prepare them for use appears here:

 Dim objCmd Dim objStrm Set objCmd = CreateObject("ADODB.Command") Set objStrm = CreateObject("ADODB.Stream") objCmd.ActiveConnection = "PROVIDER=SQLOLEDB;" & _     "DATA SOURCE=server1;" & _     "INITIAL CATALOG=Northwind;" & _     "INTEGRATED SECURITY=SSPI;" objStrm.Open objCmd.Properties("Output Stream") = objStrm 

Specifying the Schema

You use the Mapping Schema property of the Command object to specify your schema. This property is supported by the SQLOLEDB provider and accessed through the Properties collection, as you can see in this example code:

 objCmd.Properties("Mapping Schema") = _     "c:\schemas\CatalogSchemaAnn.xml" 

Or you could set the Base Path property to the directory containing the schema file and the Mapping Schema property to the relative path to the schema file, as you see here:

 objCmd.Properties("Base Path") = "c:\schemas" objCmd.Properties("Mapping Schema") = "CatalogSchemaAnn.xml" 

This approach is particularly useful if you want to apply a style sheet to the results and the style sheet is stored in a relative location to the schema.

Specifying the Root Element

If the schema returns an XML fragment, you must add a root element to create a well-formed XML document. This requirement can be met by setting the Xml Root property through the Command object’s Properties collection, as shown in this sample code:

 objCmd.Properties("Xml Root") = "Catalog" 

This action results in an XML document that has a root element Catalog and contains the data mapped through the schema.

You can also add a root element to the results simply by concatenating a literal string, which can be useful if you want to include a custom namespace reference in the root element of the document.

 strXMLRoot = "<Catalog xmlns="x-schema:CatalogSchemaAnn.xml"> strXMLEnd = "</Catalog"> strXML = strXMLRoot & strXMLResults & strXMLEnd 

In this sample, the results of the query are stored in strXMLResults and the appropriate root and end tags are added to produce a well-formed XML document with a reference to the CatalogSchemaAnn.xml schema.

Using an XPath Expression with a Command Object

You assign the XPath expression that determines the data returned through the schema to the CommandText property of the Command object. For the XPath expression to be processed as such, and not as the default Transact-SQL expression, the Dialect property of the Command object must be set to the GUID identifying the DBGUID_XPATH dialect:

 objCmd.Dialect = "{ec2a4293-e898-11d2-b1b7-00c04f680c56}" objCmd.CommandText = "Category" 

This code fragment uses an XPath expression to retrieve all of the data mapped in the schema. The SQLOLEDB provider will interpret the XPath and use the annotations in the schema to construct the corresponding FOR XML EXPLICIT query. This query will then be executed, and the resulting XML will be returned in the output stream.

Receiving XML Results from a Mapping Schema

To receive and process the results from the mapping schema, you simply need to read the data from the output stream by using the ReadText method of the Stream object. The following code sample shows the entire process of retrieving XML using a mapping schema with ADO:

 Dim objCmd Dim objStrm Const adExecuteStream = 1024 Const DBGUID_XPATH = "{ec2a4293-e898-11d2-b1b7-00c04f680c56}" ‘Set up Command and Stream objects. Set objCmd = CreateObject("ADODB.Command") Set objStrm = CreateObject("ADODB.Stream") objCmd.ActiveConnection = "PROVIDER=SQLOLEDB;" & _     "DATA SOURCE=Server1;" & _     "INITIAL CATALOG=Northwind;" & _     "INTEGRATED SECURITY=SSPI;" objStrm.Open With objCMD     .Properties("Output Stream") = objStrm     ‘Specify the schema.     .Properties("Base Path") = "C:\schemas"     .Properties("Mapping Schema") = "CatalogSchemaAnn.xml"     ‘Specify the root element.     .Properties("Xml Root") = "Catalog"     ‘Specify the XPath expression.     .Dialect = DBGUID_XPATH     .CommandText = "Category" End With ‘Execute the query and process the results. objCmd.Execute, , adExecuteStream msgbox objStrm.ReadText 

You can also find code similar to this in the Demos\Chapter6 folder on the companion CD in a file named getCatalog.vbs.

Applying a Style Sheet with ADO

You might occasionally want to apply an XSL style sheet to the XML that’s returned through a schema. For example, you might be using an ASP to retrieve data from the database and display that data in a Web page. In that case, you might want to transform the data into HTML before sending it to the browser.

As with XML templates, you can use ADO to apply a style sheet by setting the Xsl property through the Command object’s Properties collection. In the following code example for an ASP, a mapping schema named CatalogSchemaAnn.xml in the XMLFiles subfolder of the virtual root is used to retrieve the products in the category passed to the page as a query string. You make use of an XSL style sheet named Catalog.xsl in the same location to transform the data to HTML, as you see here:

 <% Dim objCmd Dim objStrm Dim strXPath Const adExecuteStream = 1024 Const DBGUID_XPATH = "{ec2a4293-e898-11d2-b1b7-00c04f680c56}" ‘Create an XPath for the specified category. strXPath = "Category[@CategoryID=‘" & _     Request.QueryString("CategoryID") & _     "‘]" ‘Set up Command and Stream objects. Set objCmd = CreateObject("ADODB.Command") Set objStrm = CreateObject("ADODB.Stream") objCmd.ActiveConnection = "PROVIDER=SQLOLEDB;" & _     "DATA SOURCE=server1;" & _     "INITIAL CATALOG=Northwind;" & _     "INTEGRATED SECURITY=SSPI;" objStrm.Open With objCmd     .Properties("Output Stream") = objStrm     ‘Specify the schema.     .Properties("Base Path") = Server.MapPath("XMLFiles")     .Properties("Mapping Schema") = "CatalogSchemaAnn.xml"     ‘Specify the style sheet.     .Properties("Xsl") = "Catalog.xsl"     ‘Specify the root element.     .Properties("Xml Root") = "Catalog"     ‘Specify the XPath expression.     .Dialect = DBGUID_XPATH     .CommandText = strXPath End With ‘Execute the query and process the results. objCmd.Execute, , adExecuteStream Response.Write objStrm.ReadText %> 

Using Mapping Schemas over HTTP

You can use mapping schemas with SQLISAPI virtual directory applications to retrieve XML data over HTTP. You can take one of two approaches: you can publish a schema in a virtual name and access it directly through a URL, or you can reference a mapping schema in an XML template.

Using a Schema Virtual Name

You can publish schemas in a virtual name of type schema. This practice allows users to access the schema directly using a URL, in the same way they would access templates in a template virtual name. To enable access to schemas in a URL, you must create a schema virtual name and select the Allow XPath option for the SQL Server virtual directory application. Both of these tasks can be accomplished using the Configure SQL XML Support In IIS tool.

Once the virtual root has been configured appropriately, schemas can be accessed using a URL of the following form:

 http://servername/virtualroot/virtualname/schemafile/xpath?root=rootelement 

As you can see, the URL contains the name of the schema, the XPath expression, and the root element. For example, you could use the following URL to define an XPath query against the CatalogSchemaAnn.xml schema to retrieve data from the Northwind database:

 http://server1/northwinddata/schemas/CatalogSchemaAnn.xml/     Category?root=Catalog 

You can navigate to this URL by opening the shortcut named CatalogSchemaAnn in the Demos\Chapter6 folder on the companion CD.

Referencing a Schema in a Template

You can reference a schema in an XML template by using the xpath-query element and specifying the schema with this element’s mapping-schema attribute. As with all templates, you use the root element of the template itself as the root of the results.

You indicate the XPath expression that you plan to use in the xpath-query element, as shown in the following sample code:

 <Catalog xmlns:sql="urn:schemas-microsoft-com:xml-sql">     <sql:xpath-query mapping-schema="..\Schemas\CatalogSchemaAnn.xml">         Category     </sql:xpath-query> </Catalog> 

You can find this template in the Demos\Chapter6\Templates folder on the companion CD in a file named NWCatalogSchema.xml. This template could be published in a virtual name and accessed over HTTP like any other template.

You can use parameters in a template to customize the XPath expression. This procedure is similar to using parameters in a template that contains a SQL query, except that you use the $ symbol rather than the @ symbol to indicate a parameter in the query:

 <Catalog xmlns:sql="urn:schemas-microsoft-com:xml-sql">     <sql:header>         <sql:param name="CategoryID">1</sql:param>     </sql:header>     <sql:xpath-query mapping-schema="..\Schemas\CatalogSchemaAnn.xml">         Category[@CategoryID=$CategoryID]     </sql:xpath-query> </Catalog> 

This template is saved as CatalogParam.xml in the Demos\Chapter6\Templates folder on the companion CD. You can view the results by opening the shortcut named Template With Param in the Demos\Chapter6 folder. This template returns the data relating to a particular category. The category number is passed as a parameter in the template.

You can also define the schema in the template itself by using an inline schema. You use the id attribute to uniquely identify the element in which this attribute is contained and the is-mapping-schema attribute to indicate an inline schema in the Schema element. The value of the id attribute will then be used to reference the inline schema in the xpath-query element. The is-mapping- schema attribute takes a Boolean type value. Here’s an example of a template with an inline schema:

 <?xml version="1.0" ?> <ProductList xmlns:sql="urn:schemas-microsoft-com:xml-sql">     <Schema         xmlns="urn:schemas-microsoft-com:xml-data"         sql:is-mapping-schema="1"            sql:>         <ElementType name="Products">             <AttributeType name="ProductID"/>             <AttributeType name="ProductName"/>                <AttributeType name="UnitPrice"/>             <attribute type="ProductID"/>             <attribute type="ProductName"/>             <attribute type="UnitPrice"/>         </ElementType>     </Schema>     <sql:xpath-query mapping-schema="#ProductSchema">         Products     </sql:xpath-query> </ProductList> 

This code is also available in the Demos\Chapter6\Templates folder on the companion CD in a file named InlineSchema.xml. Inline schemas can be a useful way to keep all the data access logic together, but because the schema isn’t published as a separate document, you can’t share it with other applications.

Caching Schemas

By default, SQL Server caches schemas to improve performance on subsequent data requests. While this strategy is certainly desirable in most production scenarios, you might want to disable schema caching during development because changes made to the schema might not be immediately reflected in query results.

When you use schemas to retrieve data over ADO, you can prevent a schema from being cached by setting the SS_STREAM_FLAGS property to 8 (which is represented by the constant STREAM_FLAGS_DONTCACHEMAPPINGSCHEMA), as shown in this code sample:

 objCmd.Properties("SS_STREAM_FLAGS")=STREAM_FLAGS_DONTCACHEMAPPINGSCHEMA 

When you use HTTP to access schemas, you can prevent schema caching by using the Configure SQL XML Support In IIStool; check the Disable Caching Of Mapping Schemas check box on the Advanced tab of the Virtual Directory Properties dialog box.



Programming Microsoft SQL Server 2000 With Xml
Programming Microsoft SQL Server(TM) 2000 with XML (Pro-Developer)
ISBN: 0735613699
EAN: 2147483647
Year: 2005
Pages: 89

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net