Solved

xml to Oracle Database

Posted on 2014-11-17
3
19 Views
Last Modified: 2016-06-15
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 help me in resolving the issue and getting the data  through this query.

Thanks in advance,
Sunil.
0
Comment
Question by:sunil allada
3 Comments
 
LVL 76

Accepted Solution

by:
slightwv (䄆 Netminder) earned 500 total points
ID: 40447728
extract and extractvalue is deprecated and shouldn't be used anymore.

use XMLTABLE instead.

See if the example below works for you.

Note:  I use CTE (WITH) to get the XML but the query should work fine using bfilename.

Also note I had to tweak the XML you posted.  You had 'Claimno' in one node and 'claimno' in another.

with mydata as (
select xmltype('
<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>') my_xml
 from dual
)
select claimno, inscode, insbal, inspriority
from mydata,
	xmltable(
     '/ATB/CLAIMDATA'
     passing my_xml
       columns
            claimno varchar2(10) path '/CLAIMDATA/Claimno',
            payors xmltype path '/CLAIMDATA/payors'
    ) atb_xml,
    xmltable(
    '/payors/payordata'
    passing payors
    columns
            inscode varchar2(10) path '/payordata/inscode',
            insbal varchar2(10) path '/payordata/insbal',
            inspriority varchar2(10) path '/payordata/inspriority'
    )
/

Open in new window

0
 

Author Comment

by:sunil allada
ID: 40466768
Thank you so much.  Let me try that and will come back if I  get any error
0

Featured Post

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now