Condividi tramite

Data Type Coercions (XDR Schema)

  • Articolo
ms172725.note(it-it,SQL.90).gifImportante:
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

  1. Copy the schema code above and paste it into a text file. Save the file as dataTypeXdr.xml.

  2. 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>

  3. 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

  1. Copy the schema code above and paste it into a text file. Save the file as DataTypeXdr2.xml.

  2. 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"

  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.

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>

Vedere anche

Riferimento

Using XPath Queries in SQLXML 4.0

Guida in linea e informazioni

Assistenza su SQL Server 2005