Oracle XMLTABLE ignores parent’s/ancestor’s attributes and nodes

I discovered an interesting work around while working on XML data in Oracle. I have a table where one column has the XMLTYPE datatype, so it can contain an XML document. I want to query, using XPath, and extract data from the xml, but when trying to access the parent or ancestor attributes, the resultset exposed NULL values, but there actually are some value to be shown.

Here is an example, which is useful to reproduce the beahviour:

-- create a table with XMLTYPE column and then fill it with some XML
create table tbck_dv_test (myxml XMLTYPE);
insert into tbck_dv_test (myxml) VALUES ('<root><parent type="father" id="100"><name>Daniele</name><children><child><name>Clara</name><age>6</age></child></children></parent></root>');

For clearness, here is the formatted XML document, just inserted in the table:

<root>
	<parent type="father" id="100">
		<name>Daniele</name>
		<children>
			<child>
				<name>Clara</name>
				<age>6</age>
			</child>
		</children>
	</parent>
</root>

Executing the following SQL Query, you should expect to have all the for columns filled with values, but “PARENT_NAME” and “PARENT_TYPE” are NULLs. In these example Oracle seems to ignore, or at least to be unable to go back and retrieve, the ancestor’s attributes and other children nodes.

select xt.* from tbck_dv_test x, xmltable('/root/parent/children/child'
    passing x.myxml
    columns
        child_name PATH 'name',
        age PATH 'age',
        parent_name PATH './../../name',
        parent_type PATH './../../@type'
    ) xt;

I tried the same query passing the XML “in-line”, using “PASSING XMLTYPE(‘<root>…</root>’)” instead of “PASSING X.MYXML” and…it worked!

So I finally tried to convert the XML column to CLOB and convert it back to XMLTYPE and it still worked.

So the final version of the query should be:

select xt.* from tbck_dv_test x, xmltable('/root/parent/children/child'
    passing XMLTYPE(XMLTYPE.GETCLOBVAL(x.myxml))
    columns
        child_name PATH 'name',
        age PATH 'age',
        parent_name PATH './../../name',
        parent_type PATH './../../@type'
    ) xt;
     

In this case, the ancestor’s attributes and node are read and displayed by SQL Developer, as it is in the following screenshot:

By the way, this issue dosn’t affect every Oracle version. It seems to affect Oracle 18c and 19c, while Oracle 12.1 doesn’t need this workaround to get ancestor’s values.