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).
The sql:field attribute may be used in conjunction with the sql:relation attribute to create elements or attributes that do not exactly match their definitions in the database. For instance, the following XDR Schema will produce an XML document with different attribute names for some of the fields in the Customer table (see Figure 2).
<?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">
<ElementType name="CUSTOMER" sql:relation="Customers">
<AttributeType name="Id"/>
<AttributeType name="Company"/>
<AttributeType name="Contact"/>
<AttributeType name="Address"/>
<AttributeType name="City"/>
<AttributeType name="Phone"/>
<attribute type="Id" sql:field="CustomerID"/>
<attribute type="Company" sql:field="CompanyName"/>
<attribute type="Contact" sql:field="ContactName"/>
<attribute type="Address" sql:field="Address"/>
<attribute type="City" sql:field="City"/>
<attribute type="Phone" sql:field="Phone"/>
</ElementType>
</Schema>
Now, let's say we don't like having the fields mapped to attributes. Instead, we could use the following schema to produce an XML document with elements (separate tags) for the fields in the Customer table (see Figure 3).
<?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">
<ElementType name="COMPANY"/>
<ElementType name="CUSTOMERID"/>
<ElementType name="CONTACT"/>
<ElementType name="ADDRESS"/>
<ElementType name="CITY"/>
<ElementType name="PHONE"/>
<ElementType name="CUSTOMER" sql:relation="Customers">
<element type="CUSTOMERID" sql:field="CustomerID"/>
<element type="COMPANY" sql:field="CompanyName"/>
<element type="CONTACT" sql:field="ContactName"/>
<element type="ADDRESS" sql:field="Address"/>
<element type="CITY" sql:field="City"/>
<element type="PHONE" sql:field="Phone"/>
</ElementType>
</Schema>
In addition to using the sql:relation and sql:field attributes, you can use the sql:relationship element to produce nested XML documents where elements may contain related child elements. Using this technique, we can create a tag for each customer, with sub-tags for all the orders each customer places (see Figure 4). A sample XDR Schema for this is:
<?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">
<ElementType name="ORDERID"/>
<ElementType name="ORDERDATE"/>
<ElementType name="ORDER" sql:relation="Orders">
<element type="ORDERID" sql:field="OrderID"/>
<element type="ORDERDATE" sql:field="OrderDate"/>
</ElementType>
<ElementType name="COMPANY"/>
<ElementType name="CUSTOMERID"/>
<ElementType name="CONTACT"/>
<ElementType name="ADDRESS"/>
<ElementType name="CITY"/>
<ElementType name="PHONE"/>
<ElementType name="CUSTOMER" sql:relation="Customers">
<element type="CUSTOMERID" sql:field="CustomerID"/>
<element type="COMPANY" sql:field="CompanyName"/>
<element type="CONTACT" sql:field="ContactName"/>
<element type="ADDRESS" sql:field="Address"/>
<element type="CITY" sql:field="City"/>
<element type="PHONE" sql:field="Phone"/>
<element type="ORDER">
<sql:relationship key="CustomerID"
key-relation="Customers"
foreign-key="CustomerID"
foreign-relation="Orders"/>
</element>
</ElementType>
</Schema>
There are 4 attributes on the sql:relationship element that must be specified: key, key-relation, foreign-key, foreign-relation. The key-relation attribute specifies the Parent Table and the key attribute specifies the key on the Parent Table to use to relate it to a child table. The foreign-relation attribute specifies the Child Table and the foreign-key attribute specifies the key on the Child Table used to relate it to the Parent Table. SQL Server 2000 is able to relate and nest related XML elements using the values specified in the attributes of the sql:relationship element.
XPath Queries
XPath Queries, coupled with XDR Schemas, allow the developer to access and query data from SQL Server 2000 in the same manner as using the XMLDOM object. For instance, navigate to the following URL (assuming you installed your SQL Server on the local machine and created a virtual directory named “sql2000”): http://localhost/sql2000/schema/sampleschema1.xml/CUSTOMER?root=ROOT. This produces an XML document without writing a single line of SQL (see Figure 1).
Similarly, navigating to http://localhost/sql2000/schema/sampleschema4.xml/CUSTOMER?root=ROOT produces an XML document with customers and their related orders (see Figure 4).
Keep in mind that you may use the XMLDOM to query SQL Server 2000. For instance, you could do the following in Visual FoxPro:
LOCAL loXML
loXML = CreateObject("MSXML.DOMDocument")
If VarType(loXML) = "O"
loXML.ASync = .F.
loXML.Load("http://localhost/sql2000/schema/sampleschema4.xml/CUSTOMER/root=ROOT")
EndIf
The above example will load every customer with their matching orders into the XMLDOM object. However, rather than download the entire set of data into the XMLDOM object and then query its contents for specific nodes, why not let SQL Server 2000 do that for you automatically on the server and send back a reduced data set? Let's say you only wanted customer ALFKI. Simply navigate to http://localhost/sql2000/schema/sampleschema4.xml/CUSTOMER[CUSTOMERID=‘ALFKI’]?root=ROOT (see Figure 4).
Notice that the syntax for an XPath query is almost identical to the syntax used for an XSL Pattern match within the XMLDOM itself. In truth, the main difference is the exclusion of the root node in the XPath query that would otherwise have to be included in an XSL Pattern match in the XMLDOM.
Conclusion
Using XPath queries and XDR Schemas, a developer no longer needs to have specific knowledge of SQL Select statements, stored procedures, or even the database structure. These tasks can now be left in the hands of the DBA, allowing the developer to concentrate on the business logic of the application, rather than on authoring SQL Select statements. XDR Schemas also allow the DBA to make database structure changes without affecting the code written by the developer, as long as he or she updates the appropriate XDR Schema files.
