Requesting URL References to BLOB Data Using sql:encode [SQLXML 4.0]

In an annotated XSD schema, when an attribute (or element) is mapped to a BLOB column in Microsoft SQL Server, the data is returned in Base 64-encoded format within XML.

If you want a reference to the data (a URI) to be returned that can be used later to retrieve the BLOB data in a binary format, specify the sql:encode annotation. You can specify sql:encode on an attribute or element of simple type.

Specify the sql:encode annotation to indicate that a URL to the field should be returned instead of the value of the field. sql:encode depends on the primary key to generate a singleton select in the URL. The primary key can be specified using the sql:key-fields annotation.

The sql:encode annotation can be assigned the "url" or the "default" value. A value of "default" returns data in Base 64-encoded format.

The sql:encode annotation cannot be used with sql:use-cdata or on the ID, IDREF, IDREFS, NMTOKEN, or NMTOKENS attribute types. It can also not be used with XSD fixed attribute.

Examples

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

A. Specifying sql:encode to obtain a URL reference to BLOB data

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

<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"
            xmlns:sql="urn:schemas-microsoft-com:mapping-schema">

  <xsd:element name="ProductPhoto" sql:relation="Production.ProductPhoto" 
               sql:key-fields="ProductPhotoID" >
   <xsd:complexType>
      <xsd:attribute name="ProductPhotoID"  type="xsd:int"  />
     <xsd:attribute name="LargePhoto" type="xsd:string" sql:encode="url" />
    </xsd:complexType>
  </xsd:element>
</xsd: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 sqlEncode.xml.

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

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

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

   mapping-schema="C:\SqlXmlTest\sqlEncode.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.

This is the result:

<ROOT xmlns:sql="urn:schemas-microsoft-com:xml-sql">
   <ProductPhoto ProductPhotoID="100"
                 LargePhoto="dbobject/Production.ProductPhoto[@ProductPhotoID="100"]/@LargePhoto" /> 
</ROOT>