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>
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>
See Also
IIS Virtual Directory Management for SQL Server