xQuery Array[*]?

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

I want to select all child-nodes of a parent-node. I am thinking about something looking like this:

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

edit: 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>

expected result:

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

Direct child nodes or all? Take a look at http://stackoverflow.com/questions/5119655/xpath-to-get-all-child-nodes-elements-comments-and-text-without-parent and if you are returning multiple columns, I recommend XMLTABLE instead — data_henrik, Nov 09 '16 at 09:53
XMLQUERY ('$x/xml[*]/child::node()' PASSING XMLCOLUMN as "x") text gives me the same result — Finn von Holten, Nov 09 '16 at 10:03

score 1 · Accepted Answer · answered Nov 09 '16 at 18:22

Try something like the following, based on XMLTABLE:

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

It should return a table with the desired values and structure.

xQuery Array[*]?

1 Answers1