Oracle XMLTable- fetching column from parent node

Question

I have the following XML structure:

<root>
    <parent>
         <parent_id>1</parent_id>
         <parent_value>10000</parent_value>
         <child>
              <child_id>11</child_id>
              <other_value>1000</other_value>
         </child>
         <child>
              <child_id>12</child_id>
              <other_value>1000</other_value>
         </child>
    </parent>
</root>

Expected Output:

  CHILD_ID PARENT_VALUE
---------- ------------
        11 10000            
        12 10000

What I have tried:

WITH xtbl AS (SELECT xmltype ('<root>
                    <parent>
                         <parent_id>1</parent_id>
                         <parent_value>10000</parent_value>
                         <child>
                              <child_id>11</child_id>
                              <other_value>1000</other_value>
                         </child>
                         <child>
                              <child_id>12</child_id>
                              <other_value>1000</other_value>
                         </child>
                    </parent>
                </root>') AS xcol FROM dual)
      SELECT myXmlTable.*
        FROM xtbl
             CROSS JOIN
             xmltable ('/root/parent/child'
                       PASSING xcol
                       COLUMNS child_id NUMBER (5) PATH 'child_id',
                               parent_value NUMBER (10) PATH './parent_value') myXmlTable;

Problem with my query is that parent_value comes to be null. Please help.

score 6 · Accepted Answer · answered Jul 16 '18 at 12:11

You are looking for ./parent_node, which is a <parent_node> under the current <child> node. And that doesn't exist.

You just need to go up a level:

parent_value NUMBER (10) PATH './../parent_value'

Demo with your CTE and just that added ../:

WITH xtbl AS (SELECT xmltype ('<root>
                    <parent>
                         <parent_id>1</parent_id>
                         <parent_value>10000</parent_value>
                         <child>
                              <child_id>11</child_id>
                              <other_value>1000</other_value>
                         </child>
                         <child>
                              <child_id>12</child_id>
                              <other_value>1000</other_value>
                         </child>
                    </parent>
                </root>') AS xcol FROM dual)
      SELECT myXmlTable.*
        FROM xtbl
             CROSS JOIN
             xmltable ('/root/parent/child'
                       PASSING xcol
                       COLUMNS child_id NUMBER (5) PATH 'child_id',
                               parent_value NUMBER (10) PATH './../parent_value') myXmlTable;

  CHILD_ID PARENT_VALUE
---------- ------------
        11        10000
        12        10000

Strange, for the it does not work. `parent_value` is NULL. **Update:** It does not work on a 11.2.0.3.0 database, on 11.2.0.4.0 it works fine. — Wernfried Domscheit, Jul 16 '18 at 12:30
This works in 11.2.0.4, 12.1.0.2 and 12.2.0.1. But it does get null in 11.2.0.2 (db<>fiddle), which I guess is a bug in that version. (That doesn't work with `/ancestor::node()[1]/parent_value` either, which was my backup...) — Alex Poole, Jul 16 '18 at 12:46
Could `LEFT (or RIGHT depends) OUTER JOIN` help? It did in my case. — davidm, Nov 10 '20 at 13:24

score 1 · Answer 2 · answered Jul 16 '18 at 12:29

I don't know whether this is the most optimized or shortest version but it works:

WITH xtbl AS (SELECT xmltype ('<root>
                    <parent>
                         <parent_id>1</parent_id>
                         <parent_value>10000</parent_value>
                         <child>
                              <child_id>11</child_id>
                              <other_value>1000</other_value>
                         </child>
                         <child>
                              <child_id>12</child_id>
                              <other_value>1000</other_value>
                         </child>
                    </parent>
                </root>') AS xcol FROM dual)
      SELECT myXmlTable.*
        FROM xtbl
             CROSS JOIN
             XMLTABLE ('for $c in /root/parent/child 
                          return <child parent_value="{$c/../parent_value}">{$c}</child>'
                       PASSING xcol COLUMNS 
                       child_id NUMBER (5) PATH 'child/child_id',
                       parent_value NUMBER (10) PATH '@parent_value'
                       ) myXmlTable;

score 0 · Answer 3 · answered Mar 15 '21 at 03:01

We had the same issue with Oracle 12.2.0.1.0 - i.e. the PLSQL query was not returning the parent node values from within the XML data using the ./../ syntax. In our case a MATERIALIZE hint was causing the null values to be returned - no idea why, but when the hint was removed the parent node issue disappeared.

Oracle XMLTable- fetching column from parent node

3 Answers3

Linked

Related