This discussion is archived
0 Replies Latest reply: Aug 23, 2006 12:31 PM by mdrake RSS

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

mdrake Expert
Currently Being Moderated
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>