I am trying to convert a oracle query to postgreSQL but not much clarity in documentation for our use case. Below query filter the xml in doc column but when trying to convert to Postgres i am not able achieve the same functionality
select colA, colB, colC from table1
xmltable('//node1/node2[normalize-space(v1/v2/v3[@type=$test_type])=$test_source]'
PASSING table1.doc, 'test_source' as "source",
'test_type' as "type" COLUMNS colA XMLTYPE PATH '/')
where (colC, colB) in (('test1', 'test2'))
as per the documentation provided https://www.postgresql.org/docs/current/functions-xml.html
I tried to convert the below query but I could not find proper way to pass the variable value, I tried below solution but its not working still. Any help is appreciated.
select colA, colB, colC from table1,
XMLTABLE('//node1/node2[normalize-space(v1/v2/v3[@type=''test_type''])=''test_source'']'
PASSING table1.doc COLUMNS colA XML PATH '/')
where (colC, colB) in (('test1', 'test2'))
And this query not working in java and giving error as
ERROR: syntax error at or near "$1" Position: 146
but not giving any error in PGAdmin Postgresql query console.
