Help with reading XML file

Toad for Oracle
1

Message from: MThomas

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

N51

465

Party_Giving_Consent_Modifications

NA

N51

465

Prior_Consent_Required_for_Modification_of_Current_Loans
LISA_Field_Name >

Not Required

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 ;

AAA
aerasdf

BBB
dsfdsfd

Historical Messages

Author: Mike Thomas
Date: Wed Jun 30 07:24:03 PDT 2010
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

N51

465

Party_Giving_Consent_Modifications

NA

N51

465

Prior_Consent_Required_for_Modification_of_Current_Loans
LISA_Field_Name >

Not Required

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 ;

AAA
aerasdf

BBB
dsfdsfd

__

2

I’m no XML expert, but any time you can get the information you want in a
Select statement

SELECT something VAL1, another NOTES FROM some-sources;

you can then insert it into an existing table like this:

INSERT INTO target-table (target-columnname1, target-columnname2)

SELECT something VAL1, another NOTES FROM some-sources;

If the table doesn’t already exist, you can in fact create it at the time
you populate it:

CREATE TABLE target-table

AS SELECT something VAL1, another NOTES FROM some-sources;

Nate Schroeder

US Seed & Trait Commercial IT - Data Management Team

Monsanto Company

800 N. Lindbergh Blvd. LC4D - Saint Louis, MO - 63167

314-694-2592