Oracle操作XML各种场景介绍(2)

二 如何存储XML文件内容：

我们知道Oracle 中xmltype数据类型用来存储XML内容。下面例子中介绍如何将系统中XML文件内容加载至

含有XMLTYPE类型的表中。

CREATE TABLE xml_table OF XMLTYPE;

INSERT INTO xml_table
    VALUES(XMLTYPE(bfilename('XML_DIR','PurchaseOrder.xml'),nls_charset_id('AL32UTF8')));
   
    SELECT x.sys_nc_rowinfo$.getstringval() FROM xml_table x;
   
CREATE TABLE table_with_xml_column(filename VARCHAR2(64), xml_document XMLTYPE);

INSERT INTO table_with_xml_column
    VALUES ('PurchaseOrder.xml',XMLType(bfilename('XML_DIR', 'PurchaseOrder.xml'),nls_charset_id('AL32UTF8')));
   
SELECT x.xml_document.getCLOBVal() FROM table_with_xml_column x;

PurchaseOrder.xml内容：

<PurchaseOrder xmlns:xsi=""
  xsi:noNamespaceSchemaLocation=
    ":8080/source/schemas/poSource/xsd/purchaseOrder.xsd">
  <Reference>SBELL-2002100912333601PDT</Reference>
  <Actions>
    <Action>
      <User>SVOLLMAN</User>
    </Action>
  </Actions>
  <Reject/>
  <Requestor>Sarah J. Bell</Requestor>
  <User>SBELL</User>
  <CostCenter>S30</CostCenter>
  <ShippingInstructions>
    <name>Sarah J. Bell</name>
    <address>400 Oracle Parkway
      Redwood Shores
      CA
      94065
      USA</address>
    <telephone>650 506 7400</telephone>
  </ShippingInstructions>
  <SpecialInstructions>Air Mail</SpecialInstructions>
  <LineItems>
    <LineItem ItemNumber="1">
      <Description>A Night to Remember</Description>
      <Part UnitPrice="39.95" Quantity="2"/>
    </LineItem>
    <LineItem ItemNumber="2">
      <Description>The Unbearable Lightness Of Being</Description>
      <Part UnitPrice="29.95" Quantity="2"/>
    </LineItem>
    <LineItem ItemNumber="3">
      <Description>Sisters</Description>
      <Part UnitPrice="29.95" Quantity="4"/>
    </LineItem>
  </LineItems>
</PurchaseOrder>