Hi there,
I have a problem related to namespaces when accessing a node in an XMLType variable using PL/SQL XMLType functions.
The database I work in is 9.2.0.6.0.
The following code works fine:
<<<code>>>
DECLARE
l_response XMLTYPE;
l_snippet XMLTYPE;
l_ix BINARY_INTEGER;
BEGIN
l_response := XMLTYPE( '<AnimalMovementResponse>'
||'<AnimalMovementExport>'
||'<Movement>'
||'<NOTF_ID>1022866707</NOTF_ID>'
||'<NOTP_CDE>ARBTH</NOTP_CDE>'
||'<ANML_REF_NBR>BE 3 77853600</ANML_REF_NBR>'
||'</Movement>'
||'<Movement>'
||'<NOTF_ID>1022866740</NOTF_ID>'
||'<NOTP_CDE>RETAG</NOTP_CDE>'
||'<ANML_REF_NBR>BE 6 71107971</ANML_REF_NBR>'
||'</Movement>'
||'</AnimalMovementExport>'
||'</AnimalMovementResponse>'
);
l_ix := 1;
WHILE l_response.EXISTSNODE('//Movement['||TO_CHAR(l_ix)||'= 1
LOOP
DBMS_OUTPUT.PUT_LINE('index = '||TO_CHAR(l_ix));
-- parse the next <Movement> from the response
l_snippet := l_response.EXTRACT('//Movement['||l_ix ||']');
DBMS_OUTPUT.PUT_LINE(' NOTF_ID: '||l_snippet.EXTRACT('//NOTF_ID/text()').GETSTRINGVAL());
IF l_snippet.EXTRACT('//NOTP_CDE/text()').GETSTRINGVAL() = 'ARBTH'
THEN
DBMS_OUTPUT.PUT_LINE(' ANML_REF_NBR: '||l_snippet.EXTRACT('//ANML_REF_NBR/text()').GETSTRINGVAL());
END IF;
l_ix := l_ix + 1;
END LOOP;
END;
/
<<<code>>>
<<<output>>>
index = 1
NOTF_ID: 1022866707
ANML_REF_NBR: BE 3 77853600
index = 2
NOTF_ID: 1022866740
<<<output>>>
When a namespace attribute is present in the AnimalMovementResponse node, and I add the namespace as a parameter to the XMLType.EXISTSNODE() and to the XMLType.EXTRACT() methods, it still works fine:
<<<code>>>
DECLARE
l_response XMLTYPE;
l_snippet XMLTYPE;
l_ix BINARY_INTEGER;
BEGIN
l_response := XMLTYPE( '<AnimalMovementResponse xmlns="http://XXX/AnimalMovement/Response">'
||'<AnimalMovementExport>'
||'<Movement>'
||'<NOTF_ID>1022866707</NOTF_ID>'
||'<NOTP_CDE>ARBTH</NOTP_CDE>'
||'<ANML_REF_NBR>BE 3 77853600</ANML_REF_NBR>'
||'</Movement>'
||'<Movement>'
||'<NOTF_ID>1022866740</NOTF_ID>'
||'<NOTP_CDE>RETAG</NOTP_CDE>'
||'<ANML_REF_NBR>BE 6 71107971</ANML_REF_NBR>'
||'</Movement>'
||'</AnimalMovementExport>'
||'</AnimalMovementResponse>'
);
l_ix := 1;
WHILE l_response.EXISTSNODE('//Movement['||TO_CHAR(l_ix)||']'
,'xmlns="http://XXX/AnimalMovement/Response"') = 1
LOOP
DBMS_OUTPUT.PUT_LINE('index = '||TO_CHAR(l_ix));
-- parse the next <Movement> from the response
l_snippet := l_response.EXTRACT('//Movement['||l_ix ||']'
,'xmlns="http://XXX/AnimalMovement/Response"');
DBMS_OUTPUT.PUT_LINE(' NOTF_ID: '||l_snippet.EXTRACT('//NOTF_ID/text()'
,'xmlns="http://XXX/AnimalMovement/Response"').GETSTRINGVAL());
IF l_snippet.EXTRACT('//NOTP_CDE/text()'
,'xmlns="http://XXX/AnimalMovement/Response"').GETSTRINGVAL() = 'ARBTH'
THEN
DBMS_OUTPUT.PUT_LINE(' ANML_REF_NBR: '||l_snippet.EXTRACT('//ANML_REF_NBR/text()'
,'xmlns="http://XXX/AnimalMovement/Response"').GETSTRINGVAL());
END IF;
l_ix := l_ix + 1;
END LOOP;
END;
/
<<<code>>>
<<<output>>>
index = 1
NOTF_ID: 1022866707
ANML_REF_NBR: BE 3 77853600
index = 2
NOTF_ID: 1022866740
<<<output>>>
However, when a namespace attribute is present both in the AnimalMovementResponse node and in the AnimalMovementExport node, I do not know how to specify the presence of these 2 namespaces in the XML to the XMLType.EXISTSNODE() and the XMLType.EXTRACT() methods:
<<<code>>>
DECLARE
l_response XMLTYPE;
l_snippet XMLTYPE;
l_ix BINARY_INTEGER;
BEGIN
l_response := XMLTYPE( '<AnimalMovementResponse xmlns="http://XXX/AnimalMovement/Response">'
||'<AnimalMovementExport xmlns="http://XXX/AnimalMovementExport">'
||'<Movement>'
||'<NOTF_ID>1022866707</NOTF_ID>'
||'<NOTP_CDE>ARBTH</NOTP_CDE>'
||'<ANML_REF_NBR>BE 3 77853600</ANML_REF_NBR>'
||'</Movement>'
||'<Movement>'
||'<NOTF_ID>1022866740</NOTF_ID>'
||'<NOTP_CDE>RETAG</NOTP_CDE>'
||'<ANML_REF_NBR>BE 6 71107971</ANML_REF_NBR>'
||'</Movement>'
||'</AnimalMovementExport>'
||'</AnimalMovementResponse>'
);
l_ix := 1;
WHILE l_response.EXISTSNODE('//Movement['||TO_CHAR(l_ix)||']'
,'???') = 1
LOOP
DBMS_OUTPUT.PUT_LINE('index = '||TO_CHAR(l_ix));
-- parse the next <Movement> from the response
l_snippet := l_response.EXTRACT('//Movement['||l_ix ||']'
,'???');
DBMS_OUTPUT.PUT_LINE(' NOTF_ID: '||l_snippet.EXTRACT('//NOTF_ID/text()'
,'???').GETSTRINGVAL());
IF l_snippet.EXTRACT('//NOTP_CDE/text()'
,'???').GETSTRINGVAL() = 'ARBTH'
THEN
DBMS_OUTPUT.PUT_LINE(' ANML_REF_NBR: '||l_snippet.EXTRACT('//ANML_REF_NBR/text()'
,'???').GETSTRINGVAL());
END IF;
l_ix := l_ix + 1;
END LOOP;
END;
/
<<<code>>>
Any suggestion?
Thanks,
Jaap Kool