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.

Answer 1

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

Answer 2

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;

Answer 3

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.