Oracle xmldb Referencial Integrity

From: will <>
Date: 24 Jan 2005 09:04:35 -0800
Message-ID: <>

I am currently looking into referential integrity for an xml document stored in a table of xmltype.
The aim is to be able create a check constraint on the table containing the xmltype column. This constraint will create a foreign key only allowing certain values for a specified element in the xml document, that appear in another referenced column..

Progress so far..

register schema

 xmlschema varchar2(10000) :=
 '<?xml version="1.0" encoding="UTF-8"?>
<xs:schema xmlns:xs=""
xmlns:da="" xmlns:xdb=""

	<xs:element name="calendar">
			<xs:documentation>Comment describing your root
				<xs:element name="date">
					    <xs:attribute name="value" type="xs:dateTime" use="optional"
					    <xs:attribute name="ac_num" type="xs:string" use="optional"


 schemaURL => '',  schemaDoc => xmlschema
  • Create table based on that registered schema...

  create table xml_calendar

        (msg_id           number     PRIMARY KEY NOT NULL
        ,xml_date       sys.XMLTYPE
        	xmltype column xml_date 
            ELEMENT ""

  • insert simple xml document into xmltype column...

insert into xml_calendar
values (6, xmltype ('
<da:calendar xmlns:da=""
xmlns:xsi="" xsi:schemaLocation="">

	<!--date value="2004-03-32"/-->
	<date value="2004-12-20T19:12:50" ac_num="1234-0987"/>

  • Create simple refernce table

create table bank_details

 (bank_name varchar2(100),
  bank_account_name varchar2(100),
  bank_account_number varchar2(100) PRIMARY KEY NOT NULL,
  bank_account_type varchar2(100)


...and populate with trivial data

insert into bank_details
  values ('barclays','mr smith','123966-123956','client');   

  insert into bank_details
  values ('barclays','mr jones','198546-123875','client');   

  insert into bank_details
  values ('barclays','mr barnes','123456-123456','client');   

  insert into bank_details
  values ('barclays','mr bloggs','123789-123598','office');

.. now the part that needs work

ALTER TABLE xml_calendar
 FOREIGN KEY (extractvalue(xml_calendar.XML_DATE, '/da:calendar/date/_at_ac_num','xmlns:da="'))  REFERENCES xml_test.bank_details(bank_account_number)

currently creating this constraint returns invalid identifier for the extract Value function.. Im sure the xpath expression is correct and the sytax for the constraint is sound..

Any help with creating this constraint would be greatly appreciated...


Will Received on Mon Jan 24 2005 - 18:04:35 CET

Original text of this message