Requesting URL References to BLOB Data Using sql:url-encode

XML and Internet Support

XML and Internet Support

Requesting URL References to BLOB Data Using sql:url-encode

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.

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.

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 these examples, you must create the nwind virtual directory (to access the Northwind database) and a virtual name of template type. For more information about creating the nwind virtual directory, see Creating the nwind Virtual Directory.

In creating working samples in each example, templates are used to specify XPath queries against the mapping XDR schema. There are different ways of using annotated XDR schemas in queries, for example, inline schemas and schemas in the URL. For more information, see Using Annotated XDR Schemas in Queries.

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 Photo attribute to retrieve the URI reference to the employee 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="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>

Testing a sample XPath query against the schema

  1. Save the schema (MySchema.xml) in the directory associated with the template virtual name that you have already created (or one of its subdirectories, in which case you must specify the relative path in the mapping-schema attribute).

  2. Create this template (MySchemaT.xml) and save it in the directory associated with the template virtual name:
    <ROOT xmlns:sql="urn:schemas-microsoft-com:xml-sql">
      <sql:xpath-query mapping-schema="MySchema.xml">
        /Employee[@EmployeeID=1]
      </sql:xpath-query>
    </ROOT>
    
  3. This URL executes the template:
    http://IISServer/VirtualRoot/template/MySchemaT.xml
    

Here is the result:

<ROOT xmlns:sql="urn:schemas-microsoft-com:xml-sql"> 
    <Employee EmployeeID="1"
              Photo="dbobject/Employees[@EmployeeID="1"]/@Photo" /> 
</ROOT>

See Also

IIS Virtual Directory Management for SQL Server

Using XPath Queries

Accessing SQL Server Using HTTP

Executing Template Files Using HTTP