Oracle环境下如何提取XML元素及属性的XPATH路径与对应值？

嘿，我来帮你搞定这个需求！要同时列出XML元素的XPATH、属性的XPATH以及对应的值，我们可以在原有递归提取元素路径的逻辑基础上，新增属性遍历的处理，用UNION ALL把两类结果合并起来。下面是具体的实现方案：

Oracle Setup：创建测试数据 #

首先我们先准备一张存储XML的测试表，插入带元素和属性的示例XML：

CREATE TABLE xml_docs (
  doc_id NUMBER PRIMARY KEY,
  xml_content XMLTYPE
);

INSERT INTO xml_docs VALUES (
  1,
  XMLTYPE('<?xml version="1.0"?>
<root>
  <person id="100" status="active">
    <name>John Doe</name>
    <age>35</age>
    <address type="home">
      <street>123 Main St</street>
      <city>Anytown</city>
    </address>
  </person>
  <person id="101" status="inactive">
    <name>Jane Smith</name>
    <age>28</age>
  </person>
</root>')
);
COMMIT;

核心查询语句 #

这个查询会分别提取元素和属性的路径与值，再合并结果：

WITH xml_paths AS (
  -- 第一部分：提取所有XML元素的XPATH和值
  SELECT
    doc_id,
    '/' || REPLACE(
      XMLSerialize(CONTENT x.column_value.getrootelement() || SYS_CONNECT_BY_PATH(x.column_value.getname(), '/') 
      AS VARCHAR2(1000)),
      '//', '/'
    ) AS xpath,
    x.column_value.getstringval() AS value,
    'ELEMENT' AS node_type
  FROM xml_docs d,
       XMLTable('/descendant::*' PASSING d.xml_content) x
  CONNECT BY PRIOR x.column_value IS CHILD OF x.column_value
  START WITH x.column_value.getrootelement() IS NOT NULL
  
  UNION ALL
  
  -- 第二部分：提取所有XML属性的XPATH和值
  SELECT
    doc_id,
    '/' || REPLACE(
      XMLSerialize(CONTENT a.element.getrootelement() || SYS_CONNECT_BY_PATH(a.element.getname(), '/') || '@' || a.attribute_name 
      AS VARCHAR2(1000)),
      '//', '/'
    ) AS xpath,
    a.attribute_value AS value,
    'ATTRIBUTE' AS node_type
  FROM xml_docs d,
       XMLTable('/descendant::*' PASSING d.xml_content) e,
       XMLTable(
         'for $attr in ./@* return 
          <attr>
            <element>{$node()}</element>
            <name>{local-name($attr)}</name>
            <value>{string($attr)}</value>
          </attr>'
         PASSING e.column_value AS "node()"
         COLUMNS
           element XMLTYPE PATH 'element',
           attribute_name VARCHAR2(100) PATH 'name',
           attribute_value VARCHAR2(1000) PATH 'value'
       ) a
  CONNECT BY PRIOR a.element IS CHILD OF a.element
  START WITH a.element.getrootelement() IS NOT NULL
)
SELECT doc_id, xpath, value, node_type
FROM xml_paths
ORDER BY doc_id, xpath;

逻辑说明 #

• 元素部分：通过递归查询遍历所有XML节点，拼接出完整的XPATH路径，同时提取元素的文本值
• 属性部分：针对每个元素节点，遍历其所有属性，将属性名以@属性名的形式拼接到元素路径后，提取属性值
• 最后用UNION ALL合并两类结果，并按文档ID和路径排序

Expected Output：预期结果 #

执行上述查询后，会得到如下结构化结果：

PL/SQL环境下的使用 #

如果需要在PL/SQL中处理结果，可以把查询封装到游标里，逐个输出或进一步处理：

DECLARE
  CURSOR c_xml_paths IS
    WITH xml_paths AS (
      -- 这里复用上面的xml_paths CTE逻辑
      SELECT
        doc_id,
        '/' || REPLACE(
          XMLSerialize(CONTENT x.column_value.getrootelement() || SYS_CONNECT_BY_PATH(x.column_value.getname(), '/') 
          AS VARCHAR2(1000)),
          '//', '/'
        ) AS xpath,
        x.column_value.getstringval() AS value,
        'ELEMENT' AS node_type
      FROM xml_docs d,
           XMLTable('/descendant::*' PASSING d.xml_content) x
      CONNECT BY PRIOR x.column_value IS CHILD OF x.column_value
      START WITH x.column_value.getrootelement() IS NOT NULL
      
      UNION ALL
      
      SELECT
        doc_id,
        '/' || REPLACE(
          XMLSerialize(CONTENT a.element.getrootelement() || SYS_CONNECT_BY_PATH(a.element.getname(), '/') || '@' || a.attribute_name 
          AS VARCHAR2(1000)),
          '//', '/'
        ) AS xpath,
        a.attribute_value AS value,
        'ATTRIBUTE' AS node_type
      FROM xml_docs d,
           XMLTable('/descendant::*' PASSING d.xml_content) e,
           XMLTable(
             'for $attr in ./@* return 
              <attr>
                <element>{$node()}</element>
                <name>{local-name($attr)}</name>
                <value>{string($attr)}</value>
              </attr>'
             PASSING e.column_value AS "node()"
             COLUMNS
               element XMLTYPE PATH 'element',
               attribute_name VARCHAR2(100) PATH 'name',
               attribute_value VARCHAR2(1000) PATH 'value'
           ) a
      CONNECT BY PRIOR a.element IS CHILD OF a.element
      START WITH a.element.getrootelement() IS NOT NULL
    )
    SELECT doc_id, xpath, value, node_type
    FROM xml_paths
    ORDER BY doc_id, xpath;
BEGIN
  FOR rec IN c_xml_paths LOOP
    DBMS_OUTPUT.PUT_LINE(
      'Doc ID: ' || rec.doc_id || 
      ', XPath: ' || rec.xpath || 
      ', Value: ' || NVL(rec.value, 'NULL') || 
      ', Type: ' || rec.node_type
    );
  END LOOP;
END;
/

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