empty rows when loading 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. 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