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
ORDER BY 1, 2
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.
UPDATE XmlTable 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%'
Posted by devio 
Posted by devio 
Posted by devio 