xQuery阵列[*]？

Question

SELECT
    XMLQUERY    ('$x/test/xml[1]/data(@type)' PASSING XMLCOLUMN as "x") type,
    XMLQUERY    ('$x/test/xml[1]/text()' PASSING XMLCOLUMN as "x") text
FROM 
    XML

我要选择父节点的所有子节点。我在想这样的事情：

SELECT
    XMLQUERY    ('$x/test/xml[*]/data(@type)' PASSING XMLCOLUMN as "x") type,
    XMLQUERY    ('$x/test/xml[*]/text()' PASSING XMLCOLUMN as "x") text
FROM 
    XML

编辑: xml：

<test>
    <xml type = 1>a</xml>
    <xml type = 2>b</xml>
    <xml type = 3>c</xml>
    <xml type = 4>d</xml>
    <xml type = 5>e</xml>
    ...
</test>

预期结果：

type | text
-----|-----
1    | a
2    | b
3    | c
4    | d
...

Answer 1

基于XMLTABLE，尝试如下所示：

select t.* from x,   
       xmltable('$X/test/xml'
          columns "type" int path 'data(@type)',
                  "text" varchar(10) path 'text()') as t

它应该返回一个具有所需值和结构的表。