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!
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