Toad World® Forums

Help with reading XML file


#1

Our objective is to write the values of the below XML sample to a table. Can anyone assist us with a syntax sample of how to accomplish this?

Thanks in advance!
Mike

-

-

** ** <Investor_Num>N51</Investor_Num>

** ** <Entity_ID>465</Entity_ID>

** ** <LISA_Field_Name>Party_Giving_Consent_Modifications</LISA_Field_Name>

** ** <Update_To>NA</Update_To>

-

** ** <Investor_Num>N51</Investor_Num>

** ** <Entity_ID>465</Entity_ID>

** **<LISA_Field_Name>Prior_Consent_Required_for_Modification_of_Current_Loans</LISA_Field_Name>

** ** <Update_To>Not Required</Update_To>

We can read it using the below method, but we don’t know how to write them to the table:

pin_submit_xml IN XMLTYPE

SELECT EXTRACTVALUE (VALUE (node), ‘/ROW_DATA/VALUE1’) val1,
TO_NUMBER (EXTRACTVALUE (VALUE (node), ‘/ROW_DATA/ NOTES’) notes
FROM (SELECT pin_submit_xml AS input_xml FROM DUAL),
TABLE(XMLSEQUENCE (EXTRACT (input_xml, ‘/DATA_FROM_CLIENT/ROW_DATA’))) node;

<DATA_FROM_CLIENT xmlns="">
<ROW_DATA>
AAA
aerasdf
</ROW_DATA>
<ROW_DATA>
BBB
dsfdsfd
</ROW_DATA>
</DATA_FROM_CLIENT>