SQL & PL/SQL

DB Version: Oracle 11g R2

We have a requirement of collecting XML data from external web services using XML query. It is working good, however, if the XML contains a second node for a particular element, - it is still valid but incompatible to be processed in Oracle - we get an error. So in order to find second nodes for all elements, we use the xpath and when we run it, it is fetching the ancestor node successfully.

{code}

SELECT EXTRACT(t.data, '//wd:Report_Entry/wd:HIRING_RESTRICTIONS_LOCATION[2]/ancestor::wd:Report_Entry', 'xmlns:wd="urn:com.workday.report/Insite_External_Position"') row_data

FROM wsfo.nat_workday_xml t

{code}

However, as we need to do it for all elements, we tried to use dynamic sql for it:

{code}

DECLARE

   v_CursorID  INTEGER;

   CURSOR nwx_cur

   IS SELECT DATA

   FROM wsfo.nat_workday_xml;

   v_xml XMLTYPE;  

   v_sql  VARCHAR2(4000) := 'SELECT EXTRACT(:pdata, ''/wd:Report_Data/wd:Report_Entry/wd:HIRING_RESTRICTIONS_LOCATION[2]/ancestor::wd:Report_Entry'', ''xmlns:wd="urn:com.workday.report/Insite_External_Position"'') row_data into :voutput FROM wsfo.nat_workday_xml t';

   v_output XMLTYPE;

   v_dummy INTEGER;

BEGIN

   OPEN nwx_cur;

   FETCH nwx_cur INTO v_xml;

   CLOSE nwx_cur;

   v_CursorID := DBMS_SQL.OPEN_CURSOR;

   DBMS_SQL.PARSE(v_CursorID, V_SQL, DBMS_SQL.NATIVE);

   DBMS_SQL.BIND_VARIABLE(v_CursorID, ':pdata', v_xml);

   DBMS_SQL.BIND_VARIABLE(v_CursorID, ':voutput', v_output);

   v_Dummy := DBMS_SQL.EXECUTE(v_CursorID);

   DBMS_SQL.VARIABLE_VALUE(v_CursorID, ':pdata', v_xml);

   DBMS_SQL.VARIABLE_VALUE(v_CursorID, ':voutput', v_output);

   DBMS_SQL.CLOSE_CURSOR(v_CursorID);

   COMMIT;

EXCEPTION

   WHEN OTHERS THEN

     DBMS_SQL.CLOSE_CURSOR(v_CursorID);

     RAISE;

END;

{code}

However this code always fails with an error: ORA-01006: bind variable does not exist. This is because of the colon used in the namespace identifier and the axis operator "ancestor".

Is there a way to overcome this issue by escaping the character colon (:)? Or it is not at all possible to use dynamic sql for this kind of requirement, if so any other alternate solutions?

Thanks in advance.

regards,

Natarajan