Tip

Oracle XML capabilities in Oracle Database 11gR2, part 3

This is part 3 of a three-part series on Oracle XML capabilities in Oracle Database 11gR2. In part 1, McCormick introduced the reader to XML capabilities in Oracle Database and set up a scenario

    Requires Free Membership to View

    Login

in which a business wants to pull XML documents into its database. Part 2 detailed how that XML data could be used and queried to gain business information. In part 3, McCormick will explain how to shred XML data into relational tables and publish relational data as XML.

Oracle database professionals must often expose data for relational applications, whether it be document-centric for unstructured XML or data-centric for structured XML. Fortunately, a single instance of data can be stored as XML and then either exposed as rows in relational tables or stored relationally and exposed as nodes in an XML document. In this article, we will learn how to shred XML data into relational tables and publish relational data as XML.

Shredding Oracle XML data into relational tables  

The ABC Gum company’s data integration hub receives source system purchase orders as XML documents (Listing 1).  The company is looking for a simple solution within the database to accept well-formed XML documents and store the data relationally in order for its SQL-based hub maintenance application to perform data manipulation operations before the data is published as XML to downstream applications.

Listing 1. Purchase Order XML Document

 
 

  <PurchaseOrder>

    <OrderNumber>11111</OrderNumber>

    <OrderDate>2010-01-15</OrderDate>

    <CustomerName>Joe Smith</CustomerName>

    <User>JSMIT</User>

    <SpecialInstructions>Air Mail</SpecialInstructions>

    <LineItems>

      <LineItem ItemNumber="1">

        <Description>Bubble Gum</Description>

        <Part Id="7155" UnitPrice="19.95" Quantity="5">

        </Part>

      </LineItem>

      <LineItem ItemNumber="2">

        <Description>Blow Pops</Description>

        <Part Id="3742" UnitPrice="15.95" Quantity="2">

        </Part>

      </LineItem>

    </LineItems>

  </PurchaseOrder>

 
 

Notice the XML document contains a LineItem collection (repeating elements) analogous to a master-detail model.

We’ll begin by creating a normalized data model with two relational tables (Listing 2) to match the complex purchase order structure. The first table will contain master purchase order information and the second will contain detailed line item data. Notice there are no XML constructs in the table definitions.

Listing 2. Create Tables

create table abc_purchase_order_table (order_number         number(5) primary key

                                      ,order_date           date

                                      ,customer_name        varchar2(30)

                                      ,userid               varchar2(10)

                                      ,special_instructions varchar2(80)

                                      );

 

create table abc_lineitem_table  (order_number         number(5)

                                 ,foreign key (order_number)

                                    references abc_purchase_order_table (order_number) on delete cascade

                                 ,itemno               number(10)

                                    ,primary key (order_number, itemno)

                                 ,description          varchar2(25)

                                 ,partno               varchar2(14)

                                 ,unitprice            number(8,4)

                                 ,quantity             number(12,2)

                                 );
 

Next, we’ll create a procedure (Listing 3) to ingest and shred XML data into our newly created relational tables. Here is where we introduce XML constructs. We’ll define an input parameter as XMLType, an abstract native SQL data type for XML data. Insert statements can now reference the input as an XML object and use an XMLTable function to map XML data constructs, such as elements, to relational constructs, such as columns. 

In our use case, in which we have a well-formed XML document, we have chosen a non-schema based XMLType view that is not constrained to a registered XML schema and stronger typing. However, if performance is the primary design goal and complete XML-to-relational mapping is required, a schema-based approach will offer the best query performance. 

Listing 3. Create Procedure

create or replace procedure shred_abc_purchase_order(p_abc_purchase_order xmltype) as

begin

  insert into abc_purchase_order_table (order_number ,order_date ,customer_name ,userid ,special_instructions)

    select * from xmltable('$po/PurchaseOrder' passing p_abc_purchase_order AS "po"

                  columns  order_number         number  (5)    path 'OrderNumber'

                          ,order_date           date           path 'OrderDate'

                          ,customer_name        varchar2(30)   path 'CustomerName'

                          ,userid               varchar2(10)   path 'User'

                          ,special_instructions varchar2(80)   path 'SpecialInstructions'

                          );

  insert into abc_lineitem_table (order_number, itemno, description, partno, quantity, unitprice)

    select t.order_number, li.itemno, li.description, li.partno, li.quantity, li.unitprice

    from xmltable('$po/PurchaseOrder' passing p_abc_purchase_order as "po"

                  columns  order_number number(5) path 'OrderNumber'

                          ,lineitem xmltype path 'LineItems/LineItem'

                 ) t

        ,xmltable('LineItem' passing t.lineitem

                  columns  itemno      number(10)   path '@ItemNumber'

                          ,description varchar2(25) path 'Description'

                          ,partno      varchar2(14) path 'Part/@Id'

                          ,unitprice   number(12,2) path 'Part/@UnitPrice'

                          ,quantity    number(12,2) path 'Part/@Quantity'

                 ) li;

  commit;

end;
 

