Oracle XMLTable- fetching column from parent node

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

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;

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.