如何从Oracle中的XML文档提取关键XML元素？

问题陈述：

您需要从XML文档中提取元素值的一部分/子集。

解：

我们可以利用支持XMLTYPE数据类型的Oracle EXTRACT函数。EXTARCT函数通过将结果作为XMLTYPE值返回来提供保留XML元素名称和属性构造的功能。

假设，我们在tmp_xml_gen表中的XML下面，我们要从中提取客户名称。

示例

<?xml version="1.0"?>
<customers>
  <customer>
    <customer_id>134</customer_id>
    <email>taylor.cauchon@internalmail</email>
    <name>Taylor Cauchon</name>
    <orders>
      <order>
        <order_id>921</order_id>
        <status>COMPLETE</status>
      </order>
      <order>
        <order_id>485</order_id>
        <status>COMPLETE</status>
      </order>
      <order>
        <order_id>1528</order_id>
        <status>COMPLETE</status>
      </order>
      <order>
        <order_id>1672</order_id>
        <status>COMPLETE</status>
      </order>
      <order>
        <order_id>1319</order_id>
        <status>COMPLETE</status>
      </order>
    </orders>
  </customer>
</customers>

SQL：从XML文档中提取客户名称值

SELECT extract (result, '/customers/customer/customer_id').getclobval() FROM tmp_xml_gen ;

输出：SQL语句中的行

<customer_id>134</customer_id>
<customer_id>135</customer_id>
<customer_id>136</customer_id>
<customer_id>137</customer_id>
<customer_id>138</customer_id>
<customer_id>139</customer_id>
<customer_id>140</customer_id>
<customer_id>141</customer_id>
<customer_id>142</customer_id>
<customer_id>143</customer_id>
<customer_id>144</customer_id>
<customer_id>145</customer_id>
<customer_id>146</customer_id>
<customer_id>147</customer_id>

注意： XML上的EXTRACT函数操作与其他数据类型一起使用时，其行为有所不同。在日期或时间上使用时，EXTRACT将仅返回请求的日期或时间的一部分，例如月份。如果要求使用月份，则EXTRACT不会返回日期或时间，也不会返回任何“较小”的度量值，例如日，时等。当对XMLTYPE数据进行操作时，EXTRACT返回等于或低于匹配元素嵌套级别的所有内容。

我们可以使用XMLTABLE提取所需的信息。

码

WITH cust AS
-- Customer details
      (SELECT xt.customer_id,
              xt.email,
              xt.name,
              x.result
         FROM tmp_xml_gen x,
         XMLTABLE('/customers/customer' PASSING x.RESULT
                   COLUMNS
                   customer_id VARCHAR2(10)  PATH 'customer_id',
                         email VARCHAR2(255) PATH 'email',
                          name VARCHAR2(255) PATH 'name') xt),
-- Order details
     ord AS
     (SELECT customer_id,
             email,
             name,
             xt2.*
        FROM cust t,
        XMLTABLE('//订单/订单的通过t.result-
                 COLUMNS order_id VARCHAR2(4)  PATH 'order_id',
                           status VARCHAR2(10) PATH 'status' ) xt2)
SELECT * FROM ord;

输出：SQL上面的行很少

134 taylor.cauchon@internalmail Taylor Cauchon  921     COMPLETE
134 taylor.cauchon@internalmail Taylor Cauchon  485     COMPLETE
134 taylor.cauchon@internalmail Taylor Cauchon  1528    COMPLETE
134 taylor.cauchon@internalmail Taylor Cauchon  1672    COMPLETE
134 taylor.cauchon@internalmail Taylor Cauchon  1319    COMPLETE
135 marc.domanski@internalmail  Marc Domanski   50      COMPLETE
135 marc.domanski@internalmail  Marc Domanski   99      COMPLETE
135 marc.domanski@internalmail  Marc Domanski   142     COMPLETE
135 marc.domanski@internalmail  Marc Domanski   195     COMPLETE