Oracle SQL从CLOB列提取XML数据的实现方法求助

先明确场景（基于常规业务场景补全你的表结构与XML示例） #

假设你的表结构如下：

CREATE TABLE SHOP_SALES (
    ID NUMBER PRIMARY KEY,
    ORDER_XML CLOB
);

插入示例XML数据（CLOB列内容）：

INSERT INTO SHOP_SALES VALUES (1, '<?xml version="1.0" encoding="UTF-8"?>
<Order>
    <ShopName>FreshMart</ShopName>
    <Customer>John Doe</Customer>
    <Products>
        <Product>
            <Name>Bananas</Name>
            <Quantity>5</Quantity>
            <Price>1.2</Price>
        </Product>
        <Product>
            <Name>Apples</Name>
            <Quantity>3</Quantity>
            <Price>0.8</Price>
        </Product>
    </Products>
</Order>');

INSERT INTO SHOP_SALES VALUES (2, '<?xml version="1.0" encoding="UTF-8"?>
<Order>
    <ShopName>GreenGrocery</ShopName>
    <Customer>Jane Smith</Customer>
    <Products>
        <Product>
            <Name>Bananas</Name>
            <Quantity>2</Quantity>
            <Price>1.1</Price>
        </Product>
    </Products>
</Order>');

方案1：用XMLQUERY提取单个节点值（单文档场景） #

如果每个CLOB对应单个XML文档，需要提取ShopName、Customer以及Bananas的数量/价格，可以用XMLQUERY结合XMLTYPE转换CLOB：

SELECT
    ID,
    XMLQUERY('/Order/ShopName/text()' PASSING XMLTYPE(ORDER_XML) RETURNING CONTENT) AS SHOPNAME,
    XMLQUERY('/Order/Customer/text()' PASSING XMLTYPE(ORDER_XML) RETURNING CONTENT) AS CUSTOMER,
    XMLQUERY('/Order/Products/Product[Name="Bananas"]/Quantity/text()' PASSING XMLTYPE(ORDER_XML) RETURNING CONTENT) AS BANANAS_QUANTITY,
    XMLQUERY('/Order/Products/Product[Name="Bananas"]/Price/text()' PASSING XMLTYPE(ORDER_XML) RETURNING CONTENT) AS BANANAS_PRICE
FROM SHOP_SALES;

说明：

• XMLTYPE(ORDER_XML)：将CLOB类型转换为Oracle可识别的XML类型
• /Order/ShopName/text()：XPath表达式，精准定位到目标节点的文本内容
• RETURNING CONTENT：指定返回节点的文本值，避免带XML标签

方案2：用XMLTABLE展开多节点（多Product场景） #

如果需要将XML中的多个Product节点行转列，同时筛选出Bananas相关数据，XMLTABLE是更合适的选择：

SELECT
    s.ID,
    x.SHOPNAME,
    x.CUSTOMER,
    x.PRODUCT_NAME,
    x.QUANTITY AS BANANAS_QUANTITY,
    x.PRICE AS BANANAS_PRICE
FROM SHOP_SALES s,
     XMLTABLE('/Order'
              PASSING XMLTYPE(s.ORDER_XML)
              COLUMNS
                  SHOPNAME VARCHAR2(50) PATH 'ShopName/text()',
                  CUSTOMER VARCHAR2(50) PATH 'Customer/text()',
                  PRODUCTS XMLTYPE PATH 'Products'
     ) o,
     XMLTABLE('/Products/Product'
              PASSING o.PRODUCTS
              COLUMNS
                  PRODUCT_NAME VARCHAR2(50) PATH 'Name/text()',
                  QUANTITY NUMBER PATH 'Quantity/text()',
                  PRICE NUMBER PATH 'Price/text()'
     ) x
WHERE x.PRODUCT_NAME = 'Bananas';

说明：

1. 第一层XMLTABLE：解析根节点Order，提取ShopName、Customer，并将Products节点作为XML类型传递给下一层
2. 第二层XMLTABLE：展开Products下的所有Product节点，提取商品名称、数量、价格
3. WHERE子句筛选出Bananas的记录

为什么不用EXTRACTVALUE？ #

EXTRACTVALUE在Oracle 12c及以后版本已被标记为过时，官方不再维护，且不支持复杂的XML结构解析，建议直接迁移到XMLQUERY/XMLTABLE。

内容的提问来源于stack exchange，提问作者Hisager