XML Workshop VII - Validating values with SCHEMA

  • Comments posted here are about the content posted at http://www.sqlservercentral.com/columnists/jSebastian/3120.asp


  • In the article you had written that we need to give time zone information while creating xml for a date or time field. In this case when we use xpath query to get the value of a date field via following query I am getting a error message.

       x.item.value('Employee\@ReportingTime[1]', 'Datetime') AS ReportingTime

       Let say ReportingTime as attribute for a Employee element.


    That message is because I am casting a value like '1975-03-14+05:30' in Datetime in Sql Server and sql server donot store timezone information in the field itself.


    I have only option to use string datatype in schema


    Is there any other method ....


  • dinesh,

    I did not notice your message until this morning (when i get into this page accidently), because this article is not yet published. It is scheduled and will be out soon.

    You need to apply an 'XQuery' conversion before the value can be converted to a valid SQL Server datetime value. Use the function "xs:dateTime()" or "xs:date()" for the conversion. Here is an example.


    @emp AS XML

    SET @emp = '

    <Employee EmployeeNumber="1001" Language="EN" >






    <ReportingTime />




    x.e.value('@EmployeeNumber[1]','varchar(20)') as EmpNumber,

    x.e.value('FullName[1]','varchar(40)') as FullName,

    x.e.value('xs:dateTime(BirthDate[1])', 'datetime') as BirthDate


    @emp.nodes('Employee') x(e)


  • Hello Jacob,

    You gave really useful information in easy way with example. Can you post the way to deal with Enumeration (for example weekdays Sunday, Monday,... Saturday) in XML Schema validation?

    Thank you once again for your articles.

  • Do you have any idea why there is a requirement for timezone on date and time fields?

    According to W3C timezone is optional on date, time and datetime fields.



  • Hi Herit,

    There is an article in this series which explains the enumeration in detail.

    I am not sure what is the scheduled date. But I guess it will be out in a week.




  • using the article example.

    this is empty xml (assuming it's the content of the file is coming from outside, meaning the content was read in an .xml file

    DECLARE @emp AS XML(EmployeeSchema)

    SET @emp = ''

    this is empty xml (assuming it's the content of the file is coming from outside, meaning the content was read in an .xml file

    DECLARE @emp AS XML(EmployeeSchema)

    SET @emp = 'put a valid xml comment here'

    why to two last statement is valid??? why an empty xml or an xml with valid xml comment cannot be validated by predefined schema.

    is there a way to validated an empty xml or an xml file which is the only content is a valid xml comments?

    please help.

  • XML data type can store XML DOCUMENTS (having exactly 1 root element) and XML CONTENT (having 0 or more top level elements). The default is CONTENT and hence it allows 0 or more top level elements. The schema performs a validation only if the element is present.

    Declare your XML variable as DOCUMENT and then SQL Sever will perform the validation that you expect. For example:

    DECLARE @x XML(DOCUMENT EmployeeSchema)


  • great thanks for the info. i have another problem. i'm trying to use a xsd validation again with regular expression, i got the regex from regexlib and it's great when i'm testing the xml using xsd validation tool. but when i try to create xsd collection in sql i got an error.

    Msg 102, Level 15, State 1, Line 32

    Incorrect syntax near 'year'.

    xsd collection attached

  • The regular expression language is documented here: http://www.w3.org/TR/xmlschema-2/#regexs

    The documentation is not very easy to understand but you might be able to spot the problem with your syntax.

    I have included a detailed RegularExpression tutorial in my XSD book which will be out in a few days. Keep a watch at http://www.sqlservercentral.com/articles/books/65843/


  • Hi Jacob,

    I am trying to Raise an error using RAISEERROR function in sql when the XML schema validation fails.

    I am doing this inside a store proc. I am declaring the XML doc binding it to it's XSD schema in the TRY block and if validation fails, raise a user define error number by using RAISEERROR function in the catch block. However, somehow i am not able to do this and sql return it's own error the moment it executes that line of code. So using Employee example here, I am doing something like this.

    CREATE PROC ImportEmployeeInfo

    @EmployeeXML as XML




    DECLARE @EmpXML AS XML(EmployeeSchema) --declaring a local XML variable and binding it to a schema

    DECLARE @LocalError

    SET @EmpXML = @EmployeeXML -- here I am setting the @EmpXML to @EmployeeXML variable passed in

    IF @@error<>0 --if the @EmployeeXML failed the XSD validation, i beileve there would be an error right


    SET @LocalError = 50001 --user defined error

    RaiseError(' The input parameter @EmployeeXML is not valid', 16, 1)


    --The XML shredding and import into table goes here



    --I have another another user defined errorhandler proc here which will take the above @LocalError

    --as input parameter and raise a detailed error


    END --End of Proc

  • Here is an example that I just tried and it works

    DECLARE @x XML(CustomerSchema), @y XML

    SELECT @y = 'a'


    SELECT @x = @y



    RAISERROR('Hey, this is an invalid XML',16,1)




    Msg 50000, Level 16, State 1, Line 7

    Hey, this is an invalid XML



  • hi, can any one please let me know how to get multiple error messages while validating xml using xsd?

    no CLR or any dlls.

    thank you so much in advance.

  • Is there any way to get all error messages validating xml using xsd?

    no CLR or any dlls.

Viewing 14 posts - 1 through 13 (of 13 total)

You must be logged in to reply to this topic. Login to reply