Hello
I am on Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
PL/SQL Release 11.2.0.3.0 - Production
I am trying to switch the xpath dynamically in an XMLTABLE clause based on the existence of a node in the XML(web service response).
I was able to compile the below code, but when I executed got this error: ORA-19280: XQuery dynamic type mismatch: expected atomic value - got node
I have defined the cursor as below:
Cursor C1(l_soap_response CLOB,l_xpath_node VARCHAR2) Is
Select xt.*
From
Xmltable(
Xmlnamespaces
(
'http://schemas.xmlsoap.org/soap/envelope/' As "n1"
,'http://www.test.com/GAU/MeterDetailsService/1.0' As "n2"
),
'''l_xpath_node''' PASSING Xmltype(l_soap_response)
Columns
InstallationDate Varchar2(40) Path 'n2:InstallationDate',
Location1 Varchar2(100) Path 'n2:Location1',
MeterPointNumber VARCHAR2(40) Path 'n2:MeterPointNumber',
PhysicalMeterNumber Varchar2(20) Path 'n2:PhysicalMeterNumber'
) Xt;
In the main PLSQL body shown below, I am checking for the existence of a node(MeterDetailsResult) in the result. If node not found,then assume an error in the response and use "StatusCode" as the xpath:
BEGIN
-- Check for existence of the "MeterDetailsResult" node in the XML
select existsnode(XMLTYPE(l_soap_response),'env:Envelope/env:Body/Response/tns:MeterDetailsResult',l_namespace)
into node_exists
from dual;
if ( node_exists = 1) then
l_xpath_node := 'n1:Envelope/n1:Body/n2:Response/n2:MeterDetailsResult'; -- Meter Details fetched from web service
else
l_Xpath_Node := 'n1:Envelope/n1:Body/n2:Response/n2:StatusCode'; -- Error returned from web service
end if;
Open c1(l_soap_response,l_xpath_node);
..
..
..
END;
Anyone has done this type of stuff before?
Thanks in advance
M