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

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.

From stackoverflow

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

• No, there is no whitespace around the string.

  I do have an email address regex which works perfectly.

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

  Peter Boughton : Yep - I've encountered this before. Removing the ^ and $ allowed the match to work.