This discussion is archived
2 Replies Latest reply: Aug 6, 2006 9:03 AM by mdrake RSS

Relational access to XML Content

mdrake Expert
Currently Being Moderated
This thread will deal with getting relational access to XML content stored in Oracle XML DB. It will cover the following techniques

1. Registering an XML Schema and creating relational views.

2. Using XMLType views and instead of triggers
  • 1. Relational Views of XML Content.
    mdrake Expert
    Currently Being Moderated
    This example shows how to create relational views of XML content. These views provide (read-only) relational access to XML content stored in Oracle XML DB.

    The first step register an XML Schema that describes the XML content using DBMS_XMLSCHEMA.REGISTERSCHEMA. This process creates an XMLType table that can store the XML documents.

    The second step is to create a set of relational views based on the XMLTable() operator. In a pre 10gr2 or XE database the views would need to be created using extractValue() operators over table(xmlsequence(extract())) constructs.

    These views that provide relational access to the contents of the documents stored in the XMLType table.
    SQL> var schemaURL varchar2(256)
    SQL> var schemaPath varchar2(256)
    SQL> --
    SQL> create or replace directory XMLDIR as '/xdb/faq/testdata'
      2  /
    
    Directory created.
    
    SQL> begin
      2    :schemaURL := 'http://xfiles:8080/home/SCOTT/poSource/xsd/purchaseOrder.xsd';
      3    :schemaPath := '/public/purchaseOrder.xsd';
      4  end;
      5  /
    
    PL/SQL procedure successfully completed.
    
    SQL> call dbms_xmlSchema.deleteSchema(:schemaURL,4)
      2  /
    
    Call completed.
    
    SQL> declare
      2    res boolean;
      3    xmlSchema xmlType := xmlType(
      4  '<!-- edited with XML Spy v4.0 U (http://www.xmlspy.com) by Mark (Drake) -->
      5  <xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns:xdb="http://xmlns.oracle.com/xdb" version="1.0" xdb:storeVarrayAsTable="tr
    ue">
      6          <xs:element name="PurchaseOrder" type="PurchaseOrderType" xdb:defaultTable="PURCHASEORDER"/>
      7          <xs:complexType name="PurchaseOrderType" xdb:SQLType="PURCHASEORDER_T" xdb:maintainDOM="false">
      8                  <xs:sequence>
      9                          <xs:element name="Reference" type="ReferenceType" xdb:SQLName="REFERENCE"/>
     10                          <xs:element name="Actions" type="ActionsType" xdb:SQLName="ACTIONS"/>
     11                          <xs:element name="Reject" type="RejectionType" minOccurs="0" xdb:SQLName="REJECTION"/>
     12                          <xs:element name="Requestor" type="RequestorType" xdb:SQLName="REQUESTOR"/>
     13                          <xs:element name="User" type="UserType" xdb:SQLName="USERID"/>
     14                          <xs:element name="CostCenter" type="CostCenterType" xdb:SQLName="COST_CENTER"/>
     15                          <xs:element name="ShippingInstructions" type="ShippingInstructionsType" xdb:SQLName="SHIPPING_INSTRUCTIONS"/>
     16                          <xs:element name="SpecialInstructions" type="SpecialInstructionsType" xdb:SQLName="SPECIAL_INSTRUCTIONS"/>
     17                          <xs:element name="LineItems" type="LineItemsType" xdb:SQLName="LINEITEMS"/>
     18                  </xs:sequence>
     19          </xs:complexType>
     20          <xs:complexType name="LineItemsType" xdb:SQLType="LINEITEMS_T" xdb:maintainDOM="false">
     21                  <xs:sequence>
     22                          <xs:element name="LineItem" type="LineItemType" maxOccurs="unbounded" xdb:SQLName="LINEITEM" xdb:SQLCollType="L
    INEITEM_V"/>
     23                  </xs:sequence>
     24          </xs:complexType>
     25          <xs:complexType name="LineItemType" xdb:SQLType="LINEITEM_T" xdb:maintainDOM="false">
     26                  <xs:sequence>
     27                          <xs:element name="Description" type="DescriptionType" xdb:SQLName="DESRIPTION"/>
     28                          <xs:element name="Part" type="PartType" xdb:SQLName="PART"/>
     29                  </xs:sequence>
     30                  <xs:attribute name="ItemNumber" type="xs:integer" xdb:SQLName="ITEMNUMBER" xdb:SQLType="NUMBER"/>
     31          </xs:complexType>
     32          <xs:complexType name="PartType" xdb:SQLType="PART_T" xdb:maintainDOM="false">
     33                  <xs:attribute name="Id" xdb:SQLName="PART_NUMBER" xdb:SQLType="VARCHAR2">
     34                          <xs:simpleType>
     35                                  <xs:restriction base="xs:string">
     36                                          <xs:minLength value="10"/>
     37                                          <xs:maxLength value="14"/>
     38                                  </xs:restriction>
     39                          </xs:simpleType>
     40                  </xs:attribute>
     41                  <xs:attribute name="Quantity" type="moneyType" xdb:SQLName="QUANTITY"/>
     42                  <xs:attribute name="UnitPrice" type="quantityType" xdb:SQLName="UNITPRICE"/>
     43          </xs:complexType>
     44          <xs:simpleType name="ReferenceType">
     45                  <xs:restriction base="xs:string">
     46                          <xs:minLength value="18"/>
     47                          <xs:maxLength value="30"/>
     48                  </xs:restriction>
     49          </xs:simpleType>
     50          <xs:complexType name="ActionsType" xdb:SQLType="ACTIONS_T" xdb:maintainDOM="false">
     51                  <xs:sequence>
     52                          <xs:element name="Action" maxOccurs="4" xdb:SQLName="ACTION" xdb:SQLCollType="ACTION_V">
     53                                  <xs:complexType xdb:SQLType="ACTION_T" xdb:maintainDOM="false">
     54                                          <xs:sequence>
     55                                                  <xs:element name="User" type="UserType" xdb:SQLName="ACTIONED_BY"/>
     56                                                  <xs:element name="Date" type="DateType" minOccurs="0" xdb:SQLName="DATE_ACTIONED"/>
     57                                          </xs:sequence>
     58                                  </xs:complexType>
     59                          </xs:element>
     60                  </xs:sequence>
     61          </xs:complexType>
     62          <xs:complexType name="RejectionType" xdb:SQLType="REJECTION_T" xdb:maintainDOM="false">
     63                  <xs:all>
     64                          <xs:element name="User" type="UserType" minOccurs="0" xdb:SQLName="REJECTED_BY"/>
     65                          <xs:element name="Date" type="DateType" minOccurs="0" xdb:SQLName="DATE_REJECTED"/>
     66                          <xs:element name="Comments" type="CommentsType" minOccurs="0" xdb:SQLName="REASON_REJECTED"/>
     67                  </xs:all>
     68          </xs:complexType>
     69          <xs:complexType name="ShippingInstructionsType" xdb:SQLType="SHIPPING_INSTRUCTIONS_T" xdb:maintainDOM="false">
     70                  <xs:sequence>
     71                          <xs:element name="name" type="NameType" minOccurs="0" xdb:SQLName="SHIP_TO_NAME"/>
     72                          <xs:element name="address" type="AddressType" minOccurs="0" xdb:SQLName="SHIP_TO_ADDRESS"/>
     73                          <xs:element name="telephone" type="TelephoneType" minOccurs="0" xdb:SQLName="SHIP_TO_PHONE"/>
     74                  </xs:sequence>
     75          </xs:complexType>
     76          <xs:simpleType name="moneyType">
     77                  <xs:restriction base="xs:decimal">
     78                          <xs:fractionDigits value="2"/>
     79                          <xs:totalDigits value="12"/>
     80                  </xs:restriction>
     81          </xs:simpleType>
     82          <xs:simpleType name="quantityType">
     83                  <xs:restriction base="xs:decimal">
     84                          <xs:fractionDigits value="4"/>
     85                          <xs:totalDigits value="8"/>
     86                  </xs:restriction>
     87          </xs:simpleType>
     88          <xs:simpleType name="UserType">
     89                  <xs:restriction base="xs:string">
     90                          <xs:minLength value="1"/>
     91                          <xs:maxLength value="10"/>
     92                  </xs:restriction>
     93          </xs:simpleType>
     94          <xs:simpleType name="RequestorType">
     95                  <xs:restriction base="xs:string">
     96                          <xs:minLength value="0"/>
     97                          <xs:maxLength value="128"/>
     98                  </xs:restriction>
     99          </xs:simpleType>
    100          <xs:simpleType name="CostCenterType">
    101                  <xs:restriction base="xs:string">
    102                          <xs:minLength value="1"/>
    103                          <xs:maxLength value="4"/>
    104                  </xs:restriction>
    105          </xs:simpleType>
    106          <xs:simpleType name="VendorType">
    107                  <xs:restriction base="xs:string">
    108                          <xs:minLength value="0"/>
    109                          <xs:maxLength value="20"/>
    110                  </xs:restriction>
    111          </xs:simpleType>
    112          <xs:simpleType name="PurchaseOrderNumberType">
    113                  <xs:restriction base="xs:integer"/>
    114          </xs:simpleType>
    115          <xs:simpleType name="SpecialInstructionsType">
    116                  <xs:restriction base="xs:string">
    117                          <xs:minLength value="0"/>
    118                          <xs:maxLength value="2048"/>
    119                  </xs:restriction>
    120          </xs:simpleType>
    121          <xs:simpleType name="NameType">
    122                  <xs:restriction base="xs:string">
    123                          <xs:minLength value="1"/>
    124                          <xs:maxLength value="20"/>
    125                  </xs:restriction>
    126          </xs:simpleType>
    127          <xs:simpleType name="AddressType">
    128                  <xs:restriction base="xs:string">
    129                          <xs:minLength value="1"/>
    130                          <xs:maxLength value="256"/>
    131                  </xs:restriction>
    132          </xs:simpleType>
    133          <xs:simpleType name="TelephoneType">
    134                  <xs:restriction base="xs:string">
    135                          <xs:minLength value="1"/>
    136                          <xs:maxLength value="24"/>
    137                  </xs:restriction>
    138          </xs:simpleType>
    139          <xs:simpleType name="DateType">
    140                  <xs:restriction base="xs:date"/>
    141          </xs:simpleType>
    142          <xs:simpleType name="CommentsType">
    143                  <xs:restriction base="xs:string">
    144                          <xs:minLength value="1"/>
    145                          <xs:maxLength value="2048"/>
    146                  </xs:restriction>
    147          </xs:simpleType>
    148          <xs:simpleType name="DescriptionType">
    149                  <xs:restriction base="xs:string">
    150                          <xs:minLength value="1"/>
    151                          <xs:maxLength value="256"/>
    152                  </xs:restriction>
    153          </xs:simpleType>
    154  </xs:schema>
    155  ');
    156  begin
    157    if (dbms_xdb.existsResource(:schemaPath)) then
    158      dbms_xdb.deleteResource(:schemaPath);
    159    end if;
    160    res := dbms_xdb.createResource(:schemaPath,xmlSchema);
    161  end;
    162  /
    
    PL/SQL procedure successfully completed.
    
    SQL> begin
      2    dbms_xmlschema.registerSchema
      3    (
      4      :schemaURL,
      5      xdbURIType(:schemaPath).getClob(),
      6      TRUE,TRUE,FALSE,TRUE
      7    );
      8  end;
      9  /
    
    PL/SQL procedure successfully completed.
    
    SQL> declare
      2    nested_table_name varchar2(256);
      3    iot_index_name varchar2(256);
      4  begin
      5    select table_name
      6      into nested_table_name
      7      from user_nested_tables
      8     where parent_table_column = '"XMLDATA"."ACTIONS"."ACTION"'
      9       and parent_table_name = 'PURCHASEORDER';
     10
     11    execute immediate 'rename "'|| nested_table_name ||'" to ACTION_TABLE';
     12
     13    select index_name
     14      into iot_index_name
     15      from user_indexes
     16     where table_name = 'ACTION_TABLE' and index_type = 'IOT - TOP';
     17
     18    execute immediate 'alter index "'|| iot_index_name ||'" rename to ACTION_IOT';
     19
     20    select table_name
     21      into nested_table_name
     22      from user_nested_tables
     23     where parent_table_column = '"XMLDATA"."LINEITEMS"."LINEITEM"'
     24       and parent_table_name = 'PURCHASEORDER';
     25
     26    execute immediate 'rename "'|| nested_table_name ||'" to LINEITEM_TABLE';
     27
     28    select index_name
     29      into iot_index_name
     30      from user_indexes
     31     where table_name = 'LINEITEM_TABLE' and index_type = 'IOT - TOP';
     32
     33    execute immediate 'alter index "'|| iot_index_name ||'" rename to LINEITEM_IOT';
     34  end;
     35  /
    
    PL/SQL procedure successfully completed.
    
    SQL> desc ACTION_TABLE
     Name                                                                                Null?    Type
     ----------------------------------------------------------------------------------- -------- ----------------------------------------------
    ----------
     ACTIONED_BY                                                                                  VARCHAR2(10 CHAR)
     DATE_ACTIONED                                                                                DATE
    
    SQL> --
    SQL> desc LINEITEM_TABLE
     Name                                                                                Null?    Type
     ----------------------------------------------------------------------------------- -------- ----------------------------------------------
    ----------
     ITEMNUMBER                                                                                   NUMBER(38)
     DESRIPTION                                                                                   VARCHAR2(256 CHAR)
     PART                                                                                         PART_T
    
    SQL> --
    SQL> create or replace view PURCHASEORDER_MASTER_VIEW
      2  as
      3    select m.*
      4      from PURCHASEORDER p,
      5           xmlTable
      6           (
      7              '/PurchaseOrder'
      8              passing object_value
      9              columns
     10              REFERENCE           varchar2(30)   path  'Reference',
     11              REQUESTER           varchar2(128)  path  'Requestor',
     12              USERID              varchar2(10)   path  'User',
     13              COSTCENTER          varchar2(4)    path  'CostCenter',
     14              REJECTEDBY          varchar2(10)   path  '/Reject/User',
     15              DATEREJECTED        timestamp      path  'Rejection/Date',
     16              COMMENTS            varchar2(2048) path  'Rejection/Comments',
     17              SHIPTONAME          varchar2(20)   path  'ShippingInstructions/name',
     18              ADDRESS             varchar2(128)  path  'ShippingInstructions/address',
     19              PHONE               varchar2(24)   path  'ShippingInstructions/telephone',
     20              SPECIALINSTRUCTIONS varchar2(2048) path  'SpecialInstructions'
     21           ) m
     22  /
    
    View created.
    
    SQL> create or replace view PURCHASEORDER_ACTION_VIEW
      2  as
      3    select m.REFERENCE, a.*
      4      from PURCHASEORDER p,
      5           xmlTable
      6           (
      7              '/PurchaseOrder'
      8              passing object_value
      9              columns
     10              REFERENCE       varchar2(30)   path  'Reference',
     11              ACTIONS         xmlType        path  'Actions/Action'
     12           ) m,
     13           xmlTable
     14           (
     15             '/Action'
     16             passing m.ACTIONS
     17             columns
     18             ACTIONEDBY   varchar2(10) path 'User',
     19             DATEACTIONED timestamp    path 'Date'
     20           ) a
     21  /
    
    View created.
    
    SQL> create or replace view PURCHASEORDER_LINEITEM_VIEW
      2  as
      3    select m.REFERENCE, l.*
      4      from PURCHASEORDER p,
      5           xmlTable
      6           (
      7              '/PurchaseOrder'
      8              passing object_value
      9              columns
     10              REFERENCE       varchar2(30)   path  'Reference',
     11              ITEMS           xmlType        path  'LineItems/LineItem'
     12           ) m,
     13           xmlTable
     14           (
     15             '/LineItem'
     16             passing m.ITEMS
     17             columns
     18             LINENO       NUMBER(38)    path '@ItemNumber',
     19             DESCRIPTION  VARCHAR2(256) path 'Description',
     20             UPC          VARCHAR2(14)  path 'Part/@Id',
     21             QUANTITY     NUMBER(12,2)  path 'Part/@Quantity',
     22             UNITPRICE    NUMBER(8,4)   path 'Part/@UnitPrice'
     23           ) l
     24  /
    
    View created.
    
    SQL> set long 10000 pages 200 lines 150
    SQL> --
    SQL> select xmltype(bfilename('XMLDIR','2003.xml'),nls_charset_id('AL32UTF8'))
      2    from dual
      3  /
    
    XMLTYPE(BFILENAME('XMLDIR','2003.XML'),NLS_CHARSET_ID('AL32UTF8'))
    --------------------------------------------------------------------------------------------------------------------------------------------
    ----------
    <upload>
      <directories>
        <directory>/2003</directory>
        <directory>/2003/Jan</directory>
        <directory>/2003/Feb</directory>
        <directory>/2003/Mar</directory>
        <directory>/2003/Apr</directory>
        <directory>/2003/May</directory>
        <directory>/2003/Jun</directory>
        <directory>/2003/Jul</directory>
        <directory>/2003/Aug</directory>
        <directory>/2003/Sep</directory>
        <directory>/2003/Oct</directory>
        <directory>/2003/Nov</directory>
        <directory>/2003/Dec</directory>
      </directories>
      <files>
        <file>/2003/Apr/AMCEWEN-20030409123336200304.xml</file>
        <file>/2003/Apr/EABEL-20030409123336200304.xml</file>
        <file>/2003/Apr/PTUCKER-20030409120030491PDT.xml</file>
        <file>/2003/Apr/PTUCKER-20030409123336200304.xml</file>
        <file>/2003/Apr/SBELL-20030409120030431PDT.xml</file>
        <file>/2003/Apr/SBELL-20030409123336200304.xml</file>
        <file>/2003/Apr/SKING-200304091233363200304.xml</file>
        <file>/2003/Apr/SMCCAIN-20030409120030441PDT.xml</file>
        <file>/2003/Apr/SMCCAIN-20030409120030451PDT.xml</file>
        <file>/2003/Apr/SMCCAIN-20030409123336151PDT.xml</file>
        <file>/2003/Apr/VJONES-20030409120030401PDT.xml</file>
        <file>/2003/Apr/VJONES-20030409123336301PDT.xml</file>
        <file>/2003/Aug/AWALSH-20030809123337203PDT.xml</file>
        <file>/2003/Aug/AWALSH-20030809123337303PDT.xml</file>
        <file>/2003/Aug/CJOHNSON-20030809123337283PDT.xml</file>
        <file>/2003/Aug/DAUSTIN-20030809123337103PDT.xml</file>
        <file>/2003/Aug/JCHEN-20030809123337123PDT.xml</file>
        <file>/2003/Aug/JCHEN-20030809123337223PDT.xml</file>
        <file>/2003/Aug/LSMITH-20030809123337323PDT.xml</file>
        <file>/2003/Aug/SBELL-20030809123337353PDT.xml</file>
        <file>/2003/Aug/SKING-20030809123337153PDT.xml</file>
        <file>/2003/Aug/SMCCAIN-20030809123337173PDT.xml</file>
        <file>/2003/Aug/TFOX-20030809123337243PDT.xml</file>
        <file>/2003/Dec/AMCEWEN-20031209123338445PDT.xml</file>
        <file>/2003/Dec/CJOHNSON-20031209123338595PDT.xml</file>
        <file>/2003/Dec/DAUSTIN-20031209123338645PDT.xml</file>
        <file>/2003/Dec/EABEL-20031209123338324PDT.xml</file>
        <file>/2003/Dec/JCHEN-20031209123338475PDT.xml</file>
        <file>/2003/Dec/LSMITH-20031209123338535PDT.xml</file>
        <file>/2003/Dec/PTUCKER-20031209123338565PDT.xml</file>
        <file>/2003/Dec/SBELL-20031209123338304PDT.xml</file>
        <file>/2003/Dec/SBELL-20031209123338505PDT.xml</file>
        <file>/2003/Dec/TFOX-20031209123338354PDT.xml</file>
        <file>/2003/Dec/VJONES-20031209123338374PDT.xml</file>
        <file>/2003/Feb/AMCEWEN-20030209123335600PDT.xml</file>
        <file>/2003/Feb/AMCEWEN-20030209123335701PDT.xml</file>
        <file>/2003/Feb/DAUSTIN-20030209123335811PDT.xml</file>
        <file>/2003/Feb/EABEL-20030209123335791PDT.xml</file>
        <file>/2003/Feb/PTUCKER-20030209123335721PDT.xml</file>
        <file>/2003/Feb/PTUCKER-20030209123335821PDT.xml</file>
        <file>/2003/Feb/SBELL-20030209123335771PDT.xml</file>
        <file>/2003/Feb/SMCCAIN-20030209123335681PDT.xml</file>
        <file>/2003/Feb/WSMITH-20030209123335650PDT.xml</file>
        <file>/2003/Feb/WSMITH-20030209123335741PDT.xml</file>
        <file>/2003/Feb/WSMITH-20030209123335751PDT.xml</file>
        <file>/2003/Jan/AMCEWEN-20030109123335370PDT.xml</file>
        <file>/2003/Jan/AWALSH-2003010912333570PDT.xml</file>
        <file>/2003/Jan/CJOHNSON-20030109123335170PDT.xml</file>
        <file>/2003/Jan/LSMITH-20030109123335500PDT.xml</file>
        <file>/2003/Jan/PTUCKER-20030109123335430PDT.xml</file>
        <file>/2003/Jan/SBELL-20030109123335280PDT.xml</file>
        <file>/2003/Jan/SKING-20030109123335560PDT.xml</file>
        <file>/2003/Jan/SMCCAIN-20030109123335470PDT.xml</file>
        <file>/2003/Jan/TFOX-20030109123335520PDT.xml</file>
        <file>/2003/Jan/VJONES-20030109123335350PDT.xml</file>
        <file>/2003/Jan/WSMITH-20030109123335450PDT.xml</file>
        <file>/2003/Jul/AMCEWEN-20030709123336902PDT.xml</file>
        <file>/2003/Jul/CJOHNSON-2003070912333783PDT.xml</file>
        <file>/2003/Jul/DAUSTIN-2003070912333702PDT.xml</file>
        <file>/2003/Jul/DAUSTIN-2003070912333742PDT.xml</file>
        <file>/2003/Jul/LSMITH-2003070912333722PDT.xml</file>
        <file>/2003/Jul/SBELL-2003070912333763PDT.xml</file>
        <file>/2003/Jul/SKING-20030709123336952PDT.xml</file>
        <file>/2003/Jul/SMCCAIN-20030709123336972PDT.xml</file>
        <file>/2003/Jul/TFOX-20030709123336872PDT.xml</file>
        <file>/2003/Jul/TFOX-20030709123336912PDT.xml</file>
        <file>/2003/Jul/VJONES-20030709123336932PDT.xml</file>
        <file>/2003/Jun/AMCEWEN-20030609123336762PDT.xml</file>
        <file>/2003/Jun/AWALSH-20030609123336642PDT.xml</file>
        <file>/2003/Jun/CJOHNSON-20030609123336712PDT.xml</file>
        <file>/2003/Jun/LSMITH-20030609123336852PDT.xml</file>
        <file>/2003/Jun/SKING-20030609123336622PDT.xml</file>
        <file>/2003/Jun/SKING-20030609123336822PDT.xml</file>
        <file>/2003/Jun/SMCCAIN-20030609123336842PDT.xml</file>
        <file>/2003/Jun/TFOX-20030609123336582PDT.xml</file>
        <file>/2003/Jun/TFOX-20030609123336782PDT.xml</file>
        <file>/2003/Jun/VJONES-20030609123336602PDT.xml</file>
        <file>/2003/Jun/VJONES-20030609123336802PDT.xml</file>
        <file>/2003/Mar/AWALSH-20030309123335871PDT.xml</file>
        <file>/2003/Mar/AWALSH-20030309123335911PDT.xml</file>
        <file>/2003/Mar/AWALSH-20030309123336101PDT.xml</file>
        <file>/2003/Mar/CJOHNSON-20030309123335851PDT.xml</file>
        <file>/2003/Mar/DAUSTIN-20030309123335931PDT.xml</file>
        <file>/2003/Mar/JCHEN-20030309123335961PDT.xml</file>
        <file>/2003/Mar/LSMITH-2003030912333661PDT.xml</file>
        <file>/2003/Mar/SBELL-2003030912333601PDT.xml</file>
        <file>/2003/Mar/SKING-20030309123336131PDT.xml</file>
        <file>/2003/Mar/TFOX-2003030912333681PDT.xml</file>
        <file>/2003/Mar/VJONES-20030309123335971PDT.xml</file>
        <file>/2003/May/CJOHNSON-20030509123336482PDT.xml</file>
        <file>/2003/May/EABEL-20030509123336382PDT.xml</file>
        <file>/2003/May/JCHEN-20030509123336462PDT.xml</file>
        <file>/2003/May/LSMITH-20030509123336562PDT.xml</file>
        <file>/2003/May/PTUCKER-20030509123336452PDT.xml</file>
        <file>/2003/May/SBELL-20030509123336362PDT.xml</file>
        <file>/2003/May/SBELL-20030509123336532PDT.xml</file>
        <file>/2003/May/SKING-20030509123336392PDT.xml</file>
        <file>/2003/May/SMCCAIN-20030509123336512PDT.xml</file>
        <file>/2003/May/VJONES-20030509123336502PDT.xml</file>
        <file>/2003/May/WSMITH-20030509123336412PDT.xml</file>
        <file>/2003/Nov/AWALSH-20031109123337954PDT.xml</file>
        <file>/2003/Nov/AWALSH-2003110912333844PDT.xml</file>
        <file>/2003/Nov/CJOHNSON-20031109123338124PDT.xml</file>
        <file>/2003/Nov/CJOHNSON-2003110912333824PDT.xml</file>
        <file>/2003/Nov/LSMITH-20031109123338234PDT.xml</file>
        <file>/2003/Nov/SBELL-20031109123338204PDT.xml</file>
        <file>/2003/Nov/SKING-20031109123337974PDT.xml</file>
        <file>/2003/Nov/SKING-20031109123338294PDT.xml</file>
        <file>/2003/Nov/SMCCAIN-2003110912333894PDT.xml</file>
        <file>/2003/Nov/TFOX-20031109123337994PDT.xml</file>
        <file>/2003/Nov/WSMITH-20031109123338154PDT.xml</file>
        <file>/2003/Oct/AMCEWEN-20031009123337874PDT.xml</file>
        <file>/2003/Oct/DAUSTIN-20031009123337613PDT.xml</file>
        <file>/2003/Oct/JCHEN-20031009123337633PDT.xml</file>
        <file>/2003/Oct/JCHEN-20031009123337733PDT.xml</file>
        <file>/2003/Oct/JCHEN-20031009123337764PDT.xml</file>
        <file>/2003/Oct/SBELL-20031009123337673PDT.xml</file>
        <file>/2003/Oct/SKING-20031009123337703PDT.xml</file>
        <file>/2003/Oct/TFOX-20031009123337653PDT.xml</file>
        <file>/2003/Oct/TFOX-20031009123337784PDT.xml</file>
        <file>/2003/Oct/VJONES-20031009123337814PDT.xml</file>
        <file>/2003/Oct/WSMITH-20031009123337924PDT.xml</file>
        <file>/2003/Sep/AWALSH-20030909123337483PDT.xml</file>
        <file>/2003/Sep/DAUSTIN-20030909123337423PDT.xml</file>
        <file>/2003/Sep/DAUSTIN-20030909123337553PDT.xml</file>
        <file>/2003/Sep/JCHEN-20030909123337443PDT.xml</file>
        <file>/2003/Sep/SKING-20030909123337383PDT.xml</file>
        <file>/2003/Sep/SKING-20030909123337503PDT.xml</file>
        <file>/2003/Sep/SMCCAIN-20030909123337403PDT.xml</file>
        <file>/2003/Sep/TFOX-20030909123337463PDT.xml</file>
        <file>/2003/Sep/TFOX-20030909123337563PDT.xml</file>
        <file>/2003/Sep/VJONES-20030909123337363PDT.xml</file>
        <file>/2003/Sep/VJONES-20030909123337583PDT.xml</file>
      </files>
    </upload>
    
    
    SQL> declare
      2    filelist XMLType;
      3    cursor xml_file_list (FILELIST XMLTYPE) is
      4    select *
      5      from xmltable
      6           (
      7             '//file' passing filelist
      8             Columns
      9             filename varchar2(128) path '/file'
     10           );
     11  begin
     12    filelist := xmltype(bfilename('XMLDIR','2003.xml'),nls_charset_id('AL32UTF8'));
     13    for file in xml_file_list(filelist) loop
     14      insert into PURCHASEORDER values (xmltype(bfilename('XMLDIR',file.filename),nls_charset_id('AL32UTF8')));
     15    end loop;
     16  end;
     17  /
    
    PL/SQL procedure successfully completed.
    
    SQL> select count(*) from PURCHASEORDER
      2  /
    
      COUNT(*)
    ----------
           133
    
    SQL> set long 1000
    SQL> --
    SQL> describe PURCHASEORDER
     Name                                                                                Null?    Type
     ----------------------------------------------------------------------------------- -------- ----------------------------------------------
    ----------
    TABLE of SYS.XMLTYPE(XMLSchema "http://xfiles:8080/home/SCOTT/poSource/xsd/purchaseOrder.xsd" Element "PurchaseOrder") STORAGE Object-relati
    onal TYPE "PURCHASEORDER_T"
    
    SQL> --
    SQL> select count(*) from PURCHASEORDER
      2  /
    
      COUNT(*)
    ----------
           133
    
    SQL> describe PURCHASEORDER_MASTER_VIEW
     Name                                                                                Null?    Type
     ----------------------------------------------------------------------------------- -------- ----------------------------------------------
    ----------
     REFERENCE                                                                                    VARCHAR2(30)
     REQUESTER                                                                                    VARCHAR2(128)
     USERID                                                                                       VARCHAR2(10)
     COSTCENTER                                                                                   VARCHAR2(4)
     REJECTEDBY                                                                                   VARCHAR2(10)
     DATEREJECTED                                                                                 TIMESTAMP(9)
     COMMENTS                                                                                     VARCHAR2(2048)
     SHIPTONAME                                                                                   VARCHAR2(20)
     ADDRESS                                                                                      VARCHAR2(128)
     PHONE                                                                                        VARCHAR2(24)
     SPECIALINSTRUCTIONS                                                                          VARCHAR2(2048)
    
    SQL> --
    SQL> select count(*) from PURCHASEORDER_MASTER_VIEW
      2  /
    
      COUNT(*)
    ----------
           133
    
    SQL> describe PURCHASEORDER_ACTION_VIEW
     Name                                                                                Null?    Type
     ----------------------------------------------------------------------------------- -------- ----------------------------------------------
    ----------
     REFERENCE                                                                                    VARCHAR2(30)
     ACTIONEDBY                                                                                   VARCHAR2(10)
     DATEACTIONED                                                                                 TIMESTAMP(9)
    
    SQL> --
    SQL> select count(*) from PURCHASEORDER_ACTION_VIEW
      2  /
    
      COUNT(*)
    ----------
           133
    
    SQL> describe PURCHASEORDER_LINEITEM_VIEW
     Name                                                                                Null?    Type
     ----------------------------------------------------------------------------------- -------- ----------------------------------------------
    ----------
     REFERENCE                                                                                    VARCHAR2(30)
     LINENO                                                                                       NUMBER(38)
     DESCRIPTION                                                                                  VARCHAR2(256)
     UPC                                                                                          VARCHAR2(14)
     QUANTITY                                                                                     NUMBER(12,2)
     UNITPRICE                                                                                    NUMBER(8,4)
    
    SQL> --
    SQL> select count(*) from PURCHASEORDER_LINEITEM_VIEW
      2  /
    
      COUNT(*)
    ----------
          2236
    
    SQL>
  • 2. XMLType view of Relational Content
    mdrake Expert
    Currently Being Moderated
    Here's an alternative that uses a purely relational persistance model. It allows the XML documents to be inserted view an XMLType view and then uses INSTEAD OF INSERT TRIGGERS to shred the XML into the relational tables. The XMLType view is created using SQL/XML publishing operators which allow an approximation of the original XML to be reconstructed from the data in the relation tables.

    SQL> var schemaURL varchar2(256)
    SQL> var schemaPath varchar2(256)
    SQL> --
    SQL> create or replace directory XMLDIR as '/xdb/faq/testdata'
      2  /
    
    Directory created.
    
    SQL> begin
      2    :schemaURL := 'http://xfiles:8080/home/SCOTT/poSource/xsd/purchaseOrder.xsd';
      3    :schemaPath := '/public/purchaseOrder.xsd';
      4  end;
      5  /
    
    PL/SQL procedure successfully completed.
    
    SQL> call dbms_xmlSchema.deleteSchema(:schemaURL,4)
      2  /
    
    Call completed.
    
    SQL> declare
      2    res boolean;
      3    xmlSchema xmlType := xmlType(
      4  '<!-- edited with XML Spy v4.0 U (http://www.xmlspy.com) by Mark (Drake) -->
      5  <xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns:xdb="http://xmlns.oracle.com/xdb" version="1.0" xdb:storeVarrayAsTable="tr
    ue">
      6          <xs:element name="PurchaseOrder" type="PurchaseOrderType" xdb:defaultTable="PURCHASEORDER"/>
      7          <xs:complexType name="PurchaseOrderType" xdb:SQLType="PURCHASEORDER_T" xdb:maintainDOM="false">
      8                  <xs:sequence>
      9                          <xs:element name="Reference" type="ReferenceType" xdb:SQLName="REFERENCE"/>
     10                          <xs:element name="Actions" type="ActionsType" xdb:SQLName="ACTIONS"/>
     11                          <xs:element name="Reject" type="RejectionType" minOccurs="0" xdb:SQLName="REJECTION"/>
     12                          <xs:element name="Requestor" type="RequestorType" xdb:SQLName="REQUESTOR"/>
     13                          <xs:element name="User" type="UserType" xdb:SQLName="USERID"/>
     14                          <xs:element name="CostCenter" type="CostCenterType" xdb:SQLName="COST_CENTER"/>
     15                          <xs:element name="ShippingInstructions" type="ShippingInstructionsType" xdb:SQLName="SHIPPING_INSTRUCTIONS"/>
     16                          <xs:element name="SpecialInstructions" type="SpecialInstructionsType" xdb:SQLName="SPECIAL_INSTRUCTIONS"/>
     17                          <xs:element name="LineItems" type="LineItemsType" xdb:SQLName="LINEITEMS"/>
     18                  </xs:sequence>
     19          </xs:complexType>
     20          <xs:complexType name="LineItemsType" xdb:SQLType="LINEITEMS_T" xdb:maintainDOM="false">
     21                  <xs:sequence>
     22                          <xs:element name="LineItem" type="LineItemType" maxOccurs="unbounded" xdb:SQLName="LINEITEM" xdb:SQLCollType="L
    INEITEM_V"/>
     23                  </xs:sequence>
     24          </xs:complexType>
     25          <xs:complexType name="LineItemType" xdb:SQLType="LINEITEM_T" xdb:maintainDOM="false">
     26                  <xs:sequence>
     27                          <xs:element name="Description" type="DescriptionType" xdb:SQLName="DESRIPTION"/>
     28                          <xs:element name="Part" type="PartType" xdb:SQLName="PART"/>
     29                  </xs:sequence>
     30                  <xs:attribute name="ItemNumber" type="xs:integer" xdb:SQLName="ITEMNUMBER" xdb:SQLType="NUMBER"/>
     31          </xs:complexType>
     32          <xs:complexType name="PartType" xdb:SQLType="PART_T" xdb:maintainDOM="false">
     33                  <xs:attribute name="Id" xdb:SQLName="PART_NUMBER" xdb:SQLType="VARCHAR2">
     34                          <xs:simpleType>
     35                                  <xs:restriction base="xs:string">
     36                                          <xs:minLength value="10"/>
     37                                          <xs:maxLength value="14"/>
     38                                  </xs:restriction>
     39                          </xs:simpleType>
     40                  </xs:attribute>
     41                  <xs:attribute name="Quantity" type="moneyType" xdb:SQLName="QUANTITY"/>
     42                  <xs:attribute name="UnitPrice" type="quantityType" xdb:SQLName="UNITPRICE"/>
     43          </xs:complexType>
     44          <xs:simpleType name="ReferenceType">
     45                  <xs:restriction base="xs:string">
     46                          <xs:minLength value="18"/>
     47                          <xs:maxLength value="30"/>
     48                  </xs:restriction>
     49          </xs:simpleType>
     50          <xs:complexType name="ActionsType" xdb:SQLType="ACTIONS_T" xdb:maintainDOM="false">
     51                  <xs:sequence>
     52                          <xs:element name="Action" maxOccurs="4" xdb:SQLName="ACTION" xdb:SQLCollType="ACTION_V">
     53                                  <xs:complexType xdb:SQLType="ACTION_T" xdb:maintainDOM="false">
     54                                          <xs:sequence>
     55                                                  <xs:element name="User" type="UserType" xdb:SQLName="ACTIONED_BY"/>
     56                                                  <xs:element name="Date" type="DateType" minOccurs="0" xdb:SQLName="DATE_ACTIONED"/>
     57                                          </xs:sequence>
     58                                  </xs:complexType>
     59                          </xs:element>
     60                  </xs:sequence>
     61          </xs:complexType>
     62          <xs:complexType name="RejectionType" xdb:SQLType="REJECTION_T" xdb:maintainDOM="false">
     63                  <xs:all>
     64                          <xs:element name="User" type="UserType" minOccurs="0" xdb:SQLName="REJECTED_BY"/>
     65                          <xs:element name="Date" type="DateType" minOccurs="0" xdb:SQLName="DATE_REJECTED"/>
     66                          <xs:element name="Comments" type="CommentsType" minOccurs="0" xdb:SQLName="REASON_REJECTED"/>
     67                  </xs:all>
     68          </xs:complexType>
     69          <xs:complexType name="ShippingInstructionsType" xdb:SQLType="SHIPPING_INSTRUCTIONS_T" xdb:maintainDOM="false">
     70                  <xs:sequence>
     71                          <xs:element name="name" type="NameType" minOccurs="0" xdb:SQLName="SHIP_TO_NAME"/>
     72                          <xs:element name="address" type="AddressType" minOccurs="0" xdb:SQLName="SHIP_TO_ADDRESS"/>
     73                          <xs:element name="telephone" type="TelephoneType" minOccurs="0" xdb:SQLName="SHIP_TO_PHONE"/>
     74                  </xs:sequence>
     75          </xs:complexType>
     76          <xs:simpleType name="moneyType">
     77                  <xs:restriction base="xs:decimal">
     78                          <xs:fractionDigits value="2"/>
     79                          <xs:totalDigits value="12"/>
     80                  </xs:restriction>
     81          </xs:simpleType>
     82          <xs:simpleType name="quantityType">
     83                  <xs:restriction base="xs:decimal">
     84                          <xs:fractionDigits value="4"/>
     85                          <xs:totalDigits value="8"/>
     86                  </xs:restriction>
     87          </xs:simpleType>
     88          <xs:simpleType name="UserType">
     89                  <xs:restriction base="xs:string">
     90                          <xs:minLength value="1"/>
     91                          <xs:maxLength value="10"/>
     92                  </xs:restriction>
     93          </xs:simpleType>
     94          <xs:simpleType name="RequestorType">
     95                  <xs:restriction base="xs:string">
     96                          <xs:minLength value="0"/>
     97                          <xs:maxLength value="128"/>
     98                  </xs:restriction>
     99          </xs:simpleType>
    100          <xs:simpleType name="CostCenterType">
    101                  <xs:restriction base="xs:string">
    102                          <xs:minLength value="1"/>
    103                          <xs:maxLength value="4"/>
    104                  </xs:restriction>
    105          </xs:simpleType>
    106          <xs:simpleType name="VendorType">
    107                  <xs:restriction base="xs:string">
    108                          <xs:minLength value="0"/>
    109                          <xs:maxLength value="20"/>
    110                  </xs:restriction>
    111          </xs:simpleType>
    112          <xs:simpleType name="PurchaseOrderNumberType">
    113                  <xs:restriction base="xs:integer"/>
    114          </xs:simpleType>
    115          <xs:simpleType name="SpecialInstructionsType">
    116                  <xs:restriction base="xs:string">
    117                          <xs:minLength value="0"/>
    118                          <xs:maxLength value="2048"/>
    119                  </xs:restriction>
    120          </xs:simpleType>
    121          <xs:simpleType name="NameType">
    122                  <xs:restriction base="xs:string">
    123                          <xs:minLength value="1"/>
    124                          <xs:maxLength value="20"/>
    125                  </xs:restriction>
    126          </xs:simpleType>
    127          <xs:simpleType name="AddressType">
    128                  <xs:restriction base="xs:string">
    129                          <xs:minLength value="1"/>
    130                          <xs:maxLength value="256"/>
    131                  </xs:restriction>
    132          </xs:simpleType>
    133          <xs:simpleType name="TelephoneType">
    134                  <xs:restriction base="xs:string">
    135                          <xs:minLength value="1"/>
    136                          <xs:maxLength value="24"/>
    137                  </xs:restriction>
    138          </xs:simpleType>
    139          <xs:simpleType name="DateType">
    140                  <xs:restriction base="xs:date"/>
    141          </xs:simpleType>
    142          <xs:simpleType name="CommentsType">
    143                  <xs:restriction base="xs:string">
    144                          <xs:minLength value="1"/>
    145                          <xs:maxLength value="2048"/>
    146                  </xs:restriction>
    147          </xs:simpleType>
    148          <xs:simpleType name="DescriptionType">
    149                  <xs:restriction base="xs:string">
    150                          <xs:minLength value="1"/>
    151                          <xs:maxLength value="256"/>
    152                  </xs:restriction>
    153          </xs:simpleType>
    154  </xs:schema>
    155  ');
    156  begin
    157    if (dbms_xdb.existsResource(:schemaPath)) then
    158      dbms_xdb.deleteResource(:schemaPath);
    159    end if;
    160    res := dbms_xdb.createResource(:schemaPath,xmlSchema);
    161  end;
    162  /
    
    PL/SQL procedure successfully completed.
    
    SQL> begin
      2    dbms_xmlschema.registerSchema
      3    (
      4      :schemaURL,
      5      xdbURIType(:schemaPath).getClob(),
      6      TRUE,TRUE,FALSE,FALSE
      7    );
      8  end;
      9  /
    
    PL/SQL procedure successfully completed.
    
    SQL> create table PURCHASEORDER_TABLE
      2  (
      3   REFERENCE                                          VARCHAR2(32),
      4   PRIMARY KEY ("REFERENCE"),
      5   REQUESTER                                          VARCHAR2(48),
      6   USERID                                             VARCHAR2(32),
      7   COSTCENTER                                         VARCHAR2(3),
      8   SPECIALINSTRUCTIONS                                VARCHAR2(2048)
      9  )
     10  /
    
    Table created.
    
    SQL> create table PURCHASEORDER_ACTION
      2  (
      3   REFERENCE,
      4   FOREIGN KEY ("REFERENCE")                          REFERENCES "PURCHASEORDER_TABLE" ("REFERENCE") ON DELETE CASCADE,
      5   ACTIONEDBY                                         VARCHAR2(32),
      6   DATEACTIONED                                       DATE
      7  )
      8  /
    
    Table created.
    
    SQL> create table PURCHASEORDER_SHIPPING
      2  (
      3   REFERENCE,
      4   FOREIGN KEY ("REFERENCE")                          REFERENCES "PURCHASEORDER_TABLE" ("REFERENCE") ON DELETE CASCADE,
      5   PRIMARY KEY ("REFERENCE"),
      6   SHIPTONAME                                         VARCHAR2(48),
      7   ADDRESS                                            VARCHAR2(512),
      8   PHONE                                              VARCHAR2(32)
      9  )
     10  /
    
    Table created.
    
    SQL> create table PURCHASEORDER_REJECTION
      2  (
      3   REFERENCE,
      4   FOREIGN KEY ("REFERENCE")                          REFERENCES "PURCHASEORDER_TABLE" ("REFERENCE") ON DELETE CASCADE,
      5   PRIMARY KEY ("REFERENCE"),
      6   REJECTEDBY                                         VARCHAR2(32),
      7   DATEREJECTED                                       DATE,
      8   COMMENTS                                           VARCHAR2(2048)
      9  )
     10  /
    
    Table created.
    
    SQL> create table PURCHASEORDER_LINEITEM
      2  (
      3   REFERENCE,
      4   FOREIGN KEY ("REFERENCE")                          REFERENCES "PURCHASEORDER_TABLE" ("REFERENCE") ON DELETE CASCADE,
      5   LINENO                                             NUMBER(10),
      6   PRIMARY KEY ("REFERENCE","LINENO"),
      7   UPC                                                   VARCHAR2(15),
      8   DESCRIPTION                                        VARCHAR2(128),
      9   QUANTITY                                           NUMBER(10),
     10   UNITPRICE                                          NUMBER(12,2)
     11  )
     12  /
    
    Table created.
    
    SQL> create or replace view PURCHASEORDER
      2  of xmltype
      3  xmlSCHEMA "http://xfiles:8080/home/SCOTT/poSource/xsd/purchaseOrder.xsd" Element "PurchaseOrder"
      4  with object id
      5  (
      6    substr(extractValue(object_value,'/PurchaseOrder/Reference'),1,32)
      7  )
      8  as
      9    select xmlElement
     10           (
     11             "PurchaseOrder",
     12             xmlAttributes
     13             (
     14               'http://xfiles:8080/home/SCOTT/poSource/xsd/purchaseOrder.xsd' as "xsi:noNamespaceSchemaLocation",
     15               'http://www.w3.org/2001/XMLSchema-instance' as "xmlns:xsi"
     16             ),
     17             xmlElement("Reference",p.REFERENCE),
     18             xmlElement("Requestor",REQUESTER),
     19             xmlElement
     20             (
     21               "Actions",
     22               ( select xmlAgg
     23                        (
     24                          xmlElement
     25                          (
     26                            "Action",
     27                            xmlElement("User",ACTIONEDBY),
     28                            xmlElement("Date",DATEACTIONED)
     29                          )
     30                        )
     31                   from PURCHASEORDER_ACTION a
     32                  where a.REFERENCE = p.REFERENCE
     33               )
     34             ),
     35             xmlElement("User",USERID),
     36             xmlElement("CostCenter",COSTCENTER),
     37             xmlElement
     38             (
     39               "Reject",
     40                  xmlElement("User",REJECTEDBY),
     41               xmlElement("Date",DATEREJECTED),
     42                  xmlElement("Comments",COMMENTS)
     43             ),
     44             xmlElement
     45             (
     46               "ShippingInstructions",
     47               xmlElement("name",SHIPTONAME),
     48               xmlElement("address",ADDRESS),
     49               xmlElement("telephone",PHONE)
     50             ),
     51             xmlElement("SpecialInstructions",SPECIALINSTRUCTIONS),
     52             xmlElement
     53             (
     54               "LineItems",
     55               ( select xmlAgg
     56                        (
     57                          xmlElement
     58                          (
     59                            "LineItem",
     60                            xmlAttributes(LINENO as "ItemNumber"),
     61                            xmlElement("Description",DESCRIPTION),
     62                            xmlElement
     63                            (
     64                              "Part",
     65                              xmlAttributes
     66                              (
     67                                UPC       as "Id",
     68                                QUANTITY  as "Quantity",
     69                                UNITPRICE as "UnitPrice"
     70                              )
     71                            )
     72                          )
     73                        )
     74                    from PURCHASEORDER_LINEITEM l
     75                   where l.REFERENCE = p.REFERENCE
     76               )
     77             )
     78           )
     79      from PURCHASEORDER_TABLE p, PURCHASEORDER_REJECTION r, PURCHASEORDER_SHIPPING s
     80     where r.REFERENCE = p.REFERENCE
     81       and s.REFERENCE = p.REFERENCE
     82  /
    
    View created.
    
    SQL> create trigger insertPurchaseOrder
      2  instead of insert on PURCHASEORDER
      3  begin
      4    insert all
      5      into PURCHASEORDER_TABLE
      6           (
      7             REFERENCE,REQUESTER,USERID,COSTCENTER,SPECIALINSTRUCTIONS
      8           )
      9           values
     10           (
     11             REFERENCE,REQUESTER,USERID,COSTCENTER,SPECIALINSTRUCTIONS
     12           )
     13      into PURCHASEORDER_REJECTION
     14           (
     15             REFERENCE,REJECTEDBY,DATEREJECTED,COMMENTS
     16           )
     17           values
     18           (
     19             REFERENCE,REJECTEDBY,DATEREJECTED,COMMENTS
     20           )
     21      into PURCHASEORDER_SHIPPING
     22           (
     23             REFERENCE,SHIPTONAME,ADDRESS,PHONE
     24           )
     25           values
     26           (
     27             REFERENCE,SHIPTONAME,ADDRESS,PHONE
     28           )
     29    select m.*
     30      from xmlTable
     31           (
     32              '/PurchaseOrder'
     33              passing :new.object_value
     34              columns
     35              REFERENCE           varchar2(30)   path  'Reference',
     36              REQUESTER           varchar2(128)  path  'Requestor',
     37              USERID              varchar2(10)   path  'User',
     38              COSTCENTER          varchar2(4)    path  'CostCenter',
     39                 REJECTEDBY          varchar2(10)   path  '/Rejection/User',
     40              DATEREJECTED   timestamp      path  'Rejection/Date',
     41                 COMMENTS            varchar2(2048) path  'Rejection/Comments',
     42              SHIPTONAME          varchar2(20)   path  'ShippingInstructions/name',
     43              ADDRESS             varchar2(128)  path  'ShippingInstructions/address',
     44              PHONE               varchar2(24)   path  'ShippingInstructions/telephone',
     45              SPECIALINSTRUCTIONS varchar2(2048) path  'SpecialInstructions'
     46           ) m;
     47
     48    insert
     49      into PURCHASEORDER_ACTION
     50           (
     51             REFERENCE,ACTIONEDBY,DATEACTIONED
     52           )
     53    select m.REFERENCE, a.*
     54      from xmlTable
     55           (
     56              '/PurchaseOrder'
     57              passing :new.object_value
     58              columns
     59              REFERENCE       varchar2(30)   path  'Reference',
     60              ACTIONS         xmlType        path  'Actions/Action'
     61           ) m,
     62           xmlTable
     63           (
     64             '/Action'
     65             passing m.ACTIONS
     66             columns
     67             ACTIONEDBY   varchar2(10) path 'User',
     68             DATEACTIONED timestamp    path 'Date'
     69           ) a;
     70
     71    insert
     72      into PURCHASEORDER_LINEITEM
     73           (
     74             REFERENCE,LINENO,UPC,DESCRIPTION,QUANTITY,UNITPRICE
     75           )
     76    select m.REFERENCE, l.*
     77      from xmlTable
     78           (
     79              '/PurchaseOrder'
     80              passing :new.object_value
     81              columns
     82              REFERENCE       varchar2(30)   path  'Reference',
     83              ITEMS           xmlType        path  'LineItems/LineItem'
     84           ) m,
     85           xmlTable
     86           (
     87             '/LineItem'
     88             passing m.ITEMS
     89             columns
     90             LINENO       NUMBER(38)    path '@ItemNumber',
     91             UPC          VARCHAR2(14)  path 'Part/@Id',
     92             DESCRIPTION  VARCHAR2(256) path 'Description',
     93             QUANTITY     NUMBER(12,2)  path 'Part/@Quantity',
     94             UNITPRICE    NUMBER(8,4)   path 'Part/@UnitPrice'
     95           ) l;
     96  end;
     97  /
    
    Trigger created.
    
    SQL> set long 10000 pages 200 lines 150
    SQL> --
    SQL> declare
      2    filelist XMLType;
      3    cursor xml_file_list (FILELIST XMLTYPE) is
      4    select *
      5      from xmltable
      6           (
      7             '//file' passing filelist
      8             Columns
      9             filename varchar2(128) path '/file'
     10           );
     11  begin
     12    filelist := xmltype(bfilename('XMLDIR','2003.xml'),nls_charset_id('AL32UTF8'));
     13    for file in xml_file_list(filelist) loop
     14      insert into PURCHASEORDER values (xmltype(bfilename('XMLDIR',file.filename),nls_charset_id('AL32UTF8')));
     15    end loop;
     16  end;
     17  /
    
    PL/SQL procedure successfully completed.
    
    SQL> select count(*) from PURCHASEORDER
      2  /
    
      COUNT(*)
    ----------
           133
    
    SQL> set long 1000
    SQL> --
    SQL> describe PURCHASEORDER
     Name                                                                                Null?    Type
     ----------------------------------------------------------------------------------- -------- ----------------------------------------------
    ----------
    VIEW of SYS.XMLTYPE(XMLSchema "http://xfiles:8080/home/SCOTT/poSource/xsd/purchaseOrder.xsd" Element "PurchaseOrder") STORAGE Object-relatio
    nal TYPE "PURCHASEORDER_T"
    
    SQL> --
    SQL> select count(*) from PURCHASEORDER
      2  /
    
      COUNT(*)
    ----------
           133
    
    SQL> describe PURCHASEORDER_TABLE
     Name                                                                                Null?    Type
     ----------------------------------------------------------------------------------- -------- ----------------------------------------------
    ----------
     REFERENCE                                                                           NOT NULL VARCHAR2(32)
     REQUESTER                                                                                    VARCHAR2(48)
     USERID                                                                                       VARCHAR2(32)
     COSTCENTER                                                                                   VARCHAR2(3)
     SPECIALINSTRUCTIONS                                                                          VARCHAR2(2048)
    
    SQL> --
    SQL> select count(*) from PURCHASEORDER_TABLE
      2  /
    
      COUNT(*)
    ----------
           133
    
    SQL> describe PURCHASEORDER_ACTION
     Name                                                                                Null?    Type
     ----------------------------------------------------------------------------------- -------- ----------------------------------------------
    ----------
     REFERENCE                                                                                    VARCHAR2(32)
     ACTIONEDBY                                                                                   VARCHAR2(32)
     DATEACTIONED                                                                                 DATE
    
    SQL> --
    SQL> select count(*) from PURCHASEORDER_ACTION
      2  /
    
      COUNT(*)
    ----------
           133
    
    SQL> describe PURCHASEORDER_LINEITEM
     Name                                                                                Null?    Type
     ----------------------------------------------------------------------------------- -------- ----------------------------------------------
    ----------
     REFERENCE                                                                           NOT NULL VARCHAR2(32)
     LINENO                                                                              NOT NULL NUMBER(10)
     UPC                                                                                          VARCHAR2(15)
     DESCRIPTION                                                                                  VARCHAR2(128)
     QUANTITY                                                                                     NUMBER(10)
     UNITPRICE                                                                                    NUMBER(12,2)
    
    SQL> --
    SQL> select count(*) from PURCHASEORDER_LINEITEM
      2  /
    
      COUNT(*)
    ----------
          2236
    
    SQL>