XPath Queries in SQL Server 2000 Imagine the potential of accessing your database without needing to know any specifics about how that data is stored.Imagine the possibility of allowing your DBA the freedom to change the database structure without affecting your software code. Impossible? Not with XPath Queries and XDR Schemas. Using these two methods, practically any developer can write a program to access data in SQL Server 2000, just by knowing the structure of the XML documents returned by the server. In the last issue, we discussed querying XML data from SQL Server 2000 using a combination of URL Queries and Template Queries. While both types of queries allow the developer to retrieve XML data from SQL Server 2000, they do require knowledge of SQL Select statements, stored procedures, and the structure of the database. However, by using XPath Queries in conjunction with XDR Schemas, these requirements can be removed. XML - Data Reduced (XDR) Schemas define the structure of the XML document returned from SQL Server 2000 and enable various constraints to be placed upon the data returned. Unlike a Document Type Definition (DTD), an XML Schema describes the structure of an XML document using XML syntax. Also, DTDs treat all data content as character type, whereas XML Schemas allow the specification of various data types for XML elements and attributes. Authoring XDR Schemas An XML - Data Reduced Schema is simply an XML Schema with specific attributes used in defining the XML elements and attributes. Every XDR Schema must include the following: <?xml version = "1.0" ?>
<Schema
xmlns="urn:schemas-microsoft-com:xml-data"
xmlns:dt="urn:schemas-microsoft-com:datatypes"
xmlns:sql="urn:schemas-microsoft-com:xml-sql">
. . .
</Schema>
Notice that the above example is an XML document with a root node of Schema. This may or may not make sense to you. Remember that an XML Schema defines the structure and data constraints of an XML document using XML syntax. There are 2 basic attributes and 1 basic element needed to author XDR Schemas. The attributes needed are sql:field and sql:relation, and the element is the sql:relationship element. The sql:relation attribute is used to map an element to a table in the database. This has the effect of creating one XML element for every record in the table. The sql:field attribute is used to map a particular attribute or node value to a field from the related table. The sql:relationship element is used to relate elements within the XML document to other elements. It specifies the 2 tables and defines the join condition necessary to relate them together. Using those XDR attributes and elements, an XDR schema can be authored to return data from SQL Server 2000 in a specific XML format. The only required XDR attribute is sql:relation, which refers to a table or view in the database and can be placed on an ElementType, element, or attribute in the XDR Schema. The following is a simple example of using sql:relation in an XDR Schema to expose the Customer table from the Northwind database in XML format: <?xml version="1.0" encoding="windows-1252" ?>
<Schema
xmlns="urn:schemas-microsoft-com:xml-data"
xmlns:dt="urn:schemas-microsoft-com:datatypes"
xmlns:sql="urn:schemas-microsoft-com:xml-sql">
<AttributeType name="CustomerID"/>
<AttributeType name="CompanyName"/>
<AttributeType name="ContactName"/>
<AttributeType name="Address"/>
<AttributeType name="City"/>
<AttributeType name="Phone"/>
<ElementType name="CUSTOMER"
sql:relation="Customers">
<attribute type="CustomerID"/>
<attribute type="CompanyName"/>
<attribute type="ContactName"/>
<attribute type="Address"/>
<attribute type="City"/>
<attribute type="Phone"/>
</ElementType>
</Schema>
Note that this example exposes only 6 fields from the Customer table. We first specify 6 different attribute types with the same names as the field names in the database. We then specify the customer element, which uses the 6 attributes defined above. By specifying the sql:relation attribute on an ElementType element within our schema, the relation is inherited by all elements and attributes of that ElementType. This means that we do not need to specify the sql:relation attribute on every element or attribute within our schema to indicate that they all are populated from the Customer table. Because we defined our attribute names just as they exist in the Northwind database, we did not need to use the sql:field attribute (which would allow us to map a field to an attribute of a different name). Keep in mind that XML is case?sensitive. Therefore, the attribute names defined above must match exactly with the field names defined in the database for this XDR Schema to work. The schema returns an XML document whose structure matches the definition in the schema (see Figure 1). 
Figure 1 - Internet Explorer displays a sample XML data set returned from SQL Server 2000 using just the sql:relation attribute. | & | | 
By: Travis Vandersypen Travis Vandersypen is a Senior Software Developer at EPS Software Corporation, located in Houston, Texas.
He concentrates on consulting on and developing COM-based, distributed applications. He is an international author and speaker, co-author of the Fox Extension Classes, co-author of the Voodoo Web Controls, and 3-time Visual FoxPro Excellence Award Nominee.
He can be reached via email at: travis@eps-software.com.
travis@dilmad.com | Fast Facts | | XPath Queries in conjunction with XDR Schemas remove the need to know about SQL Select statements, stored procedures, and the structure of the database. This gives the DBA the freedom to make database structure and stored procedure changes without needing to change the software code itself. | |
|