Continuing on, we’re ready to load our XML data (Listing 4). For simplicity’s sake, we’ll pass a single XML document defined within an anonymous block to our newly created procedure. The procedure call is no different from passing non-XML data. 

Listing 4. Load XML

declare

  v_abc_purchase_order xmltype := xmltype('<PurchaseOrder>

                                             <OrderNumber>11111</OrderNumber>

                                             <OrderDate>2010-01-15</OrderDate>

                                             <CustomerName>Joe Smith</CustomerName>

                                             <User>JSMIT</User>

                                             <SpecialInstructions>Air Mail</SpecialInstructions>

                                             <LineItems>

                                               <LineItem ItemNumber="1">

                                                 <Description>Bubble Gum</Description>

                                                 <Part Id="7155" UnitPrice="19.95" Quantity="5">

                                                 </Part>

                                               </LineItem>

                                               <LineItem ItemNumber="2">

                                                 <Description>Blow Pops</Description>

                                                 <Part Id="3742" UnitPrice="15.95" Quantity="2">

                                                 </Part>

                                               </LineItem>

                                             </LineItems>

                                           </PurchaseOrder>');

begin

  shred_abc_purchase_order(p_abc_purchase_order => v_abc_purchase_order);

end;
 

Now that our XML data has been loaded, the SQL-based data hub application can query our relational tables (Listing 5). Notice that with one procedure call our XML document has been shredded into two tables: a single row of purchase order information and two line item rows.      

Listing 5. Relational Queries

select * from abc_purchase_order_table;

 

ORDER_NUMBER  ORDER_DATE  CUSTOMER_NAME   USERID  SPECIAL_INSTRUCTIONS

------------- ----------- --------------- ------- --------------------

11111         15-JAN-10   Joe Smith       JSMIT   Air Mail

 

select * from abc_lineitem_table;

 

ORDER_NUMBER ITEMNO DESCRIPTION PARTNO UNITPRICE QUANTITY

------------ ------ ----------- ------ --------- --------

11111        1      Bubble Gum  7155   19.95     5

11111        2      Blow Pops   3742   15.95     2
 

Depending on the size and complexity of XML documents, ingesting and shredding can be expensive. In situations in which you only need a subset of data within a large, complex XML document, you can perform a partial ingestion, thereby minimizing the cost of translation. This is accomplished using the DBMS_XMLSTORE package, which enables DML operations to be performed on relational tables using XML input. Notice the DBMS_XMLSTORE.insertXML procedure accepts an XML object, a style sheet and a target relational table. The following code demonstrates how this is done (Listing 6). 

Listing 6. Partial Ingestion

-- Step 1: Create table with subsetted columns

 

create table abc_partial_po_table (ordernumber number, specialinstructions varchar2 (100));

 

-- Step 2: Create procedure

 

create or replace procedure abc_insert_xml2relational(p_xml_in xmltype,

                                                      p_xsl_in xmltype,

                                                      p_table in varchar2 ) as

  v_context dbms_xmlstore.ctxtype;

  v_rows number;

begin

  -- open a new context

  v_context := dbms_xmlstore.newcontext(p_table);

  -- Perform the ingestion

  v_rows := dbms_xmlstore.insertxml(v_context, xmltype.transform(p_xml_in, p_xsl_in));

  -- close the context

  dbms_xmlstore.closecontext(v_context);

end;

/

 

-- Step 3: Execute procedure

 

declare

  v_xml xmltype := xmltype('<PurchaseOrder>

                               <OrderNumber>11111</OrderNumber>

                               <OrderDate>2010-01-15</OrderDate>

                               <CustomerName>Joe Smith</CustomerName>

                               <User>SBELL</User>

                               <SpecialInstructions>Air Mail</SpecialInstructions>

                               <LineItems>

                                 <LineItem ItemNumber="1">

                                   <Description>Bubble Gum</Description>

                                   <Part Id="7155" UnitPrice="19.95" Quantity="5">

                                   </Part>

                                 </LineItem>

                                 <LineItem ItemNumber="2">

                                   <Description>Blow Pops</Description>

                                   <Part Id="3742" UnitPrice="15.95" Quantity="2">

                                   </Part>

                                 </LineItem>

                               </LineItems>

                             </PurchaseOrder>'

                          );

  v_xsl xmltype := xmltype('<?xml version="1.0"?>

                            <xsl:stylesheet

                                 xmlns:xsl="http://www.w3.org/1999/XSL/Transform"

                            version="1.0">

                            <xsl:template match="/">

                                <ROWSET>

                                  <ROW>

                                    <ORDERNUMBER>

                                      <xsl:value-of select="PurchaseOrder/OrderNumber" />

                                    </ORDERNUMBER>

                                    <SPECIALINSTRUCTIONS>

                                      <xsl:value-of select="PurchaseOrder/SpecialInstructions" />

                                    </SPECIALINSTRUCTIONS>

                                  </ROW>

                                </ROWSET>

                              </xsl:template>

                            </xsl:stylesheet>'

                          );

begin

  abc_insert_xml2relational(v_xml, v_xsl, 'ABC_PARTIAL_PO_TABLE');

end;

/

 

-- Step 4: Query table

 

SELECT * FROM abc_partial_po_table;

 

ORDERNUMBER            SPECIALINSTRUCTIONS                                                                                 

---------------------- --------------------                                                                                                

11111                  Air Mail 
 

Publishing relational data as XML

The final requirement of our use case is to abstract and generate XML data from relational tables. 

Abstraction is achieved through the use of XMLType views, essentially a stored query with results materialized at SQL execution. XMLType views allow document-centric applications to query underlying relational structures as virtual XML documents. You have several choices for generating XML data. A common approach is to use object types, object views, and the Oracle SQL function sys_XMLGen. I prefer to use SQL/XML constructs (Listing 7) or XQuery in combination with XMLTable or XMLQuery functions or both. Both allow me to show the query plan with an optimal execution path. 

Listing 7. Create XMLType View

create or replace view abc_purchase_order_xml_view of XMLType

with OBJECT ID (XMLCast(XMLQuery('/PurchaseOrder/OrderNumber'Passing OBJECT_VALUE Returning Content)as BINARY_DOUBLE))

as

Select XMLElement(NOENTITYESCAPING "PurchaseOrder"

                 ,XMLForest(po.order_number         as "OrderNumber"

                           ,po.order_date           as "OrderDate"

                           ,po.customer_name        as "CustomerName"

                           ,po.userid               as "User"

                           ,po.special_instructions as "SpecialInstructions"

                           )

                 ,XMLElement(NOENTITYESCAPING "LineItems"

                            ,(SELECT XMLAgg(XMLElement(NOENTITYESCAPING "LineItem"

                                                      ,XMLAttributes(NOENTITYESCAPING li.itemno  as "ItemNumber")

                                                      ,XMLElement(NOENTITYESCAPING "Description", li.description)

                                                      ,XMLElement(NOENTITYESCAPING "Part"

                                                                 ,XMLAttributes(li.partno    as "Id"
                                                                               ,li.unitprice as "UnitPrice"
                                                                               ,li.quantity  as "Quantity" 
                                                                               )

                                                                 )

                                                      )

                                           )

                                from abc_lineitem_table li

                                where li.order_number = po.order_number

                             )

                            )

                 )

  as "POXML"

  from abc_purchase_order_table po;
 

Now it’s time to generate our XML data (Listing 8). For consumption by XML applications, we can publish the XML data as an unformatted string. For times when you require structured output, the XMLSerialize function can present XML in a format suitable for viewing or printing. Notice in our example the XMLSerialize function has formatted the end tag of the “Part” element as “/>” rather than “</Part>”. Both are considered well-formed XML, so there isn’t any technical difference.

Listing 8. Generate XML Data

 

-- Unformatted

 

select OBJECT_VALUE as "Purchase Order" from abc_purchase_order_xml_view;

 

Purchase Order

<PurchaseOrder><OrderNumber>11111</OrderNumber><OrderDate>2010-01-15</OrderDate><CustomerName>Joe Smith</CustomerName>

<User>JSMIT</User><SpecialInstructions>Air Mail</SpecialInstructions><LineItems><LineItem ItemNumber="1">

<Description>Bubble Gum</Description><Part Id="7155" UnitPrice="19.95" Quantity="5"></Part></LineItem>

<LineItem ItemNumber="2"><Description>Blow Pops</Description><Part Id="3742" UnitPrice="15.95" Quantity="2">

</Part></LineItem></LineItems></PurchaseOrder>

 

-- Formatted

 

select XMLSerialize(Content OBJECT_VALUE as CLOB indent) as "Purchase Order"

  from abc_purchase_order_xml_view;

 

Purchase Order

-----------------------------------------------------

<PurchaseOrder>

  <OrderNumber>11111</OrderNumber>

  <OrderDate>2010-01-15</OrderDate>

  <CustomerName>Joe Smith</CustomerName>

  <User>JSMIT</User>

  <SpecialInstructions>Air Mail</SpecialInstructions>

  <LineItems>

    <LineItem ItemNumber="1">

      <Description>Bubble Gum</Description>

      <Part Id="7155" UnitPrice="19.95" Quantity="5"/>

    </LineItem>

    <LineItem ItemNumber="2">

      <Description>Blow Pops</Description>

      <Part Id="3742" UnitPrice="15.95" Quantity="2"/>

    </LineItem>

  </LineItems>

</PurchaseOrder>

 

 
 

Jeff McCormick is an architecture director at a major health service company and president of the Connecticut Oracle User Group. McCormick has worked in IT for more than 20 years as a data and infrastructure architect/administrator. He holds several Oracle, Microsoft and Sybase professional certifications.

This was first published in May 2011

Join the conversationComment

Share
Comments

    Results

    Contribute to the conversation

    All fields are required. Comments will appear at the bottom of the article.

    Disclaimer: Our Tips Exchange is a forum for you to share technical advice and expertise with your peers and to learn from other enterprise IT professionals. TechTarget provides the infrastructure to facilitate this sharing of information. However, we cannot guarantee the accuracy or validity of the material submitted. You agree that your use of the Ask The Expert services and your reliance on any questions, answers, information or other materials received through this Web site is at your own risk.

    Back to top