Hi, i have a requirement, could not able to identify the proper query to fetch the results. Please suggest on the better solution:
table: design
column which contains xml: setup (CLOB)
In this table we have id column.
<setup>
<design1 firstname="a" lastname="b">
<filter1 attr1="1" attr2="0" attr3="1">
<filter2 param1="3" param2="2" param3="3">
</filter2>
<filter3 param="a">
</filter3>
</filter1>
<filter1 attr1="2" attr2="1" attr3="0">
<filter2 param1="1" param2="2" param3="3">
</filter2>
<filter3 param="a">
</filter3>
</filter1>
<filter1 attr1="3" attr2="0" attr3="1">
<filter2 param1="2" param2="2" param3="3">
</filter2>
<filter3 param="a">
</filter3>
</filter1>
</design1>
<setup>
We have around more than 1000+ such rows in the table: design.
My requirement is to get the results in below manner.
id attr1 param1
1 1 3
1 2 1
1 3 2
2 1 2
And also filter the attr1=1 and param1>2
My requirement is to get the results in below manner.
id attr1 param1
1 1 3
2 1 3
I have tried below query based on other conversations:
WITH
template_data AS (
SELECT x.id, xt.*
FROM design x,
XMLTABLE('/setup/design1/filter1'
PASSING XMLType(x.setup)
COLUMNS
attr1 VARCHAR2(4) PATH '@attr1',
filter2 XMLTYPE PATH 'filter2 '
) xt
where x.setup is not null
),
param1_data AS (
SELECT dd.id, attr1,
xt2.*
FROM template_data dd
LEFT OUTER JOIN
XMLTABLE('/'
PASSING dd.filter2
COLUMNS
param1 VARCHAR2(4) PATH '@param1',
param2 VARCHAR2(10) PATH '@param2'
) xt2 ON 1=1
where dd.filter2 is not null
)
SELECT * FROM param1_data where param1 =1 and param2 > 2;
When trying to fetch for all rows, it is throwing an error.
Please suggest how to get the results.
ORA-31011: XML parsing failed
ORA-19202: Error occurred in XML processing
LPX-00229: input source is empty
ORA-06512: at "SYS.XMLTYPE", line 272
ORA-06512: at line 1
31011. 00000 - "XML parsing failed"
*Cause: XML parser returned an error while trying to parse the document.
*Action: Check if the document to be parsed is valid.