XMLTABLE Returning Null for Single & Multiple Lookups

Question

I've done extensive searches on here for similar inquires regarding parsing XML fields which are expected to return 1 or more of the same tags. I've tried to export at least one record before bothering to attempt the parsing of multiple rows however it still returns Null.

At this point, I'm completely baffled at why this is happening.

Example XML from column name "XMLPref":

<ParentProfiler>
  <SettingExp>
    <Entity name="companycode" type="robot" value="fn8ks92in"></Entity>
    <Entity name="locationcode" type="dummy" value="CO"></Entity>
    <Entity name="companycode" type="dummy" value="0xm9n4mdk"></Entity>
  </SettingExp>
</ParentProfiler>

I'm trying to parse all <Entity> tags with name = "companycode" and retrieve the value associated with them. A few other XMLs are quite large so I've simplified the XML above to only three entities.

My testing query below is an attempt at using the old extractvalue(xmltype method and XMLTABLE method.

Test Query:

select z.test1, z.test2, b.XMLPref 
,extractvalue(xmltype(b.XMLPref),'//ParentProfiler/SettingExp/Entity[@name="companycode"]/@value[0]') as TestingOldMethod
from test.XMLTABLE b,
XMLTABLE('/ParentProfiler/SettingExp'
    PASSING XMLTYPE(b.XMLPref)
    COLUMNS test1  VARCHAR2(100)  PATH 'Entity[@name="companycode"]/text()' --'Entity/text()'
           ,test2  VARCHAR2(100)  PATH 'Entity[1]' -- 'Values/string-join(./Entity/text(), ",")',
    ) z

After attempting different variations of the path, whether it's one single parse (first iteration) or multiple, NULL is returned. Could anyone please help?

Answer 1

You can go down to the (filtered) Entity node in your XPath, and then get the value attributes from those:

XMLTABLE(
  '/ParentProfiler/SettingExp/Entity[@name="companycode"]'
  PASSING XMLTYPE(b.XMLPref)
  COLUMNS value  VARCHAR2(100)  PATH '@value'
)

As an example, keeping your confusing xmltable table name:

select x.value
from xmltable b
cross apply XMLTABLE(
  '/ParentProfiler/SettingExp/Entity[@name="companycode"]'
  PASSING XMLTYPE(b.XMLPref)
  COLUMNS value  VARCHAR2(100)  PATH '@value'
) x

VALUE
fn8ks92in
0xm9n4mdk

You can then easily get other attributes if you want:

select x.type, x.value
from xmltable b
cross apply XMLTABLE(
  '/ParentProfiler/SettingExp/Entity[@name="companycode"]'
  PASSING XMLTYPE(b.XMLPref)
  COLUMNS type VARCHAR2(100) PATH '@type',
    value VARCHAR2(100) PATH '@value'
) x

TYPE	VALUE
robot	fn8ks92in
dummy	0xm9n4mdk

fiddle

---

Your extractvalue would sort of work if looked for a specific instance of the Entity rather than value, and indexed as 1 not 0, i.e.:

'/ParentProfiler/SettingExp/Entity[@name="companycode"][1]/@value'

but that only gets you one value, and is deprecated of course.

Your XMLTable version is looking for the text content of the Entity node, which is indeed null, rather than the value attribute, but has other problems in that form too.