如何使用Oracle查询从表的XMLDATA列提取XML值

你的查询无法运行的核心问题是XML路径表达式错误：

在你的XML数据里，INVOICE_NUMBER是PAYMENT_HISTORY_ROW的子元素（标签），而不是属性。但你在XMLTABLE的COLUMNS定义里用了PATH '@INVOICE_NUMBER'（@符号用于定位属性），这就导致找不到对应的数据。

另外，如果你的XMLDATA列是CLOB类型，还需要用XMLTYPE()函数把它转换成XML类型再传入XMLTABLE。

修正后的查询语句 #

WITH inv AS
(
  SELECT xt2.INVOICE_NUMBER,
         t.ID
  FROM xx_xml_data_table t,
       XMLTABLE('/COLLECTIONSET/COLLECTION/PAYMENT_HISTORY/PAYMENT_HISTORY_ROW' 
                PASSING XMLTYPE(t.XMLDATA)
                COLUMNS 
                  INVOICE_NUMBER VARCHAR2(40) PATH 'INVOICE_NUMBER'
       ) xt2
  WHERE t.XML_REQUEST_ID IN (11386,11387)
)
SELECT * FROM inv;

关键修正点 #

1. 去掉PATH里的@符号：把@INVOICE_NUMBER改成INVOICE_NUMBER，匹配XML中的子元素节点。
2. （可选但建议）如果XMLDATA是CLOB类型，用XMLTYPE(t.XMLDATA)转换为XML类型，确保XMLTABLE能正确解析。

执行这个查询后，就能得到你期望的结果：

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