SQL Server won't perform regular expression validation on XML column

Question

Hi

I have an XML column in my table which contains this xsd snippet:

<xsd:element name="Postcode" minOccurs="0">
    <xsd:simpleType>
        <xsd:restriction base="xsd:string">
            <xsd:pattern value="^[0-9]{4}$" />
        </xsd:restriction>
    </xsd:simpleType>
</xsd:element>

The regular expression should require a string containing 4 numerical digits. It validates perfectly in Visual Studio and is a correct regular expression.

SQL Server, on the other hand, won't accept it. The error message I receive is:

XML Validation: Invalid simple type value: '1234'. Location: / * : Donor[1]/*:Postcode[1].

I have an email address regex working fine, but can't get this simple numerical regex to work.

Answer 1

Does your source XML look like this:

<Postcode>1234</Postcode>

or like this:

<Postcode>
    1234
</Postcode>

Since you are trimming the string (with ^ and $) make sure that your XML looks like the former and not the latter.

Answer 2

No, there is no whitespace around the string.

I do have an email address regex which works perfectly.

Answer 3

The XML Schema regex flavor doesn't support the start and end anchors; all matches are anchored at both ends, always. It's probably trying to match '^' and '$' literally.