在 Oracle 中查询递增的 XML 元素名称

Question

我正在尝试查询表中的XMLtype字段，该字段的元素名称每次出现时都会递增1。这些元素可能有 1 到多个级别，我想知道是否有办法对元素名称进行通配符，以便它根据名称搜索返回所有元素值，而不是我将名称的所有可能级别合并在一起？我希望在弄清楚这一步后列出或连接结果。

select extractvalue(myXMLfield,'/doc/name1') from myTable
union
select extractvalue(myXMLfield,'/doc/name2') from myTable
union
select extractvalue(myXMLfield,'/doc/name3') from myTable

与对元素名称的某种通配符搜索

select extractvalue(myXMLfield,'/doc/name%') from myTable

XML 示例

<doc><name1>NAME_1</name1><name2>NAME_2</name2><name3>NAME_3</name3></doc>

所需的 SQL 查询 varchar 级联输出

"NAME_1,NAME_2,NAME_3"

Answer 1

您可以使用 XMLTable 提取多个节点值，并使用 XPath 中的子字符串模式对节点名称进行通配符(根据实际数据设置适当的列大小(：

select x.name
from myTable t
cross join XMLTable(
  '/doc/*[substring(name(), 1, 4) = "name"]'
  passing myXMLfield
  columns name varchar2(20) path '.'
) x;
NAME                
--------------------
NAME_1
NAME_2
NAME_3

然后，您可以只使用 listagg() 作为最终输出：

select listagg(x.name, ',') within group (order by null) as names
from myTable t
cross join XMLTable(
  '/doc/*[substring(name(), 1, 4) = "name"]'
  passing myXMLfield
  columns name varchar2(20) path '.'
) x;
NAMES                         
------------------------------
NAME_1,NAME_2,NAME_3

按 null ins 排序并不理想;如果您的实际数据具有其他属性或值，您可以提取这些属性或值并将其用于排序，这可能会更好。如果没有，您可以通过另一个子字符串使用"name"后面的部分，假设它实际上总是数字：

select listagg(x.name, ',') within group (order by x.id) as names
from myTable t
cross join XMLTable(
  '/doc/*[substring(name(), 1, 4) = "name"]'
  passing myXMLfield
  columns name varchar2(20) path '.',
    id number path 'substring(name(.), 5)'
) x;