Creating Valid ID, IDREF, and IDREFS Type Attributes Using sql:id-prefix (XDR Schema)

  • Article

Important

This topic is included as a reference for legacy applications. No future development work will be done on this feature. Avoid using this feature in new development work. Instead, use annotated XSD schemas to create XML views. For more information, see Introduction to Annotated XSD Schemas (SQLXML 4.0). You can convert existing annotated XDR schemas to XSD schemas. For more information, see Converting Annotated XDR Schemas to Equivalent XSD Schemas (SQLXML 4.0).

An attribute can be specified to be an ID type attribute. Attributes specified as IDREF or IDREFS can then be used to refer to the ID type attributes, thus enabling intradocument links.

ID, IDREF, and IDREFS correspond to primary key/foreign key (PK/FK) relationships in the database, with a few differences. In the XML document, the values of ID type attributes must be distinct. If you have CustomerID and SalesOrderID attributes in an XML document, these values must be distinct. However, in a database, CustomerID and SalesOrderID columns can have the same values (for example, CustomerID = 1 and OrderID = 1 are valid in the database).

For the ID, IDREF, and IDREFS attributes to be valid:

  • The value of ID must be unique within the XML document.
  • For every IDREF and IDREFS, the referenced ID values must be in the XML document.
  • The value of an ID, IDREF, and IDREFS must be named token (for example, integer value 101 cannot be an ID value).
  • The attributes of ID, IDREF, and IDREFS type cannot be mapped to columns of type text, ntext, image, or any other binary data type (for example, timestamp).

If an XML document contains multiple IDs, the sql:id-prefix annotation is specified to ensure that the values are unique. . The sql:id-prefix annotation is also used to create named tokens from numbers. The value specified for sql:id-prefix must be a valid name character.

The sql:id-prefix attribute prepends the values of ID, IDREF, and IDREFS with a string, thereby making them unique. No checks are made to ensure the validity of the prefixes and the uniqueness of the values of ID, IDREF, or IDREFS.

The sql:id-prefix attribute is ignored on attributes that are not of type ID, IDREF, or IDREFS.

Note

Each value of the ID, IDREF, and IDREFS attributes is limited to 4,000 characters, including the prefix (if specified).

Examples

To create working samples using the following examples, you must meet certain requirements. For more information, see Requirements for Running SQLXML Examples.

A. Specify sql:id-prefix for an ID type attribute

In this XDR schema, SalesOrderID and CustomerID attributes are declared as ID type. To ensure that the IDs are unique and valid, the sql:id-prefix annotation is specified for these attributes:

<?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="Sales.SalesOrderHeader" sql:key-fields="SalesOrderID">
    <AttributeType name="SalesOrderID" dt:type="id" sql:id-prefix="Ord-" />
    <AttributeType name="OrderDate" />
 
    <attribute type="SalesOrderID" />
    <attribute type="OrderDate" />
  </ElementType>

  <ElementType name="Customer" sql:relation="Sales.Customer">
    <AttributeType name="CustomerID" dt:type="id" />
    <attribute type="CustomerID" />
    <AttributeType name="OrderList" dt:type="idrefs"
                                 sql:id-prefix="Ord-" />
    <attribute type="OrderList" sql:relation="Sales.SalesOrderHeader" sql:field="SalesOrderID">
        <sql:relationship
                key-relation="Sales.Customer"
                key="CustomerID"
                foreign-relation="Sales.SalesOrderHeader"
                foreign-key="CustomerID" />
    </attribute>
    <element type="Order">
        <sql:relationship key-relation="Sales.SalesOrderHeader"
                          key="SalesOrderID"
                          foreign-relation="Sales.Customer"
                          foreign-key="SalesOrderID" />
    </element>
  </ElementType>
</Schema>

To test a sample XPath query against the schema

  1. Copy the schema code above and paste it into a text file. Save the file as sqlPrefix-Xdr.xml.

  2. Copy the following template and paste it into a text file. Save the file as sqlPrefix-XdrT.xml in the same directory where you saved sqlPrefix-Xdr.xml. The XPath query in the template returns the <Customer> and <Order> subelements where CustomerID is 1.

    <ROOT xmlns:sql="urn:schemas-microsoft-com:xml-sql">
  <sql:xpath-query mapping-schema="sqlPrefix-Xdr.xml">
    Customer[@CustomerID="1"]
  </sql:xpath-query>
</ROOT>

    The directory path specified for the mapping schema (sqlPrefix-Xdr.xml) is relative to the directory where the template is saved. An absolute path also can be specified, for example:

    mapping-schema="C:\MyDir\sqlPrefix-Xdr.xml"

  3. Create and use the SQLXML 4.0 Test Script (Sqlxml4test.vbs) to execute the template.

    For more information, see Using ADO to Execute SQLXML 4.0 Queries.

Here is the result set:

<ROOT xmlns:sql="urn:schemas-microsoft-com:xml-sql">
  <Customer CustomerID="1" OrderIDList="Ord-43860 Ord-44501 Ord-45283 Ord-46042">
    <Order SalesOrderID="Ord-43860" OrderDate="2001-08-01T00:00:00" /> 
    <Order SalesOrderID="Ord-44501" OrderDate="2001-11-01T00:00:00" /> 
    <Order SalesOrderID="Ord-45283" OrderDate="2002-02-01T00:00:00" /> 
    <Order SalesOrderID="Ord-46042" OrderDate="2002-05-01T00:00:00" /> 
  </Customer>
</ROOT>

See Also

Reference

Using XPath Queries in SQLXML 4.0

Help and Information

Getting SQL Server 2005 Assistance