Annotations to the XDR Schema
| There are several annotations to the XDR schema language that enable it to carry out its intended purpose, mapping an XML document to a relational database. These annotations specify XML-to-relational mappings, including mapping from elements and attributes in the XDR schema to tables/views and columns in the databases. Remember the following and you'll have half the battle won: Element names in an annotated schema map to table (view) names in a database, and attribute names map to the columns. These annotations also can specify the hierarchical relationships in XML (modeling database relationships). Table 5.5 is a list of the annotations. Each of these will be covered in detail with examples in this chapter. Table 5.5. Schema Annotations
As seen in the simple example given in the section "Default Mapping of XDR Elements and Attributes," XDR schemas use XPath expressions in their queries to select data. In Chapter 2,"XSLT Stylesheets," we discussed the abbreviated form of XPath query. To keep it simple for now, we will continue to use this abbreviated form until we discuss XPath expressions at length in Chapter 6, "Using XPath Queries." Explicit Mapping of XDR Elements and Attributes to Tables and ColumnsAs I said previously, as long as the names of the elements and attributes are the same as the table (view) and column names, respectively, in the database, explicit mappings are not necessary. An element name in an annotated schema maps to the table (view) name in the specified database, and the attribute names map to the column names. In practice, however, this is rarely the case. Changes to column names for reports and so on are regularly done. If the names are not the same, however, two annotations are provided to specify the mapping between an element/attribute in an XML document and the table/column in a database.These annotations are as follows :
We'll cover these two annotations in the following sections. sql:relationThe sql:relation annotation explicitly maps an XML element to a database table or view and can be added to an <ElementType> , <element> , or <attribute> node in the schema. A common use of the annotation is to relate illegal identifiers from SQL Server to an XML node. For example, a table named PUB INFO in SQL Server is a valid table name, but it's an invalid XML element name.We can get around this by using sql:relation to explicitly remap the table name, like this: <ElementType name="PI" sql:relation="[PUB INFO]"> Using sql:relation on an <ElementType> causes the relation to apply to all attributes and subelements on that <ElementType> . This annotation is ignored on <AttributeType> . Let's look at an example in Listing 5.20. Here, the sql:relation annotation maps the schema <Order> element to the Orders table. Because the annotation was applied to the <ElementType> , all the attributes also map to the Orders table. Save this file in your schema virtual directory. Listing 5.20 An Example of the sql:relation Annotation <?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"> <ElementType name="Order" sql:relation="Orders" > <AttributeType name="OrderID" /> <AttributeType name="CustomerID" /> <AttributeType name="ShipName" /> <attribute type="OrderID" /> <attribute type="CustomerID" /> <attribute type="ShipName" /> </ElementType> </Schema> Let's use the template in Listing 5.21 to query against this schema. Listing 5.21 Query Template<ROOT xmlns:sql="urn:schemas-microsoft-com:xml-sql"> <sql:xpath-query mapping-schema="../schemas/OrderSchema.xml"> /Order[@CustomerID="VINET"] </sql:xpath-query> </ROOT> Executing the template with the following URL http://iisserver/Nwind/templates/OrderTemplate.xml provides the results in Listing 5.22. Listing 5.22 Results of Using the sql:relation Annotation<ROOT xmlns:sql="urn:schemas-microsoft-com:xml-sql"> <Order OrderID="10248" CustomerID="VINET" ShipName="Vins et alcools Chevalier" /> <Order OrderID="10274" CustomerID="VINET" ShipName="Vins et alcools Chevalier" /> <Order OrderID="10295" CustomerID="VINET" ShipName="Vins et alcools Chevalier" /> <Order OrderID="10737" CustomerID="VINET" ShipName="Vins et alcools Chevalier" /> <Order OrderID="10739" CustomerID="VINET" ShipName="Vins et alcools Chevalier" /> </ROOT> Notice that what used to be the <Orders> element is now the <Order> element. In the event that the schema has a subelement, some changes have to be made. Look at Listing 5.23, a variation of our OrderSchema.xml . Listing 5.23 An XDR Schema with a Subelement<?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"> <ElementType name="ShipAddress" content="textOnly" /> <ElementType name="Order" sql:relation="Orders"> <AttributeType name="OrderID" /> <AttributeType name="CustomerID" /> <attribute type="OrderID" /> <attribute type="CustomerID" /> <element type="ShipAddress" /> </ElementType> </Schema> As we know, elements map to table names and not columns by default, so it's necessary to specify the content attribute in the <ElementType> declaration and set it equal to textOnly . In the next section, we'll see that there is another way to assign an XDR schema element to a column name instead of a table name. This is the sql:field annotation. sql:fieldThe sql:field annotation maps an attribute or a noncomplex subelement to a database column by specifying the column name in its declaration. <AttributeType> elements ignore this annotation. Four-part column names such as database.owner.table.columnname are not allowed. They are not allowed in any annotation that takes a column name for a value. For example, sql:field specifically needs a column name to perform its mapping function, so sql:field cannot use the four-part name. Listing 5.24 is a sample schema that we'll use to illustrate the sql:field annotation. Listing 5.24 An Example of the sql:field Annotation<?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"> <ElementType name="Order" sql:relation="Orders"> <AttributeType name="OrderID" /> <AttributeType name="CustomerID" /> <AttributeType name="SName" /> <attribute type="OrderID" /> <attribute type="CustomerID" /> <attribute type="SName" sql:field="ShipName"/> </ElementType> </Schema> Listing 5.25 shows the partial results. Listing 5.25 Results of the sql:field Annotation<ROOT xmlns:sql="urn:schemas-microsoft-com:xml-sql"> <Order OID="10248" CustomerID="VINET" SName="Vins et alcools Chevalier" /> <Order OID="10274" CustomerID="VINET" SName="Vins et alcools Chevalier" /> <Order OID="10295" CustomerID="VINET" SName="Vins et alcools Chevalier" /> <Order OID="10737" CustomerID="VINET" SName="Vins et alcools Chevalier" /> <Order OID="10739" CustomerID="VINET" SName="Vins et alcools Chevalier" /> </ROOT> The results show that the OrderID column is now OID , and the ShipName column is now SName. I did not modify the CustomerID element in the schema. Because this is the qualifying criterion for the search, changing it can cause problems. If there is an element type in your schema, as in the partial Listing 5.26, then you must specify either content=textOnly or sql:field=" fieldname " (elements map to relations, not columns). Listing 5.26 Partial Listing of an element with sql:field... <attribute type="OrderID" /> <attribute type="CustomerID" /> <element type="ShipName" sql:field="ShipName"/> ... Relating Tables with sql:relationshipThe sql:relationship annotation specifies foreign key relationships between tables in a database. For example, the Products table is related to the Suppliers table because the SupplierID column in the Products table is a foreign key referring to the SupplierID column in the Suppliers table. This relationship is equivalent to the hierarchical nesting of XML elements in which <Suppliers> would have the child element <Products> . From the XDR schema perspective, sql:relationship nests the elements based on the primary and foreign key relationships between tables to which the elements map. To generate the proper nesting of elements, sql:relationship has four attributes that must be specified.These attributes are listed in Table 5.6. Table 5.6. Required sql:relationship Attributes
Some restrictions are placed on the sql:relationship annotation, as follows:
Leaving out the sql:key-fields specification can cause problems with proper element nesting. I recommend that you provide the sql:key-fields attribute in all schemas. That way, you guarantee that element nesting turns out the way you expected. In the case of an <element> containing a child element and a sql:relationship attribute that doesn't specify the primary key of the parent element, you must provide the sql:key-fields attribute, which we'll cover shortly. Listing 5.27 is a sample schema expressing the relationship between the Suppliers table and the Products table. Figure 5.2 diagrams the relationship. Figure 5.2. The Suppliers/Products relationship. Listing 5.27 A Schema with the sql:relationship Annotation<?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"> <ElementType name="Product" sql:relation="Products" > <AttributeType name="ProductID" /> <AttributeType name="ProductName" /> <AttributeType name="UnitPrice" /> <attribute type="ProductID" /> <attribute type="ProductName" /> <attribute type="UnitPrice" /> </ElementType> <ElementType name="Supplier" sql:relation="Suppliers" > <AttributeType name="SupplierID" /> <attribute type="SupplierID" /> <element type="Product" > <sql:relationship key-relation="Suppliers" key="SupplierID" foreign-key="SupplierID" foreign-relation="Products" /> </element> </ElementType> </Schema> Using the template file in Listing 5.28 produces the results in Listing 5.29. Listing 5.28 Template File for Schema Listing 5.27<ROOT xmlns:sql="urn:schemas-microsoft-com:xml-sql"> <sql:xpath-query mapping-schema="../schemas/OrderSchema.xml"> Supplier[@SupplierID="3"] </sql:xpath-query> </ROOT> Listing 5.29 Results of Template Execution<ROOT xmlns:sql="urn:schemas-microsoft-com:xml-sql"> <Supplier SupplierID="3"> <Product ProductID="6" ProductName="Grandma's Boysenberry Spread" UnitPrice="25" /> <Product ProductID="7" ProductName="Uncle Bob's Organic Dried Pears" UnitPrice="30" /> <Product ProductID="8" ProductName="Northwoods Cranberry Sauce" UnitPrice="40" /> </Supplier> </ROOT> Indirect RelationshipsSo far, we have talked about and examined examples in which tables were directly related via foreign keys. If I want to generate XML data from two tables that are related by an intermediate table but I don't want any of the intermediate table's data, I have an indirect relationship. Let's look at the relationship between the three tables Customers, Orders, and Order Details. Let's say we want data from Customers and Order Details but not Orders. Customers make orders, which have details, so how do we present customer data and order detail data? Let's look at Listing 5.30. Listing 5.30 Eliminating the Middle Man<?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"> <ElementType name="OD" sql:relation="[Order Details]" > <AttributeType name="OrderID" /> <AttributeType name="ProductID" /> <AttributeType name="UnitPrice" /> <attribute type="OrderID" /> <attribute type="ProductID" /> <attribute type="UnitPrice" /> </ElementType> <ElementType name="Customer" sql:relation="Customers" > <AttributeType name="CustomerID" /> <attribute type="CustomerID" /> <element type="OD" > <sql:relationship key-relation="Customers" key="CustomerID" foreign-relation="Orders" foreign-key="CustomerID"/> <sql:relationship key-relation="Orders" key="OrderID" foreign-relation="[Order Details]" foreign-key="OrderID" /> </element> </ElementType> </Schema> In this XDR schema, there is no mention of the Orders element, even though this is the table that relates Customers to Order Details . To accomplish this, we specify two relationships, the first between the Customers table and the Orders tables and the second between the Orders table and the Order Details table. Listing 5.31 shows the template file we will execute. It applies the OrderSchema.xml mapping schema and queries for all customers who have a CustomerID of "ALFKI." Listing 5.32 shows the results obtained. Figure 5.3 diagrams the relationship. Figure 5.3. The Customers/Orders/Order Details relationship. Listing 5.31 Template for the Schema in Listing 5.30<ROOT xmlns:sql="urn:schemas-microsoft-com:xml-sql" > <sql:xpath-query mapping-schema="../schemas/OrderSchema.xml" > /Customer[@CustomerID="ALFKI"] </sql:xpath-query> </ROOT> Listing 5.32 Results of the Indirect Relation<ROOT xmlns:sql="urn:schemas-microsoft-com:xml-sql"> <Customer CustomerID="ALFKI"> <OD OrderID="10643" ProductID="28" UnitPrice="45.6" /> <OD OrderID="10643" ProductID="39" UnitPrice="18" /> <OD OrderID="10643" ProductID="46" UnitPrice="12" /> <OD OrderID="10692" ProductID="63" UnitPrice="43.9" /> <OD OrderID="10702" ProductID="3' UnitPrice="10" /> ... </ROOT> Next we'll look at adding our own elements to resulting documents. Including Schema Elements in XML Documents with sql:is-constantThe sql:is-constant annotation enables us to specify an element that does not map to a table/view or a data column. These types of elements are called constant elements. They are specified on <ElementType> elements and require a Boolean value assignment (0 = false, 1 = true). The <ElementType> to which it is assigned does not map to any database table. This annotation can be used to do the following:
Listing 5.33 is the schema for our sql:is-constant example. This is followed by the template in Listing 5.34 and the partial results Listing 5.35. In this example, we generate the ProductList element to act as a container element for our list of products. Listing 5.33 Schema for sql:is-constant<?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"> <ElementType name="Products" > <AttributeType name="ProductID" /> <AttributeType name="ProductName" /> <AttributeType name="UnitPrice" /> <attribute type="ProductID" /> <attribute type="ProductName" /> <attribute type="UnitPrice" /> </ElementType> <ElementType name="ProductList" sql:is-constant="1"> <element type="Products"> <sql:relationship key-relation="Suppliers" key="SupplierID" foreign-key="SupplierID" foreign-relation="Products" /> </element> </ElementType> <ElementType name="Suppliers"> <AttributeType name="SupplierID" /> <attribute type="SupplierID" /> <element type="ProductList" /> </ElementType> </Schema> Listing 5.34 Template for sql:is-constant<ROOT xmlns:sql="urn:schemas-microsoft-com:xml-sql"> <sql:xpath-query mapping-schema="../schemas/OrderSchema.xml"> /Suppliers </sql:xpath-query> </ROOT> Listing 5.35 Partial Results for the sql:is-constant Example<ROOT xmlns:sql="urn:schemas-microsoft-com:xml-sql"> <Suppliers SupplierID="1"> <ProductList> <Products ProductID="1" ProductName="Chai" UnitPrice="18" /> <Products ProductID="2" ProductName="Chang" UnitPrice="19" /> <Products ProductID="3" ProductName="Aniseed Syrup" UnitPrice="10" /> </ProductList> </Suppliers> ... <ROOT> Excluding Schema Elements from XML Documents with sql:map-fieldThe previous annotation, sql:is-constant , provided us with a mechanism to generate elements that did not map to a database table or column, yet they appeared in the XML document. The sql:map-field annotation does the opposite . It prevents schema elements from appearing in the XML output. This is useful if you want to use third-party XDR schemas, and one or more of the elements in these schemas contains elements that don't map to your data. See the example in Listing 5.36 in which the attribute short-desc (this is a third-party schema) is prevented from appearing in our output because we don't have that data in our database. Just like sql:is-constant , sql:map-field takes a Boolean value (true = 1, false = 0). It can be used on <attribute> , <element> , or <ElementType> with the content=textOnly setting. If an <element> or <ElementType> maps to a table, this annotation is not valid. Listing 5.36 An Example of sql:map-field Use <?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"> <ElementType name="Products" > <AttributeType name="ProductName" /> <AttributeType name="SupplierID" /> <AttributeType name="QuantityPerUnit" /> <AttributeType name="Short-desc" /> <attribute type="ProductName" /> <attribute type="SupplierID" /> <attribute type="QuantityPerUnit" /> <attribute type="Short-desc " sql:map-field="0" /> </ElementType> </Schema> Listing 5.37 shows the template file to execute. Listing 5.37 The sql:map-field Template File<ROOT xmlns:sql="urn:schemas-microsoft-com:xml-sql"> <sql:xpath-query mapping-schema="../schemas/OrderSchema1.xml"> /Products[@SupplierID="1"] </sql:xpath-query> </ROOT> The results are shown in Listing 5.38. Listing 5.38 Results<ROOT xmlns:sql="urn:schemas-microsoft-com:xml-sql"> <Products ProductName="Chai" SupplierID="1" QuantityPerUnit="10 boxes x 20 bags" /> <Products ProductName="Chang" SupplierID="1" QuantityPerUnit="24 - 12 oz bottles" /> <Products ProductName="Aniseed Syrup" SupplierID="1" QuantityPerUnit="12 - 550 ml bottles" /> </ROOT> Notice that the Short-desc attribute did not appear in our output. That was on purpose. Identifying Key Columns with sql:key-fieldsThe sql:key-fields annotation's purpose is to ensure the proper nesting of elements and to maintain their hierarchy. I recommend that this annotation be used in all your schemas. It guarantees that proper nesting will occur. The sql:key-fields annotation can be added to <element> and <ElementType> entities. This will identify columns that uniquely identify rows (keys). Placing spaces between the column values specifies multicolumn keys. One last point: sql:key-fields must be specified in an element containing a child element and a <sql:relationship> , which is defined between the element and the child, that does not provide the primary key of the table specified in the parent element. Listing 5.39 starts another example for you by providing a schema file that is utilized by the template in Listing 5.40. The results are given in Listing 5.41. Listing 5.39 Schema Utilizing sql:key-fields<?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"> <ElementType name="Product" sql:relation="Products" > <AttributeType name="ProductID" /> <AttributeType name="ProductName" /> <AttributeType name="UnitPrice" /> <attribute type="ProductID" /> <attribute type="ProductName" /> <attribute type="UnitPrice" /> </ElementType> <ElementType name="Supplier" sql:relation="Suppliers" sql:key-fields="SupplierID" > <AttributeType name="SupplierID" /> <attribute type="SupplierID" /> <element type="Product" > <sql:relationship key-relation="Suppliers" key="SupplierID" foreign-key="SupplierID" foreign-relation="Products" /> </element> </ElementType> </Schema> Listing 5.40 The Template to Execute the sql:key-fields Schema<ROOT xmlns:sql="urn:schemas-microsoft-com:xml-sql"> <sql:xpath-query mapping-schema="../schemas/OrderSchema.xml"> Supplier </sql:xpath-query> </ROOT> Listing 5.41 The Results of Using sql:key-fields<ROOT xmlns:sql="urn:schemas-microsoft-com:xml-sql"> <Supplier SupplierID="1"> <Product ProductID="1" ProductName="Chai" UnitPrice="18" /> <Product ProductID="2" ProductName="Chang" UnitPrice="19" /> <Product ProductID="3" ProductName="Aniseed Syrup" UnitPrice="10" /> </Supplier> <Supplier SupplierID="2"> <Product ProductID="4" ProductName="Chef Anton's Cajun Seasoning" UnitPrice="22" /> <Product ProductID="5" ProductName="Chef Anton's Gumbo Mix" UnitPrice="21.35" /> <Product ProductID="65" ProductName="Louisiana Fiery Hot Pepper Sauce" UnitPrice="21.05" /> <Product ProductID="66" ProductName="Louisiana Hot Spiced Okra" UnitPrice="17" /> </Supplier> ... <ROOT> Specifying a Target Namespace with sql:target-namespaceThe sql:target-namespace annotation places elements and attributes from the default namespace into another specified namespace. This prevents having to add a namespace prefix to the elements of a previously generated document when you want to assign that document to a namespace. This annotation can only appear as an attribute of the <schema> element. sql:target-namespace utilizes a namespace URI to generate mapping schema elements and attributes. This URI is applied to all elements and attributes in the default namespace. Query documents contain xmlns:prefix="uri" declarations and prefixes. The URI comes from the value of the sql:target-namespace annotation. The prefix is generated arbitrarily and does not correspond to any values in the schema. Listing 5.42 shows how the target namespace is specified. Listing 5.42 Schema Utilizing sql:target-namespace<?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" sql:target-namespace="urn:SchemaNamespace"> <ElementType name="Product" sql:relation="Products" > <AttributeType name="ProductID" /> <AttributeType name="ProductName" /> <AttributeType name="UnitPrice" /> <attribute type="ProductID" /> <attribute type="ProductName" /> <attribute type="UnitPrice" /> </ElementType> <ElementType name="Suppliers"> <AttributeType name="SupplierID" /> <attribute type="SupplierID" /> <element type="Product" > <sql:relationship key-relation="Suppliers" key="SupplierID" foreign-key="SupplierID" foreign-relation="Products" /> </element> </ElementType> </Schema> The template file in Listing 5.43 can be used to test the schema in Listing 5.42. Listing 5.43 The Template to Test the sql:target-namespace Annotation<ROOT xmlns:sql="urn:schemas-microsoft-com:xml-sql"> <sql:xpath-query mapping-schema="../schemas/OrderSchema.xml" xmlns:x="urn:SchemaNamespace" > x:Suppliers[@SupplierID="3"] </sql:xpath-query> </ROOT> Looking at this template, you can see that we assigned an arbitrary prefix to the XPath query to request all the Suppliers elements from the namespace SchemaNamespace . The results are shown in Listing 5.44. Notice that our xmlns was returned in the second line with the value we specified for a namespace. Also, notice that the returned prefix is entirely arbitrary. Listing 5.44 Results of the sql:target-namespace Query<ROOT xmlns:sql="urn:schemas-microsoft-com:xml-sql"> <y0:Suppliers xmlns:y0="urn:SchemaNamespace" SupplierID="3"> <y0:Product ProductID="6" ProductName="Grandma's Boysenberry Spread" UnitPrice="25" /> <y0:Product ProductID="7" ProductName="Uncle Bob's Organic Dried Pears" UnitPrice="30" /> <y0:Product ProductID="8" ProductName="Northwoods Cranberry Sauce" UnitPrice="40" /> </y0:Suppliers> </ROOT> sql:id-prefix and Valid ID , IDREF , and IDREFS Type AttributesThe sql:id-prefix annotation ensures that if a document contains multiple IDs, they will be unique. Attributes can be made an ID type attribute, and intradocument links can then be created by specifying IDREF or IDREFS to refer to the ID type attributes. ID , IDREF , and IDREFS relate directly to database primary key/foreign key relationships (differences are minimal). By definition, ID attributes must be distinct in XML documents; therefore, CustomerID and OrderID must also be distinct in XML documents if they exist. A problem occurs in a database, however, because it is possible for CustomerID and OrderID to both be equal to "1" or "2" or whatever. Validity of ID , IDREF , and IDREFS in a document requires the following:
Essentially, sql:id_prefix prepends a string to ID , IDREF , and IDREFS to make it unique. Prefix validity and value uniqueness of ID, IDREF, and IDREFS are not checked. Also, the value of the attributes is limited to 4,000 characters , including the prefix, if present. Listing 5.45 is a schema document illustrating sql:id-prefix .This is followed by the template in Listing 5.46 to test it and results in Listing 5.47. Listing 5.45 sql:id_prefix Schema Document<?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"> <ElementType name="Product" sql:relation="Products" sql:key- fields="ProductID" > <AttributeType name="ProductID" dt:type=" id" sql:id-prefix="Prod-" /> <AttributeType name="ProductName" /> <AttributeType name="UnitPrice" /> <attribute type="ProductID" /> <attribute type="ProductName" /> <attribute type="UnitPrice" /> </ElementType> <ElementType name="Supplier" sql:relation="Suppliers" > <AttributeType name="SupplierID" dt:type="id"/> <attribute type="SupplierID" /> <AttributeType name="ProductList" dt:type="idrefs" sql:id-prefix="Prod-"/ > <attribute type="ProductList" sql:relation="Products" sql:field="ProductID"> <sql:relationship key-relation="Suppliers" key="SupplierID" foreign-key="SupplierID" foreign-relation="Products" /> </attribute> <element type="Product" > <sql:relationship key-relation="Suppliers" key="SupplierID" foreign-key="SupplierID" foreign-relation="Products" /> </element> </ElementType> </Schema> Listing 5.46 The sql:id_prefix Template to Test the Schema<ROOT xmlns:sql="urn:schemas-microsoft-com:xml-sql"> <sql:xpath-query mapping-schema="../schemas/OrderSchema.xml"> Supplier[@SupplierID="1"] </sql:xpath-query> </ROOT> Listing 5.47 sql:id_prefix Results<ROOT xmlns:sql="urn:schemas-microsoft-com:xml-sql"> <Supplier SupplierID="1" ProductList="Prod-1 Prod-2 Prod-3"> <Product ProductID="Prod-1" ProductName="Chai" UnitPrice="18" /> <Product ProductID="Prod-2" ProductName="Chang" UnitPrice="19" /> <Product ProductID="Prod-3" ProductName="Aniseed Syrup" UnitPrice="10" /> </Supplier> </ROOT> Why all the fuss? In this schema, we declared the Suppliers element to be an ID type. We also declared the Products element to be an ID type and to have a prefix of Prod . If we had not declared this prefix, and both a SupplierID and ProductID of, say, 1 had come up (look at the results, it did!), then the document would not have been valid because duplicate ID values are illegal. sql:id-prefix prevented this. Before you start yelling about changing the data value retrieved from the database and therefore invalidating the data, remember that XSLT has a function that can change the data back to the way it was at any timeproblem solved . Creating CDATA Sections with sql:use-cdataAs we saw in the section "Entities in XML" in Chapter 4, there are certain charac-terssuch as <, >, &, and so onthat are treated as markup characters by XML. If it were necessary to prevent this behavior and output the characters as is, you would surround these characters with a CDATA section. You can do the equivalent in XDR schemas by utilizing the sql:use-cdata annotation. The sql:use-cdata annotation is used as an attribute on <ElementType> or <element> entities and requires a boolean value (0 = false, 1 = true). Listing 5.48 presents a schema that applies CDATA sections to the ShipName column of the Orders table to ensure that any special characters are not interpreted as markup. Listing 5.49 shows the template, and the partial result list is shown in Listing 5.50. sql:use-cdata cannot be used with sql:url-encode , which we talk about in the next section, or the attribute types ID , IDREFS , NMTOKEN , or NMTOKENS . Listing 5.48 Schema Utilizing sql:use-cdata<?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"> <ElementType name="ShipName" content="textOnly" /> <ElementType name="Orders" > <element type="ShipName" sql:use-cdata="1" /> </ElementType> </Schema> Listing 5.49 The Template to Test sql:use-cdata<ROOT xmlns:sql="urn:schemas-microsoft-com:xml-sql"> <sql:xpath-query mapping-schema="../schemas/OrderSchema1.xml"> /Orders </sql:xpath-query> </ROOT> Listing 5.50 The Results of Specifying sql:use-cdata<ROOT xmlns:sql="urn:schemas-microsoft-com:xml-sql"> <Orders> <ShipName> <![CDATA[Vins et alcools Chevalier]]> </ShipName> </Orders> <Orders> <ShipName> <![CDATA[Toms Spezialitten]]> </ShipName> </Orders> ... </ROOT> Requesting URL References to BLOB data with sql:url-encodeBLOB data is stored in SQL Server columns in base 64-encoded format. The sql:url-encode annotation returns a reference (URI) to this data instead of the value of the field. You can then use this URI to access the data. sql:url-encode is dependent on the primary key field to generate a unique select. Use the sql:key-fields annotation for this purpose. sql:url-encode also is one of the annotations that takes a Boolean value attribute (0 = false, 1 = true). sql:url-encode cannot be used with the sql:use-cdata annotation or on ID , IDREF , IDREFS , NMTOKEN , or NMTOKENS . In Listing 5.51, we specify sql:url-encode on the Photo attribute to have a URI reference returned instead of the filed value. We can then use the URI to retrieve the data later. The template file is given in Listing 5.52. Listing 5.51 sql:url-encode Example<?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"> <ElementType name="Employee" sql:relation="Employees" sql:key-fields="EmployeeID" > <AttributeType name="EmployeeID" /> <AttributeType name="Photo" /> <attribute type="EmployeeID" /> <attribute type="Photo" sql:url-encode="1" /> </ElementType> </Schema> Listing 5.52 The Template to Execute the sql:url-encode Example<ROOT xmlns:sql="urn:schemas-microsoft-com:xml-sql"> <sql:xpath-query mapping-schema="../schemas/ProductSchema.xml"> /Employee[@EmployeeID=1] </sql:xpath-query> </ROOT> The result of specifying sql:url-encode is as follows: <ROOT xmlns:sql="urn:schemas-microsoft-com:xml-sql"> <Employee EmployeeID="1" Photo="dbobject/Employees[@EmployeeID="1"]/@Photo" /> </ROOT> Retrieving Unconsumed Data with sql:overflow-fieldThis annotation applies to the use of the OPENXML extension, which we'll cover in Chapter 8, "OPENXML." I'll defer discussion of this annotation until then, where it will make more sense. Filtering Records with sql:limit-field and sql:limit-valueThe sql:limit-field and sql:limit-value annotations specify the database column and limiting value, respectively, that are used to restrict the rows returned from a query. The sql:limit-field actually further qualifies the relation specified in sql:relationship . Data Type CoercionsUsing an XDR schema, we can control the type of data that is output when data is obtained from a database. The dt:type and sql:datatype annotations control the mapping between SQL Server data types and XML data types. The dt:type Data Typedt:type can be an attribute on <AttributeType> or <ElementType> . It specifies the XML data type of the element or attribute that maps to a column. Not only does dt:type affect the document returned from a query, it also affects the query itself. Specifying a dt:type of int (integer) causes the query to be executed expecting a column of integer data. Because all data returned in an XML document is strings, some conversions to other data types will be required for proper data manipulation. Table 5.7 enumerates the conversion function required for different dt:types . Keep in mind that there are always some conversions that are impossible . Examples include trying to convert a float to an i2 (tinyint) or an "ABC" to a numeric form. Table 5.7. SQL Conversion Functions for XML Data Types
Table 5.8 shows default data type mappings between SQL Server and XML. Table 5.8. Data Type Mappings Between SQL Server and XML
The sql:datatype Data TypeThe SQL Server data types binary, image, and varbinary all use bin.base64 encoding. The sql:datatype annotation specifies exactly which of the data types an XDR schema bin.base64 attribute maps to in a database. When inserting data into a database, the sql:datatype will specify the type of data to store. Valid values are text, ntext, image, and binary. First let's work with an example of dt:type on an attribute in a schema. Listing 5.53 shows a schema in which we've used dt:type in two different places: one on OrdDate and the other on Shipdate . This is followed by the template file in Listing 5.54 and the results in Listing 5.55. Listing 5.53 The Schema Utilizing dt:type<?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"> <ElementType name="Order" sql:relation="Orders"> <AttributeType name="OID" /> <AttributeType name="CustID" /> <AttributeType name="OrdDate" dt:type="date" /> <AttributeType name="ReqDate" /> <AttributeType name="ShipDate" dt:type="time" /> <attribute type="OID" sql:field="OrderID" /> <attribute type="CustID" sql:field="CustomerID" /> <attribute type="OrdDate" sql:field="OrderDate" /> <attribute type="ReqDate" sql:field="RequiredDate" /> <attribute type="ShipDate" sql:field="ShippedDate" /> </ElementType> </Schema> Listing 5.54 The Template File to Test the dt:type Schema<ROOT xmlns:sql="urn:schemas-microsoft-com:xml-sql"> <sql:xpath-query mapping-schema="../schemas/OrderSchema.xml"> /Order </sql:xpath-query> </ROOT> Listing 5.55 Partial Results of Testing dt:type- <ROOT xmlns:sql="urn:schemas-microsoft-com:xml-sql"> <Order OID="10248" CustID="VINET" OrdDate="1996-07-04" ReqDate="1996-08- 01T00:00:00" ShipDate="00:00:00" /> <Order OID="10249" CustID="TOMSP" OrdDate="1996-07-05" ReqDate="1996-08- 16T00:00:00" ShipDate="00:00:00" /> <Order OID="10250" CustID="HANAR" OrdDate="1996-07-08" ReqDate="1996-08- 05T00:00:00" ShipDate="00:00:00" /> ... </ROOT> In this example, dt:type was not applied to the ReqDate , so the full value of the data was returned (both date and time).The OrdDate attribute had the date data type applied, so only the date portion of the data was returned. Finally, ShipDate has the time data type applied, so only the time portion of the data was returned. In the example in Listing 5.56, we use the sql:datatype annotation on the Photo attribute to identify the data type of the Picture column in the Categories table. Listing 5.56 Schema Utilizing sql:datatype <?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"> <ElementType name="Category" sql:relation="Categories"> <AttributeType name="ID" /> <AttributeType name="Name" /> <AttributeType name="Desc" /> <AttributeType name="Photo" /> <attribute type="ID" sql:field="CategoryID" /> <attribute type="Name" sql:field="CategoryName" /> <attribute type="Desc" sql:field="Description" /> <attribute type="Photo" sql:field="Picture" sql:datatype="image" /> </ElementType> </Schema> Listing 5.57 illustrates a template file that utilizes the schema in Listing 5.56. Listing 5.57 The Template to Test the sql:datatype Schema<ROOT xmlns:sql="urn:schemas-microsoft-com:xml-sql"> <sql:xpath-query mapping-schema="../schemas/CategoriesSchema.xml"> /Category[@ID="1"] </sql:xpath-query> </ROOT> The result of testing sql:datatype is as follows: <ROOT xmlns:sql="urn:schemas-microsoft-com:xml-sql"> <Category ID="1" Name="Beverages" Desc="Soft drinks, coffees, teas, beers, and ales" Photo=" Base 64 encoded image here " /> </ROOT>  |
