Validating without typing

You might encounter a situation where you want to validate incoming XML instances against the contents of an xml schema collection, but you still want to store your XML data as untyped.

We're going to look at a very simple way to do this. All you have to do is add a CHECK constraint to your table where your XML instance is converted into typed XML. Here is an example

 

CREATE XML SCHEMA COLLECTION SC_test AS '
<schema xmlns="https://www.w3.org/2001/XMLSchema">
<element name="e" type="byte"/>
</schema>'
go 

 

CREATE TABLE T1 (xmlCol XML,
CHECK (datalength(CONVERT(XML(dbo.SC_test), xmlCol)) > -1))
go

Please note, I used datalength() to make up a dummy test that would always return true provided the call to CONVERT succeeds. I am not a T-SQL expert so if you know of a more elegant way to do this please let me know.

 

So given the schema collection SC_test and the table T1 defined above, we can try to insert various values into the table.

 

INSERT INTO T1 VALUES (null)
INSERT INTO T1 VALUES ('<e>1</e>')
INSERT INTO T1 VALUES ('')
go

All 3 INSERTs above will succeed.

 

The following however will fail because “a1” is not a valid byte value.
insert into T1 VALUES ('<e>a1</e>')

 

Another interesting thing to note is that you can also use the “document” facet which is normally unavailable for untyped XML (if you do not know about this feature please check this previous post)

 

CREATE TABLE T2 (xmlCol XML,
CHECK (datalength(CONVERT(XML(DOCUMENT dbo.SC_test), xmlCol)) > -1))
go

 

Now let’s try to insert into T2 the same values we tried to insert into T1.

 

The following 2 statements will succeed

INSERT INTO T2 VALUES (null)
INSERT INTO T2 VALUES ('<e>1</e>')
go

 

This time, the following statement will fail because the value is not a valid XML document

INSERT INTO T2 VALUES ('')
go

 

And finally the statement below will fail once again because of the invalid byte value

INSERT INTO T2 VALUES ('<e>a1</e>')
go

This way, you can make sure that all XML instances that aren't valid XML documents will be rejected, even though your column was declared as plain vanilla untyped XML.

-
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.