Hi,
I'm on 11.2.0.3 and I need to extract columns out of a section of XML with conditions on, but I get "ORA-00907: missing right parenthesis", so I'm guessing that the way I'm going about it is not supported or I've somehow got the wrong syntax or something.
Here's a highly simplified version of what I'm trying to do (there could be many different entries in the <sets1> and <sets2> sections, and they could be in different orders, so it's not like I can always choose the first or last):
with sample_data as (select 1 id,
xmltype('<root>
<sets1>
<set1><set_name>name1</set_name><set_val>val1</set_val></set1>
<set1><set_name>name2</set_name><set_val>val2</set_val></set1>
</sets1>
<sets2>
<set2><set_name>name3</set_name><set_val>val3</set_val></set2>
<set2><set_name>name4</set_name><set_val>val4</set_val></set2>
</sets2>
</root>') xml_doc from dual)
select *
from sample_data sd,
xmltable('<root>' passing sd.xml_doc
columns col1 varchar2(10) path 'sets1/set1[set_name=''name1'']/set_val',
col2 varchar2(10) path 'sets1/set1[set_name=''name2'']/set_val',
col3 varchar2(10) path 'sets2/set2[set_name=''name4'']/set_val') x;
What I'm trying to get:
COL1 COL2 COL3
---- ---- ----
val1 val2 val4
I know that I could split out the <set1> and <set2> nodes into their own xmltypes, do xmltable on that and then join them and do the necessary pivoting etc, but when I try running that against just 1000 rows, the db did not like it at all and terminated my session! Hence, I'm looking for a more elegant way of doing it.
Is there a way of doing this within one XMLTABLE? Any pointers will be very gratefully received!