如何使用XMLTable读取CLOB列中XML的指定节点数据

场景说明 #

数据库某列以CLOB类型存储了如下XML数据：

<?xml version = "1.0" encoding = "UTF-8" standalone = "yes" ?>
   <Supplier>
    <Electronics>
      <Vendor>
        <PoductCode>APP13</PoductCode>
        <Phones>
          <Model>iPhone13</Model>
          <Color>Silver</Color>
          <Storage>128GB</Storage>
        </Phones>   
      </Vendor> 
      <Vendor>
        <PoductCode>APP13P</PoductCode>
        <Phones>
          <Model>iPhone13Pro</Model>
          <Color>Grey</Color>
          <Storage>512GB</Storage>
        </Phones>   
      </Vendor>   
      <Manufacturer>Apple</Manufacturer>
    </Electronics>
    <Electronics>  
      <Vendor>
        <PoductCode>SAMS22</PoductCode>
        <Phones>
          <Model>S22</Model>
          <Color>Black</Color>
          <Storage>256GB</Storage>
        </Phones>   
      </Vendor>      
      <Vendor>
        <PoductCode>SAMS23U</PoductCode>
        <Phones>
          <Model>S23 Ultra</Model>
          <Color>Gold</Color>
          <Storage>512GB</Storage>
        </Phones>   
      </Vendor>   
      <Manufacturer>Samsung</Manufacturer>  
    </Electronics>
    <SupplierName>AlphaLLC</SupplierName>
   </Supplier>

需求 #

使用XMLTable读取该XML的所有节点数据，提取出**产品编码（ProdCode）、型号（Model）、颜色（Color）、存储容量（Storage）、制造商（Manufact）、供应商名称（SupplierName）**字段，期望得到如下结构化输出：

解决方案（Oracle数据库为例） #

假设存储该CLOB字段的表名为supplier_data，字段名为xml_clob，可使用以下SQL语句通过XMLTable解析数据：

SELECT
    x.prod_code AS "产品编码",
    x.model AS "型号",
    x.color AS "颜色",
    x.storage AS "存储容量",
    e.manufacturer AS "制造商",
    s.supplier_name AS "供应商名称"
FROM
    supplier_data sd,
    XMLTable(
        '/Supplier/Electronics'
        PASSING XMLTYPE(sd.xml_clob)
        COLUMNS
            manufacturer VARCHAR2(50) PATH 'Manufacturer',
            vendors XMLTYPE PATH 'Vendor'
    ) e,
    XMLTable(
        '/Vendor'
        PASSING e.vendors
        COLUMNS
            prod_code VARCHAR2(20) PATH 'PoductCode',
            model VARCHAR2(50) PATH 'Phones/Model',
            color VARCHAR2(20) PATH 'Phones/Color',
            storage VARCHAR2(20) PATH 'Phones/Storage'
    ) x,
    XMLTable(
        '/Supplier'
        PASSING XMLTYPE(sd.xml_clob)
        COLUMNS
            supplier_name VARCHAR2(50) PATH 'SupplierName'
    ) s;

逻辑说明 #

1. 第一层XMLTable遍历<Electronics>节点，提取制造商信息及对应的<Vendor>节点集合；
2. 第二层XMLTable遍历每个<Vendor>节点，提取产品编码、手机型号、颜色和存储容量；
3. 第三层XMLTable提取根节点下的供应商名称，确保每条数据关联正确的供应商信息。

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