2 Replies Latest reply: Aug 6, 2006 11:03 AM by mdrake RSS

    Relational access to XML Content

    mdrake
      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
          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
            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>