Read nested XML in Oracle (SQL)

Question

<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!

@a_horse_with_no_name I am using Oracle SQL Developer for this — NIRMIT PATEL
– NIRMIT PATEL, Commented Oct 24, 2017 at 20:27
It seems that your code is not complete. I dont see exit from outer loop. Also you cannt run without begin-end block. — 0xdb
– 0xdb, Commented Oct 24, 2017 at 21:35
@0xdb Yeah I just didn't post an entire code as it is a little large since there is too many information we are taking out of the xml. I have posted this as an overview of what I am trying to achieve and which route am I taking. — NIRMIT PATEL
– NIRMIT PATEL, Commented Oct 24, 2017 at 21:55

0xdb · Accepted Answer · 2017-12-24 18:44:14Z

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

Thank you @0xdb, that actually solved my problem. I used the logic you provided and it works just fine.

Collectives™ on Stack Overflow

Read nested XML in Oracle (SQL)

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related