Solved

xml to Oracle Database

Posted on 2014-11-17
3
42 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 77

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

Webinar: Aligning, Automating, Winning

Join Dan Russo, Senior Manager of Operations Intelligence, for an in-depth discussion on how Dealertrack, leading provider of integrated digital solutions for the automotive industry, transformed their DevOps processes to increase collaboration and move with greater velocity.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
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
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

837 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