In Oracle 10gR2, two XMLTYPE tables are created.

  1. registration xml-schemes

    begin dbms_xmlschema.registerSchema( 'tab1_schema.xsd', '<?xml version="1.0" encoding="utf-8"?> <xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema"> <xs:element name="man"> <xs:complexType> <xs:sequence> <xs:element name="man_id" type="xs:int" /> <xs:element name="man_name" type="xs:string" /> <xs:element name="man_date" type="xs:date" /> <xs:element name="cars"> <xs:complexType> <xs:sequence> <xs:element maxOccurs="unbounded" name="car"> <xs:complexType> <xs:sequence> <xs:element name="car_id" type="xs:int" /> </xs:sequence> </xs:complexType> </xs:element> </xs:sequence> </xs:complexType> </xs:element> </xs:sequence> </xs:complexType> </xs:element> </xs:schema>', true, true, false, false); end; / begin dbms_xmlschema.registerSchema( 'tab2_schema.xsd', '<?xml version="1.0" encoding="utf-8"?> <xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema"> <xs:element name="car"> <xs:complexType> <xs:sequence> <xs:element name="car_id" type="xs:int" /> <xs:element name="car_name" type="xs:string" /> <xs:element name="car_date" type="xs:date" /> </xs:sequence> </xs:complexType> </xs:element> </xs:schema>', true, true, false, false); end; /

  2. creating tables based on xml schemas

    create table tab1 of xmltype xmlschema "tab1_schema.xsd" element "man";

    create table tab2 of xmltype xmlschema "tab2_schema.xsd" element "car";

  3. key creation

    alter table tab1 add constraint tab1_pk primary key (xmldata. "man_id");

    alter table tab2 add constraint tab2_pk primary key (xmldata. "car_id");

4. Question: how to create a foreign key for the tab1 table (the element "/ cars / car / car_id") on the table tab2 (the element "/ car_id")

  alter table tab1 add constraint tab1_fk foreign key (xmldata."/cars/car/car_id") references tab2(xmldata."car_id");

    1 answer 1

    No way, in general, this whole fashion to shove in the XMLTYPE is a very bad practice, than you did not please the clob ???. But in general, make another field where you write down your ID. either by default sys_guid and you will be a unique ID

    • Storage in CLOB excludes xmltype store object relational (structural storage of xml-document). - prunum
    • In general, ideally, as for hml in an oracle, it is better to do so, parsing it into a table, and when sending somewhere to collect hml, it will be much more convenient. as for CLOB, I always work with him and there were no problems. do a lot of checks before writing it there so that it would be valid. - Valera Vovnov