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

Very short version of “oracle xmltable ignores parent’s attributes using xpath”

In latest versions of Oracle (like 18c and 19c) don’t use directly the function XMLTABLE on a XMLTYPE column, but first convert it to CLOB and then back to XMLTYPE like in this query:

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; 

Now the full story about XPATH and XMLTABLE

I discovered an interesting work around while working on XML data in Oracle. When you have a table where one column has the XMLTYPE datatype, so that it can contain an XML document, and you want to extract data from the xml, using XPath, trying to access the parent or ancestor attributes, the result set exposed NULL values, even if 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 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.