May 13
Primary key on xml column
The xml data has a few limitations that other datatypes in SQL Server 2005 do not have, for example you cannot compare two xml values. This has to with the nature of xml, but as a result it prevents you from making an xml column a primary key, sort of...
With little help from XQuery and a persisted computed column you can make a primary key on an xml column. For example suppose you have an invoice table and you want to make sure that the same invoice isn't inserted twice. An invoice looks like:
<Invoice>
<ID>1200</ID>
<LineItems>
<LineItem>stuff</LineItem>
</LineItems>
</Invoice>
You table for invoices would be something like:
CREATE TABLE INVOICES
(
ID ??? PRIMARY KEY,
Invoice xml
)
The question is what should replace ???. The answer is, of course, the content of the <ID> element in the invoice. A user defined function can be used to pull out the ID.
create function InvId9(@i xml)
returns varchar(10)
with schemabinding
as
BEGIN
DECLARE @inv int
select @inv =
@i.value('string(/Invoice[1]/ID[1])',
'varchar(10)')
return @inv
END
Now we can create the table as:
CREATE TABLE INVOICES
(
ID as dbo.InvId9(Invoice) PERSISTED PRIMARY KEY,
Invoice xml
)
There are a couple of gotcha's here. First of all the function used to extract the ID must be schema bound even though it is not use anything from the database schema. The second is that it may not return a MAX type, for example VARCHAR(MAX), because you cannot create an index on a MAX type.
So there you go, a way to use an xml column as a primary key.
Dan