How to validate XML schema in SQL Server

XML integration in SQL Server is always been an added advantage to T-SQL. XML is designed to meet the challenges of large scale electronic data publishing in structured format. It is widely being used for the representation of arbitrary data structures such as in web services. XML can be of any structure, so it’s important to validate it before accepting or creating an XML document in SQL Server. Now the question is how to validate xml schema in sql server ?

XSD (XML Schema Definition), a recommendation of the World Wide Web Consortium (W3C), specifies how to formally describe the elements in an Extensible Markup Language (XML) document. The same can be used to verify each piece of item content or their data type in a XML document. XSD validation is first supported in SQL Server 2005 version and then new enhancements done in later versions of SQL Server.

Compare two XML docuement in SQL Server.

Read XML document in tabular format.

Create an XSD:

If you have a table in SQL Server and want to create or see the XSD of it then that can be done in SQL Server itself.

If you have already an XML document and want to create a XSD then that’s very simple. There are many websites and tools available on internet to create a XSD from a XML document. Mostly I use freeformatter.com to create XSD from a valid xml document. The below is the XSD for books.xml

Know more about XML Schema on W3schools

Create an XML schema collection of the XSD in SQL Server:

xml-schema-collection-sql-serverValidate an XML:

Once the schema collection is created in SQL Server (shown above), you can able to validate the xml docuemnts in SQL Server. If the xml document is matched as per the rules defined in the schema collection then it will give us successful message otherwise error saying the mismatch.

Example 01: (Proper format)

Message: Command(s) completed successfully.

Example 02: (Removed genre element)

Message: Msg 6965, Level 16, State 1, Line 2
XML Validation: Invalid content. Expected element(s): ‘genre’. Found: element ‘price’ instead. Location: /*:catalog[1]/*:book[1]/*:price[1].

The above validation failed because the genre element was missing.

Example 03: (Removed id attribute)

Message: Command(s) completed successfully.

This XML is validated successfully without id because the id attribute is mentioned optional in the XSD. (<xs:attribute type=”xs:string” name=”id” use=”optional“/>)

Example 04: (Changed publish_data datatype)

Message: Msg 6926, Level 16, State 1, Line 2
XML Validation: Invalid simple type value: ‘Oct 01, 2000’. Location: /*:catalog[1]/*:book[1]/*:publish_date[1]

The above validation is failed because the data type of publish_date element did not match with the XSD. (<xs:element type=”xs:date” name=”publish_date”/>)

xml-schema-validation-sql-server

Prasad Sahoo

Prasad Sahoo, is an enthusiast of Microsoft technologies in general and a passionate database professional, mainly focusing on SQL Server performance tuning and business intelligence. He has contributed a long time of his career working on SQL Server and other RDBMS. If he is not busy with SQL stuff then he must be watching some Hollywood movies.

2 thoughts on “How to validate XML schema in SQL Server

  • December 22, 2015 at 7:34 pm
    Permalink

    Your first example is not works
    DECLARE @xml XML(Books) –Schema name

    SELECT @xml = ‘

    Gambardella, Matthew
    XML Developer”s Guide
    Computer
    44.95
    2000-10-01
    An in-depth look at creating applications
    with XML.


    Msg 6926, Level 16, State 1, Line 4
    XML Validation: Invalid simple type value: ‘2000-10-01’. Location: /*:catalog[1]/*:book[1]/*:publish_date[1]

    because timezone is required for SQL server
    2000-10-01Z
    or
    2000-10-01+01:00
    is correct, but
    2000-10-01
    is not correct

    tested on Microsoft SQL Server 2012 (SP2-CU7) (KB3072100) – 11.0.5623.0 (X64)

    Reply
    • December 26, 2015 at 2:13 pm
      Permalink

      the error is due to invalid xml being parsed against the xml schema. In the schema publish_date element is of date datatype and 2000-10-01 is in date neutral format, it should work.

      Reply

Leave a Reply

%d bloggers like this: