How to use XMLTABLE (Oracle SQL) in combination with attribute names

Question

I'm querying an Oracle SQL table containing XML.

The (simplified) XML structure is as follows:

<aggregate type="HeadAggregate">
  <entity type="Default" root="true" id="asdb7e9e-93324-43242d-b83a-f2d3202ed">
    <attribute name="ObjectName" multivalue="false">ExampleName</attribute>
    <attribute name="Subprocesses" multivalue="false">false</attribute>
    <attribute name="ObjectDesc" multivalue="false">Description</attribute>
  </entity>
<aggregate>

I want to get to retreive the object name. Therefore I wrote the following query:

SELECT xt.*
FROM DATABASENAME.TABLENAME x,
       XMLTABLE('/aggregate/entity[@type = ''Default'']'
         PASSING XMLTYPE(x.DATA)
         COLUMNS 
           attribute_name  VARCHAR2(100)  PATH 'attribute[1]'
         ) xt

So far so good. This works fine! I get the desired output.

However, now I want to replace '[1]' in PATH by a reference to the attribute name to make my script a bit more flexible. So I changed my script to:

SELECT xt.*
FROM DATABASENAME.TABLENAME x,
       XMLTABLE('/aggregate/entity[@type = ''Default'']'
         PASSING XMLTYPE(x.DATA)
         COLUMNS 
           attribute_name  VARCHAR2(100)  PATH 'attribute[@name = ''ObjectName'']'
         ) xt

For some reason, now I get the following error message:

00907. 00000 -  "missing right parenthesis"
*Cause:    
*Action:
Error at Line: 3 Column: 47

I've been struggling on this for a while. What am I doing wrong?

Many thanks in advance!

Answer 1

You can (or should) use double-quotes, rather than escaped single-quotes:

SELECT xt.*
FROM DATABASENAME.TABLENAME x,
       XMLTABLE('/aggregate/entity[@type = "Default"]'
         PASSING XMLTYPE(x.DATA)
         COLUMNS 
           attribute_name  VARCHAR2(100)  PATH 'attribute[@name = "ObjectName"]'
         ) xt

db<>fiddle