SQL SERVER – Validate an XML Document in TSQL using XSD by Jacob Sebastian

December 2, 2009

Let us learn about XML Document in TSQL using XSD by Jacob Sebastian.

XML Schema Collections

Because of the ‘eXtensible’ nature of XML (eXtensible Markup Language), often there is a requirement to restrict and validate the content of an XML document to a pre-defined structure and values. XSD (XML Schema Definition Language) is the W3C recommended language for describing and validating XML documents. SQL Server implements XSD as XML Schema Collections.

An XML Schema Collection can be used to validate an XML document stored in an XML column or variable. To understand this better, let us see an example.

XML Document

For the purpose of this example, let us create an XML Schema Collection to validate the following XML Document.

<Employee>
    <FirstName>Jacob</FirstName>
    <MiddleName>V</MiddleName>
    <LastName>Sebastian</LastName>
</Employee>

Validation Rules

Here are the validation rules we are trying to implement.

The root element should be named ‘Employee’
The root element should have three child elements, named ‘FirstName’, ‘MiddleName’ and ‘LastName’.
Child elements should appear exactly in the order given above.

Creating the XML Schema Collection

Here is the XML Schema collection that performs the above validations.

CREATE XML SCHEMA COLLECTION EmployeeSchema
    AS'<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema">
    <xsd:element >
        <xsd:complexType>
            <xsd:sequence>
                <xsd:element />
                <xsd:element />
                <xsd:element />
            </xsd:sequence>
        </xsd:complexType>
    </xsd:element>
</xsd:schema>'

Validating an XML variable

The following code validates an XML document against the rules defined in the XML Schema Collection.

DECLARE @x XML(EmployeeSchema)
SELECT @x = '<Employee>
    <FirstName>Jacob</FirstName>
    <MiddleName>V</MiddleName>
    <LastName>Sebastian</LastName>
</Employee>'

Note that the operation will succeed only if all the validation rules defined in the XML schema collection are met. For example, the following will fail because the XML document is missing the ‘MiddleName’ element.

DECLARE @x XML(EmployeeSchema)
SELECT @x = '<Employee>
    <FirstName>Jacob</FirstName>
    <LastName>Sebastian</LastName>
</Employee>'

Reference: Pinal Dave (https://blog.sqlauthority.com)

SQL Scripts, SQL Server, SQL XML

SQLAuthority News – A Daily Doze of Technology – Alvin Ashcraft’s Morning Dew

SQLAuthority News – Airline Review – Paramount, Kingfisher, Go Air, Indigo, Jet Airways, Indian Airlines, Spicejet

9 Comments. Leave new

Ganesh
December 2, 2009 10:14 am

Simple and perfect example, for any newbie to understand. Great thanks to sebastian and pinal for this lucid explanation.

Reply
Dan
December 2, 2009 6:27 pm

How does the schema shown above enforce having elements named FirstName, MiddleName, LastName?

Reply
ramdas
December 2, 2009 8:32 pm

Hi Pinal,
When i ran the CREATE XML SCHEMA script, i got this error:
Msg 2299, Level 16, State 1, Line 1
Required attribute “name” of XSD element “element” is missing.

Any thoughts on this.

Thank you

Reply
ramdas
December 3, 2009 1:45 am

Hi Pinal,
When i modified the CREATE XML SCHEMA COLLECTION to include the element name it worked:
CREATE XML SCHEMA COLLECTION EmployeeSchema AS'<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"> <xsd:element name = "Employee" > <xsd:complexType> <xsd:sequence> <xsd:element name = "FirstName" /> <xsd:element name = "MiddleName"/> <xsd:element name = "LastName" /> </xsd:sequence> </xsd:complexType> </xsd:element> </xsd:schema>'

Reply
Jerry Scannell
April 2, 2010 11:15 pm

I’ve tried everything I can to create an XML SCHEMA in SQL Server 2008.

Maybe someone can help. Here’s the XML that I need a schema for:

e4933d5d-7452-4fad-b557-125d700da000

Here is my original XSD SCHEMA that successfully ‘compiled’ into a XML SCHEMA:

CREATE XML SCHEMA COLLECTION [dbo].[GUIDSchema]
AS
N’

‘
GO

Here is the function that makes use of the schema:
ALTER FUNCTION [dbo].[fnGUID_from_XML] ( @inputXML XML )
RETURNS uniqueidentifier
AS
BEGIN
DECLARE @ValidateXML as XML(GUIDSchema)
SET @ValidateXML = @inputXML

DECLARE @GUID as uniqueidentifier
DECLARE @PL_FILE TABLE (TempGUID uniqueidentifier )

INSERT INTO @PL_FILE (TempGUID)
SELECT ParamValues.Items.value(‘GUID[1]’,’uniqueidentifier’)
FROM @ValidateXML.nodes(‘/NODE’) as ParamValues(Items)

select @GUID = TempGUID from @PL_FILE
RETURN @GUID
END

However, when I try to compile the above function, I get this error:
Msg 9314, Level 16, State 1, Procedure fnGUID_from_XML, Line 18
XQuery [value()]: Cannot implicitly atomize or apply ‘fn:data()’ to complex content elements, found type ‘xs:anyType’ within inferred type ‘element(GUID,xs:anyType) ?’.

I have tried removing the various tags from the schema, but can never compile the schema again. The only code that creates a successful schema compilation is what I’ve provided here.

Thanks in advance,
Jerry

Reply
poorna
July 25, 2011 7:19 pm

Hi,
Im poorna. Can any one please let me know how to get alla error messages at once while validating xml with xsd in sql2008?
thank a lot in advance.

Reply
NickyD
August 11, 2011 1:19 am

Very concise and useful. Thanks Pinal and Jacob.

Reply
yobikwhatus
February 17, 2012 11:02 am

Do you have a way to generate SQL tables from XML Schema Collections?

Reply
Anirudh Sriram
June 21, 2012 9:56 am

Brilliant! Thanks a tonne!

Reply