Data Type Coercions (XDR Schema)
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).
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 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 (SQLXML 4.0).
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 sql:datatype annotation supports all SQL Server built-in data types. (User-defined data types or synonyms are not supported.) Precision and scale are also supported, for example, sql:datatype="nvarchar"and sql:datatype="nchar(10)".
Specifying XDR and SQL Server data types in the mapping schema can help eliminate unnecessary data conversions in SQL queries. For example, XPath must convert from the SQL Server data type to the XDR data type, and then from the XDR type to the XPath type. When the sql:datatype or XDR type is specified and XPath determines that the conversion is unnecessary, XPath does not do it.
In the case of primary key columns, the elimination of these conversions can result in a significant performance improvement. For example, the following query usually requires a conversion of CustomerID to string (nvarchar) to guarantee correct results:
Customer[@CustomerID='ALFKI']
If CustomerID is annotated in the schema with sql:datatype="nvarchar", XPath can avoid the unnecessary data conversion.
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 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 the OrdDate attribute. XPath returns only the date part of the values (and no time) retrieved from the 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 the 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>
To test a sample XPath query against the schema
Copy the schema code above and paste it into a text file. Save the file as dataTypeXdr.xml.
Copy the following template and paste it into a text file. Save the file as dataTypeXdrT.xml in the same directory where you saved dataTypeXdr.xml.
<ROOT xmlns:sql="urn:schemas-microsoft-com:xml-sql"> <sql:xpath-query mapping-schema="dataTypeXdr.xml"> /Order[@OID=43860] </sql:xpath-query> </ROOT>This URL executes the template:
http://IISServer/AdventureWorks/template/dataTypeXdrT.xml
Here is the result set:
<ROOT xmlns:sql="urn:schemas-microsoft-com:xml-sql">
<Order OID="43860" CustID="1" OrdDate="2001-08-01" ReqDate="2001-08-13T00: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 LargePhoto 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="ProductPhoto" sql:relation="Production.ProductPhoto">
<AttributeType name="PhotoID" />
<AttributeType name="filename" />
<AttributeType name="photo" sql:datatype="image" />
<attribute type="PhotoID" sql:field="ProductPhotoID" />
<attribute type="filename" sql:field="LargePhotoFileName" />
<attribute type="photo" sql:field="LargePhoto" />
</ElementType>
</Schema>
To test a sample XPath query against the schema
Copy the schema code above and paste it into a text file. Save the file as DataTypeXdr2.xml.
Copy the following template and paste it into a text file. Save the file as DataTypeXdr2T.xml in the same directory where you saved DataTypeXdr2.xml.
<ROOT xmlns:sql="urn:schemas-microsoft-com:xml-sql"> <sql:xpath-query mapping-schema="DataTypeXdr2.xml"> /ProductPhoto[@PhotoID="100"] </sql:xpath-query> </ROOT>The directory path specified for the mapping schema (DataTypeXdr2.xml) is relative to the directory where the template is saved. An absolute path also can be specified, for example:
mapping-schema="C:\MyDir\DataTypeXdr2.xml"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 PhotoID="100" filename="racer02_red_large.gif"
photo="Binary_base64_image_returned_here"/>
</ROOT>
See Also
Reference
Using XPath Queries in SQLXML 4.0