Oracle xmldb Referencial Integrity

From: will <william_hulse_at_hotmail.com>
Date: 24 Jan 2005 09:04:35 -0800
Message-ID: <283ad5ec.0501240904.4477092_at_posting.google.com>

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

declare
 xmlschema varchar2(10000) :=
 '<?xml version="1.0" encoding="UTF-8"?>
<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema"
xmlns:da="http://www.oracle.com/date.xsd" xmlns:xdb="http://xmlns.oracle.com/xdb"
targetNamespace="http://www.oracle.com/date.xsd">

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

</xs:schema>';

 begin
 dbms_xmlschema.registerSchema
 (
 schemaURL => 'http://www.oracle.com/date.xsd',  schemaDoc => xmlschema
  );
 end;

• 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 "http://www.oracle.com/date.xsd#calendar"
			;

• insert simple xml document into xmltype column...

insert into xml_calendar
values (6, xmltype ('
<da:calendar xmlns:da="http://www.oracle.com/date.xsd"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://www.oracle.com/date.xsd http://www.oracle.com/date.xsd">

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

• 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
 ADD CONSTRAINT CON_AC_NUM
 FOREIGN KEY (extractvalue(xml_calendar.XML_DATE, '/da:calendar/date/_at_ac_num','xmlns:da="http://www.oracle.com/date.xsd'))  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...

Regards

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