<detailedOutput>
<entity>
<entityLabel>Policy Holder 1</entityLabel>
<entityName>PHN.1</entityName>
<entityType>Policy Holder Name</entityType>
<fact>
<name>fired</name>
<nameContents>Y</nameContents>
<messageCode>M003</messageCode>
<reported>Y</reported>
<uniqueID>C_9_3_1</uniqueID>
<instanceID>1.1</instanceID>
</fact>
<fact>
<name>fired</name>
<nameContents>Y</nameContents>
<messageCode>M010</messageCode>
<reported>Y</reported>
<uniqueID>C_4_3_1</uniqueID>
<instanceID>1.1</instanceID>
</fact>
</entity>
<entity>
<entityLabel>property</entityLabel>
<entityName>PROP.1</entityName>
<entityType>Property</entityType>
<fact>
<name>propertyAddress</name>
<nameContents>1280 Somewhere Street, San Diego, WA 98312</nameContents>
</fact>
<fact>
<name>inqZipCode</name>
<nameContents>98312</nameContents>
</fact>
</detailedOutput>
I have an XML like this and I need to read all the values and store it in the table in SQL. I have following as my code:
for rec_facts in (
select EXTRACTVALUE(VALUE(TAB),'/entity/entityLabel/text()') as entity_label
,EXTRACTVALUE(VALUE(TAB),'/entity/entityName/text()') as entity_name
,EXTRACTVALUE(VALUE(TAB),'/entity/entityType/text()') as entity_type
FROM TABLE(XMLSEQUENCE(EXTRACT(SYS.XMLTYPE(v_xmltype),'/creditBureau/product/custom/riskVerificationPlatform/detailedOutput/entity'))) TAB
union all
select null, null, null from dual where not exists (select 1 from table(xmlsequence(extract(sys.xmltype(v_xmltype),'/creditBureau/product/custom/riskVerificationPlatform/detailedOutput/entity')))TAB1)
)
loop
for recout in (
select EXTRACTVALUE(VALUE(TAB2),'/fact/name/text()') as fact_name
,EXTRACTVALUE(VALUE(TAB2),'/fact/nameContents/text()') as fact_nameContents
,EXTRACTVALUE(VALUE(TAB2),'/fact/messageCode/text()') as fact_messageCode
,EXTRACTVALUE(VALUE(TAB2),'/fact/reported/text()') as fact_reported
,EXTRACTVALUE(VALUE(TAB2),'/fact/uniqueID/text()') as fact_uniqueID
,EXTRACTVALUE(VALUE(TAB2),'/fact/instanceID/text()') as fact_instanceID
TABLE(XMLSEQUENCE(EXTRACT(SYS.XMLTYPE(v_xmltype),'/creditBureau/product/custom/riskVerificationPlatform/message'))) TAB2
union all
select null, null, null, null, null, null from dual where not exists (select 1 from table(xmlsequence(extract(sys.xmltype(v_xmltype),'/creditBureau/product/custom/riskVerificationPlatform/message')))TAB2)
)
loop
insert into TUNA_FACTS
(TUNA_FACTS
,TUNA_WEB_REQUEST_RESPONSE
,ENTITY_LABEL
,ENTITY_NAME
,ENTITY_TYPE
,FACT_NAME
,FACT_NAMECONTENTS
,FACT_MESSAGECODE
,FACT_REPORTED
,FACT_UNIQUEID
,FACT_INSTANCEID
,CREATED_ON
,CREATED_BY
,AUDIT_ID
)
values
(seq_TUNA_FACTS.nextval
,v_seq_TUNA_web_req_res
,rec_facts.entity_label
,rec_facts.entity_name
,rec_facts.entity_type
,recout.fact_name
,recout.fact_nameContents
,recout.fact_messageCode
,recout.fact_reported
,recout.fact_uniqueID
,recout.fact_instanceID
,sysdate
,'TUNA'
,user);
end loop;
end loop;
The code runs, but it is repeating each entityLabel, entityName and entityType for 4 times instead of only 2 times. For example the table should be like this:
entityLabel entityName entityType FactName
--------------- ---------- ------------------ ---------------
Policy Holder 1 PHN.1 Policy Holder Name fired
Policy Holder 1 PHN.1 Policy Holder Name fired
property PROP.1 Property PropertyAddress
property PROP.1 Property inqZipCode
instead, it it creating a table like:
entityLabel entityName entityType FactName
--------------- ---------- ------------------ ---------------
Policy Holder 1 PHN.1 Policy Holder Name fired
Policy Holder 1 PHN.1 Policy Holder Name fired
Policy Holder 1 PHN.1 Policy Holder Name PropertyAddress
Policy Holder 1 PHN.1 Policy Holder Name inqZipCode
property PROP.1 Property fired
property PROP.1 Property fired
property PROP.1 Property PropertyAddress
property PROP.1 Property inqZipCode
I am sure it is a looping issue but I cannot figure out. Any help would be appreciated!
The for-loop construct is superfluous here. The single select statement does exactly what you want.
In the examples below I've used the data proposed in the question:
create table xmldata as
with xmldoc as (
select xmlType('
<detailedOutput>
<entity>
<entityLabel>Policy Holder 1</entityLabel>
<entityName>PHN.1</entityName>
<entityType>Policy Holder Name</entityType>
<fact>
<name>fired</name>
<nameContents>Y</nameContents>
<messageCode>M003</messageCode>
<reported>Y</reported>
<uniqueID>C_9_3_1</uniqueID>
<instanceID>1.1</instanceID>
</fact>
<fact>
<name>fired</name>
<nameContents>Y</nameContents>
<messageCode>M010</messageCode>
<reported>Y</reported>
<uniqueID>C_4_3_1</uniqueID>
<instanceID>1.1</instanceID>
</fact>
</entity>
<entity>
<entityLabel>property</entityLabel>
<entityName>PROP.1</entityName>
<entityType>Property</entityType>
<fact>
<name>propertyAddress</name>
<nameContents>1280 Somewhere Street, San Diego, WA 98312</nameContents>
</fact>
<fact>
<name>inqZipCode</name>
<nameContents>98312</nameContents>
</fact>
</entity>
</detailedOutput>
') xml from dual
)
select xml from xmldoc
/
Table XMLDATA created.
The statement with extract() and xmlSequence:
select
extractValue (value(e), '/entity/entityLabel') as "entityLabel",
extractValue (value(e), '/entity/entityName') as "entityName",
extractValue (value(e), '/entity/entityType') as "entityType",
extractValue (value(f), '/fact/name') as "factName"
from xmldata x,
table (xmlSequence(extract(xml, '/detailedOutput/entity'))) e,
table (xmlSequence(extract(value(e), '/entity/fact'))) f
/
Also consider that the xmlSequence has been deprecated and one should use xmlTable instead:
select e."entityLabel", e."entityName", e."entityType", f."factName"
from xmldata x cross join
xmlTable ('/detailedOutput/entity' passing x.xml columns
"entityLabel" varchar2(32) path '/entity/entityLabel',
"entityName" varchar2(32) path '/entity/entityName',
"entityType" varchar2(32) path '/entity/entityType',
facts xmlType path 'fact'
) e cross join
xmlTable ('fact' passing e.facts columns
"factName" varchar2(32) path '/fact/name'
) f
/
The output of both of these statements is the same:
entityLabel entityName entityType factName
---------------- ---------------- ------------------------ ----------------
Policy Holder 1 PHN.1 Policy Holder Name fired
Policy Holder 1 PHN.1 Policy Holder Name fired
property PROP.1 Property propertyAddress
property PROP.1 Property inqZipCode
4 rows selected.
Now the PL/SQL block might look quite simple:
create view tuna_facts_view as <one of both selects suggested above>;
create table tuna_facts as select * from tuna_facts_view where 1=0;
declare
function insertTunaFacts return integer is
begin
insert into tuna_facts select * from tuna_facts_view;
return sql%rowcount;
end insertTunaFacts;
begin
dbms_output.put_line ('inserted rows '||insertTunaFacts ());
end;
/
PL/SQL procedure successfully completed.
inserted rows 4