Complementing XSD with CHECK constraints

I was recently asked to look at a customer’s question. This person wanted to write a schema that would validate instances like the following one.

<TopElement>

  <a>

    <element1 att1="data1" att2="data2"/>

    <an_element att1="data 1" att2="data 2"/>

    <something att1="abc" att2="def"/>

  </a>

</TopElement>

The customer had two requirements for the children of element ‘a’.

1. They should be allowed to have any valid unqualified name.

2. They are required to contain both attributes att1 and att2.

The first requirement can easily be taken care of using element wildcards but I couldn’t come up with a way to define the second constraint in an XML schema.

There are constraints like this one that are not expressible (or not easily expressible) using XSD but that are easy to check using a simple XQuery. The usual workaround is to use a T-SQL CHECK constraint.

First, let’s create a schema collection

CREATE XML SCHEMA COLLECTION SC AS '

<schema xmlns="https://www.w3.org/2001/XMLSchema">

      <element name="TopElement">

            <complexType>

                  <sequence>

                        <element name="a">

            <complexType>

                                    <sequence>

                                          <any processContents="skip" namespace="##local" minOccurs="0" maxOccurs="unbounded"/>

                                    </sequence>

                              </complexType>

                        </element>

                  </sequence>

            </complexType>

      </element>

</schema>

'

go

If we type an XML column with this collection, we will accept all unqualified elements as children of ‘a’, whether they contain the required attributes or not. In order to implement this second requirement, we are going to use a CHECK constraint.

Since CHECK constraints don’t allow calls to XML data type methods, we need to create a user-defined function.

CREATE FUNCTION dbo.checkAttrs(@x XML(SC))

RETURNS bit

AS

BEGIN

      RETURN ~(@x.exist('/TopElement/a/*[not( ./@att1 and ./@att2 )]'))

END

go

The function defined above checks for the presence of a child element of ‘a’ that doesn’t contain both attributes att1 and att2. If such an element exists, the function returns 0. If none is found, the function returns 1.

With this function, we can now create a table with an XML column that behaves according to the user’s specifications.

CREATE TABLE T(xmlCol XML(SC)

CHECK (1 = dbo.checkAttrs(xmlCol)))

go

When an XML instance is inserted into or modified inside table T it is first validated against the schemas present in the ‘SC’ schema collection. Then the server verifies that CHECK constraint holds true. If the instance doesn’t validate against the schema, or if it violates the check constraint, it is rejected.

In conclusion this short example shows how you can harness the power of XQuery through the T-SQL CHECK syntax in order to implement constraints that cannot be defined with XSD alone.

-
Disclaimer:
This posting is provided “AS IS” with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at https://www.microsoft.com/info/cpyright.htm.