Please help me out loading an XML file into oracle table. Please help me out loading an XML file into oracle table. Please help me out loading an XML file into oracle table.
My XML file: textfile.xml
<ATB>
<CLAIMDATA oid="1">
<Claimno>101</Claimno>
<patname>John,Miller</patname>
<payors>
<payordata>
<inscode>MCR</inscode>
<insbal>100.66</insbal>
<inspriority>1</inspriority>
</payordata>
<payordata>
<inscode>BCBS</inscode>
<insbal>200.20</insbal>
<inspriority>2</inspriority>
</payordata>
</payors>
</CLAIMDATA>
<CLAIMDATA oid="2">
<claimno>102</claimno>
<patname>Louis,Phillipe</patname>
<payors>
<payordata>
<inscode>TUFTS</inscode>
<insbal> 1010.50</insbal>
<inspriority>1</inspriority>
</payordata>
</payors>
</CLAIMDATA>
</ATB>
I have written the following query which gives me two empty rows with out any data.
WITH g AS (SELECT xmltype(bfilename('SAMP_XML_DIR','textfile.xml'), nls_charset_id('WE8ISO8859P1')) xmlcol1 FROM dual)
SELECT
extractValue(value(x),'ATB/CLAIMDATA/Claimno') claimno
,extractValue(value(x),'ATB/CLAIMDATA/patname') patname
,extractValue(value(x),'ATB/CLAIMDATA/payors/payordata/inscode[1]') ins1code
,extractValue(value(x),'ATB/CLAIMDATA/payors/payordata/insbal[1]') ins1bal
,extractValue(value(x),'ATB/CLAIMDATA/payors/payordata/inspriority[1]') ins1priority
,extractValue(value(x),'ATB/CLAIMDATA/payors/payordata/inscode[2]') ins2code
,extractValue(value(x),'ATB/CLAIMDATA/payors/payordata/insbal[2]') ins2bal
,extractValue(value(x),'ATB/CLAIMDATA/payors/payordata/inspriority[2]') ins2priority
FROM g,TABLE(XMLSequence(extract(g.xmlcol1,'ATB/CLAIMDATA'))) x;
Please let me know how can I get data
Thanks,
Sunil
No comments:
Post a Comment