Data Type Coercions

XML and Internet Support

previous page next page
XML and Internet Support

Data Type Coercions

The data type of an element or an attribute can be specified in an XDR schema. When an XDR schema is used to extract data from the database, the appropriate data format is output as a result of a query. The dt:type and sql:datatype annotations are used to control the mapping between XDR data types and Microsoft® SQL Server™ 2000 data types.

dt:type

You can use the dt:type attribute to specify the XML data type of an attribute or element that maps to a column. The dt:type attribute can be specified on <AttributeType> or <ElementType>. The dt:type affects the document returned from the server and also the XPath query executed. When an XPath query is executed against a mapping schema containing dt:type, XPath uses the data type indicated when processing the query. For more information about how XPath uses dt:type, see XPath Data Types.

In a document returned, all SQL Server data types are converted into string representations. Some data types require additional conversions. The following table lists the conversions that are used for various dt:type values.

XML data type SQL Server conversion
bit CONVERT(bit, COLUMN)
date LEFT(CONVERT(nvarchar(4000), COLUMN, 126), 10)
fixed.14.4 CONVERT(money, COLUMN)
id/idref/idrefs id-prefix + CONVERT(nvarchar(4000), COLUMN, 126)
nmtoken/nmtokens id-prefix + CONVERT(nvarchar(4000), COLUMN, 126)
time/time.tz SUBSTRING(CONVERT(nvarchar(4000), COLUMN, 126), 1+CHARINDEX(N'T', CONVERT(nvarchar(4000), COLUMN, 126)), 24)
All others No additional conversion

Note that some SQL Server values cannot be converted to some XML data types, either because the conversion is not possible (for example, "XYZ" to a number data type) or because the value exceeds the range of that data type (for example, -100000 converted to ui2). Incompatible type conversions may result in invalid XML documents or SQL Server errors.

Mapping from SQL Server Data Types to XML Data Types

The table shows a natural mapping from SQL Server data types to XML data types.

SQL Server data type XML data type
bigint i8
binary bin.base64
bit boolean
char char
datetime datetime
decimal r8
float r8
image bin.base64
int int
money r8
nchar string
ntext string
nvarchar string
numeric r8
real r4
smalldatetime datetime
smallint i2
smallmoney fixed.14.4
sysname string
text string
timestamp ui8
tinyint ui1
varbinary bin.base64
varchar string
uniqueidentifier uuid

sql:datatype

The XML data type bin.base64 maps to various Microsoft® SQL Server™ data types (binary, image, varbinary). To clearly map the XML data type bin.base64 to a specific SQL Server data, the sql:datatype annotation is used. sql:datatype specifies the SQL Server data type of the column to which the attribute maps.

This is useful when data is being stored in the database. By specifying the sql:datatype annotation, you can identify the explicit SQL Server data type. The data item is then stored as the type specified in sql:datatype.

The valid values for sql:datatype are text, ntext, image, and binary).

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 dt:type on an attribute

In this XDR schema, dt:type is specified on the OrdDate and ShipDate attributes.

For the ReqDate attribute, no XPath data type is specified. Therefore, XPath returns the SQL Server datetime values retrieved from the RequiredDate column in the database.

The date XPath data type is specified on OrdDate attribute. XPath returns only the date part of the values (and no time) retrieved from OrderDate column.

The time XPath data type is specified on ShipDate attribute. XPath returns only the time part of the values (and no date) retrieved from ShippedDate column. 

<?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>

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">
    /Order
  </sql:xpath-query>
</ROOT>
  3. This URL executes the template: 
    http://IISServer/VirtualRoot/template/MySchemaT.xml

Here is the partial result set:

<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" /> 
   ...
</ROOT>
B. Specify sql:datatype on an attribute

In this example, sql:datatype is used to identify the SQL Server data type of the Photo column.

<?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">
    <AttributeType name="EID" />
    <AttributeType name="fname" />
    <AttributeType name="lname" />
    <AttributeType name="photo"  />

    <attribute type="EID" sql:field="EmployeeID" />
    <attribute type="fname" sql:field="FirstName" />
    <attribute type="lname" sql:field="LastName" />
    <attribute type="photo" sql:field="Photo" sql:datatype="image" />
</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 (Schema19T.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[@EID="1"]
  </sql:xpath-query>
</ROOT>
  3. This URL executes the template: 
    http://IISServer/VirtualRoot/template/MySchemaT.xml

Here is the partial result set:

<ROOT xmlns:sql="urn:schemas-microsoft-com:xml-sql"> 
    <Employee EID="1" fname="Nancy" lname="Davolio" 
              photo="Binary base 64 image returned here"/> 
</ROOT>

See Also

IIS Virtual Directory Management for SQL Server

Using XPath Queries

Accessing SQL Server Using HTTP

Executing Template Files Using HTTP

previous page start next page