使用Oracle XMLTable解析XML返回空值，求正确实现方案

问题背景 #

需要解析包含HTML结构的XML，提取TABLE_NAME、NUM_ROWS、RUN_DATE三列数据（共3行），但现有Oracle查询返回空列且无有效数据行，期望输出如下：

UNIFIER_US_XCPG1_VIEW   1   2024-10-16T08:36:53.000+00:00
UNIFIER_UXCORY_VIEW     5   2024-10-16T08:36:53.000+00:00
UNIFIER_UXBLS_VIEW      9   2024-10-16T08:36:53.000+00:00

现有查询的问题 #

1. 未处理XML命名空间：原始XML的<html>标签带有xmlns="http://www.w3.org/1999/xhtml"命名空间，XPath未指定命名空间会导致节点匹配失败。
2. XPath根路径错误：正确的根路径应为/html/body/table/tr，而非/body/table/tr，遗漏了顶层的<html>节点。
3. 列路径错误：rundate的PATH错误指向td[2]，实际应为td[3]。
4. 未过滤表头行：查询会包含第一行表头数据，需通过行的class属性或位置过滤掉。

修正后的查询语句 #

WITH tbl AS (
    SELECT
        xmltype('<?xml version="1.0" encoding="UTF-8"?>
<html xmlns="http://www.w3.org/1999/xhtml">
<!-- Generated by Oracle BI Publisher 12.2.1.4.0 -->
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8"/>
<title></title>
<style type="text/css" id="internalStyle">
.c0 {height: 13.048pt;}
.c1 {word-wrap:break-word;border-width: 0.8pt;border-color: #777777;border-style: solid;width:33.947%;background-color: #cfe0f1;}
.c2 {line-height: 9.248pt;margin-top: 0.0pt;margin-bottom: 0.0pt;margin-left: 3.4pt;margin-right: 3.399pt;}
.c3 {font-family: Tahoma;font-size: 8.0pt;color: #000000;}
.c4 {word-wrap:break-word;border-width: 0.8pt;border-color: #777777;border-style: solid;width:25.413%;background-color: #cfe0f1;}
.c5 {line-height: 9.248pt;margin-top: 0.0pt;margin-bottom: 0.0pt;margin-left: 3.399pt;margin-right: 3.399pt;}
.c6 {word-wrap:break-word;border-width: 0.8pt;border-color: #777777;border-style: solid;width:40.639%;background-color: #cfe0f1;}
.c7 {height: 16.048pt;}
.c8 {word-wrap:break-word;border-width: 0.8pt;border-color: #777777;border-style: solid;width:33.947%;background-color: #ffffff;}
.c9 {word-wrap:break-word;border-width: 0.8pt;border-color: #777777;border-style: solid;width:25.413%;background-color: #ffffff;}
.c10 {text-align: right;line-height: 9.248pt;margin-top: 0.0pt;margin-bottom: 0.0pt;margin-left: 3.399pt;margin-right: 3.399pt;}
.c11 {word-wrap:break-word;border-width: 0.8pt;border-color: #777777;border-style: solid;width:40.639%;background-color: #ffffff;}
.c12 {margin-top: 0.0pt;margin-bottom: 9.0pt;table-layout:fixed;margin-right: auto;width: 369.1pt;border-collapse: collapse;}
</style>
</head>
<body>
<table class="c12">
<col width="33.947%"/>
<col width="25.413%"/>
<col width="40.639%"/>
<tr class="c0">
<td valign="top" class="c1"><p class="c2"><span class="c3">TABLE_NAME</span></p>
</td>
<td valign="top" class="c4"><p class="c5"><span class="c3">NUM_ROWS</span></p>
</td>
<td valign="top" class="c6"><p class="c5"><span class="c3">RUN_DATE</span></p>
</td>
</tr>
<tr class="c7">
<td valign="top" class="c8"><p class="c2"><span class="c3">UNIFIER_US_XCPG1_VIEW</span></p>
</td>
<td valign="top" class="c9"><p class="c10"><span class="c3">1</span></p>
</td>
<td valign="top" class="c11"><p class="c5"><span class="c3">2024-10-16T08:36:53.000+00:00</span></p>
</td>
</tr>
<tr class="c7">
<td valign="top" class="c8"><p class="c2"><span class="c3">UNIFIER_UXCORY_VIEW</span></p>
</td>
<td valign="top" class="c9"><p class="c10"><span class="c3">5</span></p>
</td>
<td valign="top" class="c11"><p class="c5"><span class="c3">2024-10-16T08:36:53.000+00:00</span></p>
</td>
</tr>
<tr class="c7">
<td valign="top" class="c8"><p class="c2"><span class="c3">UNIFIER_UXBLS_VIEW</span></p>
</td>
<td valign="top" class="c9"><p class="c10"><span class="c3">9</span></p>
</td>
<td valign="top" class="c11"><p class="c5"><span class="c3">2024-10-16T08:36:53.000+00:00</span></p>
</td>
</tr>
</table>
</body>
</html>') xml_data
    FROM
        dual
)
SELECT
    x.tablename,
    x.rowcount,
    x.rundate
FROM
     tbl
CROSS JOIN XMLTABLE ( 
    XMLNAMESPACES ('http://www.w3.org/1999/xhtml' AS "html"),
    '/html/body/table/tr[@class="c7"]'
    PASSING tbl.xml_data
    COLUMNS
        tablename VARCHAR2(100) PATH 'html:td[1]/html:p/html:span',
        rowcount VARCHAR2(100) PATH 'html:td[2]/html:p/html:span',
        rundate VARCHAR2(100) PATH 'html:td[3]/html:p/html:span'
) x;

关键修复点说明 #

• 命名空间处理：通过XMLNAMESPACES声明命名空间，并在XPath中使用前缀html:匹配所有节点。
• 正确路径：根路径修正为/html/body/table/tr[@class="c7"]，仅匹配数据行（过滤掉class为c0的表头行）。
• 列路径修正：将rundate的PATH改为td[3]/p/span，并添加命名空间前缀。

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