SQL 2005 XML Schemas - Defining a Unique Attribute

Question

Here's a Sample xml document I'm trying to create a schema for:

'<Fields>
  <Field ID="-1">somevalue</Field>
  <Field ID="-2" />
  <Field ID="-3" />
  <Field ID="-4">Some other value</Field>
  <Field ID="-5" />
</Fields>'

I'm trying to create a SQL Server 2005 schema collection to:
1. prevent dupicate id's.
2. only allow negative ids.

I can achieve the negative constraint with type="xs:negativeInteger" for the id attribute. But I can't create a unique constraint or key\keyref in SQL 2005. If I set my type="xs:ID", then I cant use numbers at all for my ID values, nevermind negative IDs.

Either I'm missing something, or its not possible. If its not possible, why would SQL 2005 not support the ability to have a unique attribute? Is there a work around using a constraint\trigger or an xml index on the table, or some other technique that I cant even imagine right now?

All help is greatly appreciated.

Answer 1

Here's a solution using "unique" ("key" also works):

<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema"&gt;
  <xs:element name="Fields">
    <xs:complexType>
      <xs:sequence>
        <xs:element maxOccurs="unbounded" ref="Field"/>
      </xs:sequence>
    </xs:complexType>

    <xs:unique name="nearlyID">
      <xs:selector xpath=".//*"/>  
      <xs:field xpath="@ID"/>
    </xs:unique>
  </xs:element>


  <xs:element name="Field">
    <xs:complexType>
      <xs:simpleContent>
        <xs:extension base="xs:string">
          <xs:attribute name="ID" type="xs:negativeInteger"/>
        </xs:extension>
      </xs:simpleContent>
    </xs:complexType>
  </xs:element>
</xs:schema>

It validates your example, and gives errors for:

1. duplicate id's
2. id's that aren't negative integers.

Here's your eg plus tests:

<Fields>
  <Field ID="-1">somevalue</Field>
  <Field ID="-2" />
  <Field ID="-3" />
  <Field ID="-4">Some other value</Field>
  <Field ID="-5" />

  <Field ID="-2"   >  not unique   </Field>
  <Field ID="2"    >  not negative </Field>
  <Field ID="hello">  not integer  </Field>
</Fields>

EDIT I have no idea if this is the best way or even if it's a good way. It works, but I think there's might be a simpler, more straightforward way.