如何提取 XML 数据以供关系使用?
问题陈述:
您需要从 XML 文档中提取单个元素值。
解决方案:
Oracle 提供 XMLTABLE 函数，使用 XQuery 和列映射到 Oracle 数据类型来操作 XML 文档。使用 XMLTABLE，我们可以以关系方式识别和使用 XML 文档中的数据元素。
假设，下面的 XML 文档存储在表 tmp_xml_gen 中，我们想从中提取元素。我们有客户详细信息以及订单相关信息。
示例
<?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>
代码
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('//orders/order' PASSING 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
XMLTABLE 函数采用一个或多个 XQuery 表达式来应用于 XMLTYPE 数据的列或表达式。XMLTABLE 的一般形式为:
xmltable( <XQuery to apply>, <Source XMLTYPE column or expression>, <Column definition optionally mapped with XQuery path expression>).
