Paul Ulvinius Blog

From one developer to another

Archive for November, 2007

Issue with HR-XML schema in SQL Server 2005

Posted by paululvinius on November 23, 2007

We are developing a HR-software which stores its data as HR-XML (the Candidate schema) directly in a database table using SQL Server 2005. The XML-column is typed to the schema so that we get schema validation and data type storing optimization. In our tests everything runs fast, especially with the XML-indexes the database provides. The only issue we have encountered is that the XML schema has some string patterns which SQL Server also warns us about:
Warning: Type ‘http://ns.hr-xml.org/2007-04-15:LocalDateType’ is restricted by a facet ‘pattern’ that may impede full round-tripping of instances of this type.

The problem lies in the HR-XML type “LocalDateType”, which isn’t compatible with SQL server 2005 since it forces you to follow the pattern “\d\d\d\d-\d\d-\d\d”. SQL Server 2005 doesn’t allow this because there is no “Z”-postfix decorating the date. In SQL Server 2005, values of type xs:datetime, xs:date, and xs:time must be specified in ISO 8601 format and include a time zone (http://msdn2.microsoft.com/en-us/library/ms345115.aspx, chapter: “Using xs:datetime, xs:date, and xs:time”).
E.g. the element:

“Candidate/CandidateProfile/PersonalData/PersonDescriptors/BiologicalDescriptors/DateOfBirth” is defined with the type LocalDateNkNaType which in turn refers to the types “LocalDateType NotKnownLiteral NotApplicableLiteral”. The only usable choice here is obviously LocalDateType, which is not supported by SQL Server 2005. We chose to store DateOfBirth somewhere else, in favor of altering the schema. Its tempting to just add that ā€˜Zā€™ in the HR-XML schema, but being 99.99% compatible is just annoying.

Posted in Uncategorized | Leave a Comment »