Handling XSD dateTime in SQL Server 2005

A client is running an application which exchanges XML documents with other installations of the same application. The application’s documentation also includes XSD schema definitions.

What an opportunity for me to get to study SQL Server 2005’s XML capabilities.

I open the .xsd files in a text editor, and invoke CREATE XML SCHEMA COLLECTION statements for each xsd file.

Next, I create a table which contains the XML data originally stored as ntext, and an untyped XML column. Additionally, each XSD gets a separate typed XML column:

CREATE TABLE [dbo].[XmlTable](
    [OID] [int] IDENTITY(1,1) NOT NULL,
    [Data] [ntext] NULL,
    [XmlData] [xml] NULL,
    [XsdData1] [xml] (CONTENT [dbo].[Xsd1] NULL

I import data from the production system into the Data column, and copy the data as untyped XML using

UPDATE XmlTable SET XmlData = Data

If every record contains valid XML data, the operation should complete successfully, with XmlData now containing the (untyped) XML documents. Remember that the Data column starts with an XML tag identifying the XSD. We can now start to copy the data to the typed (XSD-based) XML columns:

UPDATE XmlTable SET XsdData1 = XmlData WHERE Data LIKE '<Xsd1%'

which works fine unless you have an xsd:dateTime value in a format that SQL Server cannot process, generating an error message

XML Validation: Invalid simple type value

As this MS feedback page explains, SQL Server 2005 cannot handle datetime values with timezones. An xsd:dateTime value therefore has to end with a ‘Z’.

To make the XML data match SQL Server’s interpretation of the XSD schema information, we need to replace each xsd:dateTime from yyyy-mm-ddThh:mm:ss.sssssss+hh:mm (day, time, second fractions, timezone offset) to yyyy-mm-ddThh:mm:ssZ.

So, first find all XSD columns of type xsd:dateTime (Michael’s post was very inspiring):

SELECT sc.name XSD, sel.name Element
FROM sys.xml_schema_component_placements scp
INNER JOIN sys.xml_schema_types sct 
    ON scp.placed_xml_component_id=sct.xml_component_id
INNER JOIN sys.xml_schema_elements sel 
    ON scp.xml_component_id = sel.xml_component_id
INNER JOIN sys.xml_schema_collections sc 
    ON sel.xml_collection_id = sc.xml_collection_id
WHERE sct.name = 'dateTime' AND sct.xml_namespace_id = 1

The resultset contains the XML Schema Collection and Element names.

Next we take any one of the retrieved elements and check their values in the imported XmlData column using XQuery syntax:

SELECT XmlData.value('(//TimeStamp)[1]', 'nvarchar(50)')
FROM XmlTable

The pattern of date and time requires 19 character.

Using XMLDML, we can now update the timestamp value by taking the first 19 characters and appending ‘Z’ using UPDATE .modify. The result is calculated using XQuery functions.

SET XmlData.modify(
    'replace value of (//TimeStamp/text())[1]
     with concat(substring((//TimeStamp)[1], 1, 19), "Z")')

After this step is repeated for each dateTime XML element, the XML content can be inserted into the typed XML columns, as stated above:

UPDATE XmlTable SET XsdData1 = XmlData WHERE Data LIKE '<Xsd1%'

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.