Re: XMLType primary key

From: stephen O'D <stephen.odonnell_at_gmail.com>
Date: 28 Oct 2005 01:19:47 -0700
Message-ID: <1130487587.831492.56940@g14g2000cwa.googlegroups.com>

Albert Greinöcker wrote:
> Hi NG,
>
> I have a question concerning Oracle's XmlType. How can I use a similar
> constraint like a primary key within an XMLType entry?
> In other words: I would like to store documents as XML which do have an
> unique id (which is an attribute of the root element),
> e.g.
> <METS>
> <metsHdr ID="0001">
> </metsHdr>
> </METS>
>
> How can I assure that the column does not already contain a document with ID
> 0001
> (without using triggers or storing the id in another column)?
>
> thx,
> Albert

You can create a unique function based index on the table, and this will do what you want:-

SQL> create table pkey (xml xmltype);

Table created.

SQL> create unique index pkey_pk on pkey (xml.extract('/METS/metsHdr/@ID').getstringval());

Index created.

SQL> insert into pkey values (xmltype.createxml('<METS> <metsHdr ID="0001">
</metsHdr>
</METS>')); 2 3 4

1 row created.

SQL> /
insert into pkey values (xmltype.createxml('<METS> *
ERROR at line 1:
ORA-00001: unique constraint (BAL.PKEY_PK) violated

SQL> insert into pkey values (xmltype.createxml('<METS> <metsHdr ID="0002">
</metsHdr>
</METS>')); 2 3 4

1 row created.

SQL> commit
2 ;

Commit complete.

SQL> select * from pkey;

XML

SQL> This will also allow Oracle to use the same function based index to extract rows using the index if you issue queries like:-

select xml
from pkey
where xml.extract('/METS/metsHdr/@ID').getstringval() = '001'

Cheers,

Stephen. Received on Fri Oct 28 2005 - 03:19:47 CDT