Passing Parameter to Dynamic SQL for parsing XML input
I'm working on a stored procedure that will execute a dynamic SQL that for shredding an XML. The "dynamic SQL" itself is already pregenerated using a separate process. I do need to change the condition on the WHERE clause prior to execution. The Dynamic SQL looks something like this:
SELECT XML.f1, XML.f2, XML.f3
FROM (SELECT XMLTEXT FROM XMLTABLE WHERE ID = __XML_ID__) as SRC, (XMLTABLE(XMLNAMESPACES(...),
'/' PASSING CREATEXML(SRC.XMLTEXT)
"f1" VARCHAR(20) PATH 'A/B/f1'
,"f2" VARCHAR(20) PATH 'A/B/f2'
,"f3" VARCHAR(20) PATH 'A/B/f3'
) AS XML;
For the above, I'm trying to do an OREPLACE to change the string "__XML_ID__" to the actual ID but it's failing as the source variable is very long (as some of our inputs tend to be long). Is there an alternative I can use for this? Alternatively, is there another way of writing this so I can pass the variable __XML_ID__ somehow (maybe as a variable)? Any help will be greatly appreciated.