0 Replies Latest reply: Aug 23, 2006 2:31 PM by mdrake-Oracle RSS

    How to create XML from relational tables based on an XML Schema ?

    mdrake-Oracle
      There is no automated way in Oracle XML DB to define an automatic mapping between a set of columns in some existing relational tables and the elements and attributres defined by an XML Schema.

      However it is easy solve this problem by using the SQL/XML operators (XMLAGG, XMLELEMENT, XMLFOREST, XMLATTRIBUTES, etc) to generate XML documents that are compliant with an XML Schema directly from a SQL statement.

      If the XML Schema is registered with Oracle XML DB and the appropraite Schema Location information is added into the generated document using XMLAttributes then it becomes very easy to ensure that the generated documents are valid.

      The following example show an easy way to do this by creating an XML View that contains the documents to be validated.
      SQL> drop table PURCHASEORDER_LINEITEM
        2  /
      
      Table dropped.
      
      SQL> drop table PURCHASEORDER_REJECTION
        2  /
      
      Table dropped.
      
      SQL> drop table PURCHASEORDER_SHIPPING
        2  /
      
      Table dropped.
      
      SQL> drop TABLE PURCHASEORDER_ACTION
        2  /
      
      Table dropped.
      
      SQL> drop TABLE PURCHASEORDER_TABLE
        2  /
      
      Table dropped.
      
      SQL> create table PURCHASEORDER_TABLE
        2  (
        3   REFERENCE                                          VARCHAR2(28),
        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(14),
        8   DESCRIPTION                                        VARCHAR2(128),
        9   QUANTITY                                           NUMBER(10),
       10   UNITPRICE                                          NUMBER(12,2)
       11  )
       12  /
      
      Table created.
      
      SQL> insert into PURCHASEORDER_TABLE values ('SMCCAIN-20030109123335470PDT','Samuel B. McCain','SMCCAIN','A10','Courier')
        2  /
      
      1 row created.
      
      SQL> insert into PURCHASEORDER_ACTION values ('SMCCAIN-20030109123335470PDT','SVOLLMAN',NULL)
        2  /
      
      1 row created.
      
      SQL> insert into PURCHASEORDER_SHIPPING values ('SMCCAIN-20030109123335470PDT','Samuel B. McCain','800 Bridge Parkway,Redwood Shores,CA,9406
      5,USA','650 506 7800')
        2  /
      
      1 row created.
      
      SQL> insert into PURCHASEORDER_REJECTION values ('SMCCAIN-20030109123335470PDT',null,null,null)
        2  /
      
      1 row created.
      
      SQL> insert into PURCHASEORDER_LINEITEM values ('SMCCAIN-20030109123335470PDT','1','715515010320','Life of Brian - Monty Python''s','2','39.
      95')
        2  /
      
      1 row created.
      
      SQL> insert into PURCHASEORDER_LINEITEM values ('SMCCAIN-20030109123335470PDT','2','37429145227','The Night Porter','2','29.95')
        2  /
      
      1 row created.
      
      SQL> insert into PURCHASEORDER_LINEITEM values ('SMCCAIN-20030109123335470PDT','3','37429128121','Oliver Twist','1','39.95')
        2  /
      
      1 row created.
      
      SQL> insert into PURCHASEORDER_LINEITEM values ('SMCCAIN-20030109123335470PDT','4','715515012720','Notorious','4','39.95')
        2  /
      
      1 row created.
      
      SQL> insert into PURCHASEORDER_LINEITEM values ('SMCCAIN-20030109123335470PDT','5','715515012928','In the Mood for Love','3','39.95')
        2  /
      
      1 row created.
      
      SQL> insert into PURCHASEORDER_LINEITEM values ('SMCCAIN-20030109123335470PDT','6','37429130926','Alphaville','2','29.95')
        2  /
      
      1 row created.
      
      SQL> insert into PURCHASEORDER_LINEITEM values ('SMCCAIN-20030109123335470PDT','7','37429166529','General Idi Amin Dada','4','29.95')
        2  /
      
      1 row created.
      
      SQL> insert into PURCHASEORDER_LINEITEM values ('SMCCAIN-20030109123335470PDT','8','715515012928','In the Mood for Love','3','39.95')
        2  /
      
      1 row created.
      
      SQL> insert into PURCHASEORDER_LINEITEM values ('SMCCAIN-20030109123335470PDT','9','715515009423','Flesh for Frankenstein','3','39.95')
        2  /
      
      1 row created.
      
      SQL> insert into PURCHASEORDER_LINEITEM values ('SMCCAIN-20030109123335470PDT','10','715515008976','The Killer','1','39.95')
        2  /
      
      1 row created.
      
      SQL> insert into PURCHASEORDER_LINEITEM values ('SMCCAIN-20030109123335470PDT','11','37429167922','Ballad of a Soldier','2','29.95')
        2  /
      
      1 row created.
      
      SQL> insert into PURCHASEORDER_LINEITEM values ('SMCCAIN-20030109123335470PDT','12','37429158623','Ordet','2','0')
        2  /
      
      1 row created.
      
      SQL> var schemaPath varchar2(256)
      SQL> --
      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 or replace view PURCHASEORDER_XML
        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
       19             (
       20               "Actions",
       21               ( select xmlAgg
       22                        (
       23                          xmlElement
       24                          (
       25                            "Action",
       26                            xmlElement("User",ACTIONEDBY),
       27                            case
       28                              when DATEACTIONED is not null
       29                              then xmlElement("Date",DATEACTIONED)
       30                            end
       31                          )
       32                        )
       33                   from PURCHASEORDER_ACTION a
       34                  where a.REFERENCE = p.REFERENCE
       35               )
       36             ),
       37             xmlElement
       38             (
       39               "Reject",
       40                  xmlForest
       41                  (
       42                    REJECTEDBY as "User",
       43                 DATEREJECTED as "Date",
       44                    COMMENTS as "Comments"
       45                  )
       46             ),
       47             xmlElement("Requestor",REQUESTER),
       48             xmlElement("User",USERID),
       49             xmlElement("CostCenter",COSTCENTER),
       50             xmlElement
       51             (
       52               "ShippingInstructions",
       53               xmlElement("name",SHIPTONAME),
       54               xmlElement("address",ADDRESS),
       55               xmlElement("telephone",PHONE)
       56             ),
       57             xmlElement("SpecialInstructions",SPECIALINSTRUCTIONS),
       58             xmlElement
       59             (
       60               "LineItems",
       61               ( select xmlAgg
       62                        (
       63                          xmlElement
       64                          (
       65                            "LineItem",
       66                            xmlAttributes(LINENO as "ItemNumber"),
       67                            xmlElement("Description",DESCRIPTION),
       68                            xmlElement
       69                            (
       70                              "Part",
       71                              xmlAttributes
       72                              (
       73                                UPC       as "Id",
       74                                QUANTITY  as "Quantity",
       75                                UNITPRICE as "UnitPrice"
       76                              )
       77                            )
       78                          )
       79                        )
       80                    from PURCHASEORDER_LINEITEM l
       81                   where l.REFERENCE = p.REFERENCE
       82               )
       83             )
       84           )
       85      from PURCHASEORDER_TABLE p, PURCHASEORDER_REJECTION r, PURCHASEORDER_SHIPPING s
       86     where r.REFERENCE = p.REFERENCE
       87       and s.REFERENCE = p.REFERENCE
       88  /
      
      View created.
      
      SQL> set long 10000 pages 0 lines 140
      SQL> --
      SQL> select x.object_value.extract('/*')
        2    from PURCHASEORDER_XML x
        3  /
      <PurchaseOrder xsi:noNamespaceSchemaLocation="http://xfiles:8080/home/SCOTT/poSource/xsd/purchaseOrder.xsd" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
        <Reference>SMCCAIN-20030109123335470PDT</Reference>
        <Actions>
          <Action>
            <User>SVOLLMAN</User>
          </Action>
        </Actions>
        <Reject/>
        <Requestor>Samuel B. McCain</Requestor>
        <User>SMCCAIN</User>
        <CostCenter>A10</CostCenter>
        <ShippingInstructions>
          <name>Samuel B. McCain</name>
          <address>800 Bridge Parkway,Redwood Shores,CA,94065,USA</address>
          <telephone>650 506 7800</telephone>
        </ShippingInstructions>
        <SpecialInstructions>Courier</SpecialInstructions>
        <LineItems>
          <LineItem ItemNumber="1">
            <Description>Life of Brian - Monty Python&apos;s</Description>
            <Part Id="715515010320" Quantity="2" UnitPrice="39.95"/>
          </LineItem>
          <LineItem ItemNumber="2">
            <Description>The Night Porter</Description>
            <Part Id="37429145227" Quantity="2" UnitPrice="29.95"/>
          </LineItem>
          <LineItem ItemNumber="3">
            <Description>Oliver Twist</Description>
            <Part Id="37429128121" Quantity="1" UnitPrice="39.95"/>
          </LineItem>
          <LineItem ItemNumber="4">
            <Description>Notorious</Description>
            <Part Id="715515012720" Quantity="4" UnitPrice="39.95"/>
          </LineItem>
          <LineItem ItemNumber="5">
            <Description>In the Mood for Love</Description>
            <Part Id="715515012928" Quantity="3" UnitPrice="39.95"/>
          </LineItem>
          <LineItem ItemNumber="6">
            <Description>Alphaville</Description>
            <Part Id="37429130926" Quantity="2" UnitPrice="29.95"/>
          </LineItem>
          <LineItem ItemNumber="7">
            <Description>General Idi Amin Dada</Description>
            <Part Id="37429166529" Quantity="4" UnitPrice="29.95"/>
          </LineItem>
          <LineItem ItemNumber="8">
            <Description>In the Mood for Love</Description>
            <Part Id="715515012928" Quantity="3" UnitPrice="39.95"/>
          </LineItem>
          <LineItem ItemNumber="9">
            <Description>Flesh for Frankenstein</Description>
            <Part Id="715515009423" Quantity="3" UnitPrice="39.95"/>
          </LineItem>
          <LineItem ItemNumber="10">
            <Description>The Killer</Description>
            <Part Id="715515008976" Quantity="1" UnitPrice="39.95"/>
          </LineItem>
          <LineItem ItemNumber="11">
            <Description>Ballad of a Soldier</Description>
            <Part Id="37429167922" Quantity="2" UnitPrice="29.95"/>
          </LineItem>
          <LineItem ItemNumber="12">
            <Description>Ordet</Description>
            <Part Id="37429158623" Quantity="2" UnitPrice="0"/>
          </LineItem>
        </LineItems>
      </PurchaseOrder>
      
      
      SQL> begin
        2    for x in (select object_value from PURCHASEORDER_XML) loop
        3      x.object_value.schemaValidate();
        4    end loop;
        5  end;
        6  /
      
      PL/SQL procedure successfully completed.
      
      SQL>