SQL 2005 XML Schemas - Defining a Unique Attribute
Here is an example 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 am trying to create a SQL Server 2005 schema collection to:
1. prevent duplicate ID.
2.Only allow negative IDs.
I can get a negative constraint with type = "xs: negativeInteger" for the id attribute. But I cannot create a unique constraint or key \ keyref in SQL 2005. If I set my type = "xs: ID" then I cannot use numbers at all for ID values, never negative IDs.
Either I'm missing something, or it's impossible. If this is not possible, why does SQL 2005 not support the ability to have a unique attribute? Is there a work around using constraint / trigger or xml index on the table, or some other technique that I can't even imagine right now?
All help is greatly appreciated.
a source to share
Here's a solution using "unique" ("key"):
<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema">
<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 checks your example and gives errors for:
- duplicate id
- id, which are not negative integers.
Here's yours for example 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 is a good way. It works, but I think there might be an easier, simpler way.
a source to share
This is an old post, but still relevant today as unique syntax is not supported (and does not have to be below).
What you need to do is create a schema-bound function that will pull in the value that you want to be unique.
Then you create a permanent computed column in the table that contains the xml column. This computed column will execute the associated schema function defined above.
Finally, you define a unique constraint on the computed column to be stored. Now it is not only guaranteed to be unique, but also indexed for quick search.
EDIT -
This works for top-level attributes that need to be unique across multiple rows.
If you need uniqueness in a set of elements in a single xml document, the approach is similar.
You are creating a function that takes the xml data type as a parameter and has a BIT return value.
Then you use xpath on the xml datatype to check for uniqueness yourself. Return 1 if the values are unique, otherwise return 0.
Finally, you add a validation constraint to the table that calls the scalar function and validates the result: 1.
a source to share