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

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

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!

Solution

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