Requesting URL References to BLOB Data Using sql:url-encode (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).

In the annotated XDR schema, when an attribute (or element) is mapped to a Microsoft SQL Server BLOB column, the data is returned in Base 64-encoded format within XML. For a description of the SQL Server data types and their corresponding XML data types, see Data Type Coercions (XDR Schema).

If you want a reference to the data (URI) to be returned that can be used later to retrieve the BLOB data in a binary format, specify the sql:url-encode annotation.

Specify sql:url-encode annotation to indicate that a URL to the field should be returned instead of the value of the field. sql:url-encode depends on the primary key to generate a singleton select in the URL. The primary key can be specified using sql:key-fields annotation. For more information, see Identifying Key Columns Using sql:key-fields [SQLXML 4.0].

The sql:url-encode annotation takes a Boolean type value (0 = FALSE, 1 = TRUE). sql:url-encode cannot be used with sql:use-cdata or on any of the attribute types ID, IDREF, IDREFS, NMTOKEN, or NMTOKENS.

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:url-encode to obtain a URL reference to BLOB data

In this example, the mapping schema specifies sql:url-encode on the LargePhoto attribute to retrieve the URI reference to the product photo (instead of retrieving the binary data in Base 64-encoded format).

<?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="ProductPhoto" sql:relation="Production.ProductPhoto" 
                 sql:key-fields="ProductPhotoID" >
      <AttributeType name="ProductPhotoID" />
      <AttributeType name="LargePhoto" />

      <attribute type="ProductPhotoID" />
      <attribute type="LargePhoto" sql:url-encode="1" />
   </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 UrlEncodeXdr.xml.

  2. Copy the following template and paste it into a text file. Save the file as UrlEncodeXdrT.xml in the same directory where you saved UrlEncodeXdr.xml.

    <ROOT xmlns:sql="urn:schemas-microsoft-com:xml-sql">
  <sql:xpath-query mapping-schema="UrlEncodeXdr.xml">
    /ProductPhoto[@ProductPhotoID=100]
  </sql:xpath-query>
</ROOT>

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

    mapping-schema="C:\MyDir\UrlEncodeXdr.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">
  <ProductPhoto ProductPhotoID="100" LargePhoto="dbobject/Production.ProductPhoto[@ProductPhotoID="100"]/@LargePhoto"/>
</ROOT>

See Also

Reference

Using XPath Queries in SQLXML 4.0

Help and Information

Getting SQL Server 2005 Assistance