Edit

Share via

Data Type conversions and the sql:datatype Annotation (SQLXML 4.0)

  • Article

Applies to: SQL Server Azure SQL Database

In an XSD schema, the xsd:type attribute specifies the XSD data type of an element or attribute. When an XSD schema is used to extract data from the database, the data type specified is used to format the data.

In addition to specifying an XSD type in a schema, you can also specify a Microsoft SQL Server data type by using the sql:datatype annotation. The xsd:type and sql:datatype attributes control the mapping between XSD data types and SQL Server data types.

xsd:type Attribute

You can use the xsd:type attribute to specify the XML data type of an attribute or element that maps to a column. The xsd:type affects the document that is returned from the server and also the XPath query that is executed. When an XPath query is executed against a mapping schema that contains xsd:type, XPath uses the specified data type when it processes the query. For more information about how XPath uses xsd:type, see Mapping XSD Data Types to XPath Data Types (SQLXML 4.0).

In a returned document, 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 xsd:type values.

XSD data type SQL Server conversion
Boolean CONVERT(bit, COLUMN)
Date LEFT(CONVERT(nvarchar(4000), COLUMN, 126), 10)
decimal CONVERT(money, COLUMN)
id/idref/idrefs id-prefix + CONVERT(nvarchar(4000), COLUMN, 126)
nmtoken/nmtokens id-prefix + CONVERT(nvarchar(4000), COLUMN, 126)
Time SUBSTRING(CONVERT(nvarchar(4000), COLUMN, 126), 1+CHARINDEX(N'T', CONVERT(nvarchar(4000), COLUMN, 126)), 24)
All others No additional conversion

Note

Some of the values returned by SQL Server might not be compatible with the XML data types that are specified by using xsd:type, either because the conversion is not possible (for example, converting "XYZ" to a decimal data type) or because the value exceeds the range of that data type (for example, -100000 converted to an UnsignedShort XSD type). Incompatible type conversions might result in XML documents that are not valid, or in SQL Server errors.

Mapping from SQL Server Data Types to XSD Data Types

The following table shows an obvious mapping from SQL Server data types to XSD data types. If you know the SQL Server type, this table provides the corresponding XSD type that you can specify in the XSD schema.

SQL Server data type XSD data type
bigint long
binary base64Binary
bit boolean
char string
datetime dateTime
decimal decimal
float double
image base64Binary
int int
money decimal
nchar string
ntext string
nvarchar string
numeric decimal
real float
smalldatetime dateTime
smallint short
smallmoney decimal
sql_variant string
sysname string
text string
timestamp dateTime
tinyint unsignedByte
varbinary base64Binary
varchar string
uniqueidentifier string

sql:datatype Annotation

The sql:datatype annotation is used to specify the SQL Server data type; this annotation must be specified when:

  • You are bulk loading into a dateTimeSQL Server column from an XSD dateTime, date, or time type. In this case, you must identify the SQL Server column data type by using sql:datatype="dateTime". This rule also applies to updategrams.

  • You are bulk loading into a column of SQL Server uniqueidentifier type and the XSD value is a GUID that includes braces ({ and }). When you specify sql:datatype="uniqueidentifier", the braces are removed from the value before it is inserted in the column. If sql:datatype is not specified, the value is sent with the braces, and the insert or update fails.

  • The XML data type base64Binary maps to various SQL Server data types (binary, image, or varbinary). To map the XML data type base64Binary to a specific SQL Server data type, use the sql:datatype annotation. This annotation specifies the explicit SQL Server data type of the column to which the attribute maps. This is useful when data is being stored in the databases. By specifying the sql:datatype annotation, you can identify the explicit SQL Server data type.

It is generally recommended that you specify sql:datatype in the schema.

Examples

To create working samples using the following examples, you must meet certain requirements. For more information, see Requirements for Running SQLXML Examples.

A. Specifying xsd:type

This example shows how an XSD date type that is specified by using the xsd:type attribute in the schema affects the resulting XML document. The schema provides an XML view of the Sales.SalesOrderHeader table in the AdventureWorks database.

<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"   
            xmlns:sql="urn:schemas-microsoft-com:mapping-schema">  
  <xsd:element name="Order" sql:relation="Sales.SalesOrderHeader">  
     <xsd:complexType>  
       <xsd:attribute name="SalesOrderID" type="xsd:string" />   
       <xsd:attribute name="CustomerID"   type="xsd:string" />   
       <xsd:attribute name="OrderDate"    type="xsd:date" />   
       <xsd:attribute name="DueDate"  />   
       <xsd:attribute name="ShipDate"  type="xsd:time" />   
     </xsd:complexType>  
  </xsd:element>  
</xsd:schema>

In this XSD schema, there are three attributes that return a date value from SQL Server. When the schema:

  • Specifies xsd:type=date on the OrderDate attribute, the date part of the value returned by SQL Server for the OrderDate attribute is displayed.

  • Specifies xsd:type=time on the ShipDate attribute, the time part of the value that is returned by SQL Server for the ShipDate attribute is displayed.

  • Does not specify xsd:type on the DueDate attribute, the same value that is returned by SQL Server is displayed.

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 xsdType.xml.

  2. Copy the following template and paste it into a text file. Save the file as xsdTypeT.xml in the same directory where you saved xsdType.xml.

    <ROOT xmlns:sql="urn:schemas-microsoft-com:xml-sql">  
  <sql:xpath-query mapping-schema="xsdType.xml">  
    /Order  
  </sql:xpath-query>  
</ROOT>

    The directory path specified for the mapping schema (xsdType.xml) is relative to the directory where the template is saved. An absolute path also can be specified, for example:

    mapping-schema="C:\SqlXmlTest\xsdType.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 partial result set:

<ROOT xmlns:sql="urn:schemas-microsoft-com:xml-sql">  
  <Order SalesOrderID="43659"   
         CustomerID="676"   
         OrderDate="2001-07-01"   
         DueDate="2001-07-13T00:00:00"   
         ShipDate="00:00:00" />   
  <Order SalesOrderID="43660"   
         CustomerID="117"   
         OrderDate="2001-07-01"   
         DueDate="2001-07-13T00:00:00"   
         ShipDate="00:00:00" />   
 ...  
</ROOT>

This is the equivalent XDR schema:

<?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="Sales.SalesOrderHeader">  
    <AttributeType name="SalesOrderID" />  
    <AttributeType name="CustomerID"  />  
    <AttributeType name="OrderDate" dt:type="date" />  
    <AttributeType name="DueDate" />  
    <AttributeType name="ShipDate" dt:type="time" />  
  
    <attribute type="SalesOrderID" sql:field="OrderID" />  
    <attribute type="CustomerID" sql:field="CustomerID" />  
    <attribute type="OrderDate" sql:field="OrderDate" />  
    <attribute type="DueDate" sql:field="DueDate" />  
    <attribute type="ShipDate" sql:field="ShipDate" />  
</ElementType>  
</Schema>

B. Specifying SQL data type using sql:datatype

For a working sample, see Example G in XML Bulk Load Examples (SQLXML 4.0). In this example, a GUID value including "{" and "}" is bulk loaded. The schema in this example specifies sql:datatype to identify the SQL Server data type as uniqueidentifier. This example illustrate when sql:datatype must be specified in the schema.