如何把Oracle XML数据分解为关系型表

Oracle数据库专家们必须经常提供数据给关系型应用，不管是文档为中心的非结构化XML数据还是数据为中心的结构化XML数据。幸运的是，在Oracle数据库中数据可以被存储为XML形式然后在关系型表中展示出来，也可以存储为关系型表但在XML文档中以节点形式展现。本文中，我们将学到如何把XML数据分解到关系型表中，以及如何把关系型数据发布为XML。点击查看本系列文章第一部分　　把Oracle XML数据分解为关系型表　　ABC Gum公司的数据集成中心以XML文档格式接收源系统购买订单(见下面列表1)。

该公司希望寻求一种简单的解决方案，能在数据库中接受结构良好的XML文档，并存储为关系型表数……

查看全文

我们一直都在努力坚持原创.......请不要一声不吭，就悄悄拿走。

我原创，你原创，我们的内容世界才会更加精彩！

【所有原创内容版权均属TechTarget，欢迎大家转发分享。但未经授权，严禁任何媒体（平面媒体、网络媒体、自媒体等）以及微信公众号复制、转载、摘编或以其他方式进行使用。】

微信公众号

TechTarget

官方微博

TechTarget中国

取消回复

点击查看本系列文章第一部分

　　把Oracle XML数据分解为关系型表

　　ABC Gum公司的数据集成中心以XML文档格式接收源系统购买订单(见下面列表1)。该公司希望寻求一种简单的解决方案，能在数据库中接受结构良好的XML文档，并存储为关系型表数据，以便在数据为下游应用程序被发布为XML之前，能在基于SQL语句的中心维护应用程序中对数据执行控制操作。

　　列表1：购买订单XML文档。

<CustomerName>Joe Smith</CustomerName>

<User>JSMIT</User>

<Description>Bubble Gum</Description>

</Part>

</LineItem>

</Part>

</LineItem>

</LineItems>

</PurchaseOrder>

　　请注意，该XML文档中包含类似主从模式的“LineItem”集合(重复元素)。

　　我们将开始创建一个规范化的数据模型，有两个关系型表(见列表2)来与上面复杂的购买订单结构相对应。第一个表将包含购买订单主信息，第二个表将包含元素详细数据。请注意，在表定义中没有XML结构。

　　列表2：创建表脚本。

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)
                                 );

　　接下来，我们将创建一个存储过程(见列表3)来获取并分解XML数据，导入到我们新创建的关系型表中。这里我们引入了XML结构。我们将定义一个XMLType类型的输入参数，它是一种抽象的本地SQL数据类型，专为XML数据使用。插入语句现在可以把输入看作XML对象，利用XMLTable函数来映射XML数据结构，比如：把元素映射为数据结构，把关系型结构映射为表的列。

　　在我们的例子中，我们有一份结构良好的XML文档，我们选择了不基于XMLType类型的结构，它不限制注册的XML结构和强类型检测。然而，如果性能是一个主要的设计目标，而且需要完整的XML向关系型表的映射，基于结构的方法将能提供更好的查询性能。

　　列表3：创建存储过程。

   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;

　　继续。我们已经准备好加载XML数据了(见列表4)。为了简单起见，我们将给新创建的存储过程传递匿名代码块中定义的一个简单的XML文档。该存储过程调用与传递非XML数据的存储过程没有什么不同。

　　列表4：加载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;

　　现在，我们的XML数据已经加载了，基于SQL的数据中心应用程序可以查询我们的关系型表(见列表5)了。请注意，存储过程调用我们的XML文档把数据分解到了两个表中：一个是一行购买订单信息，另一个是两行明细信息。

　　列表5：关系型查询。

    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

　　由于对于XML文档存在文件大小和复杂性的问题，消化和分解XML文档的代价可能会很昂贵。如果你需要获取一个很大的很复杂的XML文档中的一部分数据，你可以执行部分处理，因而最小化解析的代价。这一功能可以利用“DBMS_XMLSTORE”包来完成，它使得DML操作可以在利用XML输入的关系表上执行。请注意，“DBMS_XMLSTORE.insertXML”存储过程接受一个XML对象，一个样式表和一个目标关系表。下面的代码演示了如何使用该功能(见列表6)。

　　列表6：部分解析。

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

                            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

作者

: Jeff McCormick

翻译

: 冯昀晖

如何把Oracle XML数据分解为关系型表

取消回复

作者

Jeff McCormick

翻译

冯昀晖

相关推荐

OpenWorld18大会：Ellison宣布数据库的搜寻和破坏任务

ObjectRocket着力发展Azure MongoDB服务

数据库和数据仓库的区别在哪儿？

如何使用服务来平衡Oracle RAC 数据库工作负载