Maria Hristova
asked on
Problem with XMLTABLE in PL/SQL Oracle 12c
Resp_7812.xml
I try to get columns from a XML file. I have attached it in here.
The code is as follows:
declare
c_xml xmltype;
begin
c_xml := xmltype('<?xml version=''1.0'' encoding=''utf-8''?>
<env:Envelope xmlns:env="http://schemas.xmlsoap.org/soap/envelope/" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<env:Header/>
<env:Body>
<srvc:returnActStateByEgnR esponse xmlns="http://curr_state_egn/CURR_STATE_EGNService" xmlns:srvc="http://curr_state_egn/CURR_STATE_EGNServiceService">
<srvc:result>
<consents_tblType>
<item>
<req_id>112</req_id>
<purpose_code>CC0100</purp ose_code>
<consent_state>0</consent_ state>
</item>
<item>
<req_id>112</req_id>
<purpose_code>CC0200</purp ose_code>
<consent_state>1</consent_ state>
</item>
<item>
<req_id>112</req_id>
<purpose_code>CC0300</purp ose_code>
<consent_state>0</consent_ state>
</item>
</consents_tblType>
</srvc:result>
</srvc:returnActStateByEgn Response>
</env:Body>
</env:Envelope>');
FOR consents_tblTypes IN
( SELECT
p_req_id
, p_purpose_code
, p_consent_state
FROM xmltable(
XMLNamespaces(
'http://schemas.xmlsoap.org/soap/envelope/' AS "env"
--, 'http://www.w3.org/2001/XMLSchema-instance' AS "xsi"
, 'http://curr_state_egn/CURR_STATE_EGNServiceService' AS "srvc"
),
'/env:Envelope/env:Body/sr vc:returnA ctStateByE gnResponse /srvc:resu lt/consent s_tblType/ item'
PASSING c_xml
COLUMNS
p_req_id NUMBER PATH 'req_id' --/text()
, p_purpose_code VARCHAR2(20) PATH 'purpose_code' --/text()
, p_consent_state NUMBER PATH 'consent_state' --/text()
)
)
LOOP
DBMS_OUTPUT.put_line('p_re q_id = ' || to_char(consents_tblTypes. p_req_id)) ;
DBMS_OUTPUT.put_line('p_pu rpose_code = ' || consents_tblTypes.p_purpos e_code) ;
DBMS_OUTPUT.put_line('p_co nsent_stat e = ' || to_char(consents_tblTypes. p_consent_ state)) ;
END LOOP;
end;
To c_xml variable is assigned XML from the file in the real code. But in the above example I assigned the XML directly to c_xml.
So the problem is that I could not get anything from the code.
Nothing is printed as DBMS_OUTPUT.
Nothing is got as column.
Does anybody know where is the problem?
I try to get columns from a XML file. I have attached it in here.
The code is as follows:
declare
c_xml xmltype;
begin
c_xml := xmltype('<?xml version=''1.0'' encoding=''utf-8''?>
<env:Envelope xmlns:env="http://schemas.xmlsoap.org/soap/envelope/" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<env:Header/>
<env:Body>
<srvc:returnActStateByEgnR
<srvc:result>
<consents_tblType>
<item>
<req_id>112</req_id>
<purpose_code>CC0100</purp
<consent_state>0</consent_
</item>
<item>
<req_id>112</req_id>
<purpose_code>CC0200</purp
<consent_state>1</consent_
</item>
<item>
<req_id>112</req_id>
<purpose_code>CC0300</purp
<consent_state>0</consent_
</item>
</consents_tblType>
</srvc:result>
</srvc:returnActStateByEgn
</env:Body>
</env:Envelope>');
FOR consents_tblTypes IN
( SELECT
p_req_id
, p_purpose_code
, p_consent_state
FROM xmltable(
XMLNamespaces(
'http://schemas.xmlsoap.org/soap/envelope/' AS "env"
--, 'http://www.w3.org/2001/XMLSchema-instance' AS "xsi"
, 'http://curr_state_egn/CURR_STATE_EGNServiceService' AS "srvc"
),
'/env:Envelope/env:Body/sr
PASSING c_xml
COLUMNS
p_req_id NUMBER PATH 'req_id' --/text()
, p_purpose_code VARCHAR2(20) PATH 'purpose_code' --/text()
, p_consent_state NUMBER PATH 'consent_state' --/text()
)
)
LOOP
DBMS_OUTPUT.put_line('p_re
DBMS_OUTPUT.put_line('p_pu
DBMS_OUTPUT.put_line('p_co
END LOOP;
end;
To c_xml variable is assigned XML from the file in the real code. But in the above example I assigned the XML directly to c_xml.
So the problem is that I could not get anything from the code.
Nothing is printed as DBMS_OUTPUT.
Nothing is got as column.
Does anybody know where is the problem?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
So you can accept it as solution to this question :).
ASKER
I have received useful answer from my colleague and it works!
declare
c_xml xmltype;
begin
c_xml := xmltype('<?xml version=''1.0'' encoding=''utf-8''?>
<env:Envelope xmlns:env="http://schemas.xmlsoap.org/soap/envelope/" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<env:Body>
<srvc:returnActStateByEgnR
<srvc:result>
<consents_tblType>
<item>
<req_id>112</req_id>
<purpose_code>CC0100</purp
<consent_state>0</consent_
</item>
<item>
<req_id>112</req_id>
<purpose_code>CC0200</purp
<consent_state>1</consent_
</item>
<item>
<req_id>112</req_id>
<purpose_code>CC0300</purp
<consent_state>0</consent_
</item>
</consents_tblType>
</srvc:result>
</srvc:returnActStateByEgn
</env:Body>
</env:Envelope>');
FOR consents_tblTypes IN
( SELECT
p_req_id
, p_purpose_code
, p_consent_state
FROM xmltable(
XMLNamespaces(
'http://schemas.xmlsoap.org/soap/envelope/' AS "env"
, 'http://curr_state_egn/CURR_STATE_EGNServiceService' AS "srvc"
),
'/env:Envelope/env:Body/sr
PASSING c_xml
COLUMNS
p_req_id NUMBER PATH 'req_id' --/text()
, p_purpose_code VARCHAR2(20) PATH 'purpose_code' --/text()
, p_consent_state NUMBER PATH 'consent_state' --/text()
)
)
LOOP
DBMS_OUTPUT.put_line('p_re
DBMS_OUTPUT.put_line('p_pu
DBMS_OUTPUT.put_line('p_co
END LOOP;
end;