How to check for the XML document constraint
During a recent newsgroup exchange, we discussed how to constrain an untyped XML data type to be a document. Kent recapped my statement and Darshan provides an attempt at writing the constraint. Unfortunately, it is not quite correct.
Here is Darshan's statement:
CREATE
TABLE t (
xcol xml
CHECK (xcol.value('count(/*)', 'int')=1)
);
This is not going to catch the invalid document <a/>textnode that is a valid XML data type instance. The general way to constrain the column is the following (Bob recently posted a variety):
CREATE
TABLE t (
xcol xml
CHECK (xcol.value('count(/*)', 'int') = 1 and xcol.exist('/text()')=0)
);
Another interesting alternative is the following (courtesy of John Gallardo):
CREATE
TABLE t (
xcol xml
CHECK (xcol.exist('(/*[position() > 1] , /text())') = 0 and xcol.exist('/*') = 1)
);
I have not tested yet, which of the two expressions will be more efficient.