Passing Parameter to Dynamic SQL for parsing XML input

Database
Teradata Employee

Passing Parameter to Dynamic SQL for parsing XML input

Hi guys,


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)
COLUMNS
"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.

 

Thanks,

Mike