Home » SQL & PL/SQL » SQL & PL/SQL » Extract from unconventional XML. (Oracle 10g)
Extract from unconventional XML. [message #281985] Tue, 20 November 2007 04:45 Go to next message
ramya.pathak
Messages: 15
Registered: August 2007
Junior Member
Hi,

I have an xml file from which I need to extract data to insert into a relational db table.
I have read the docs on the internet related to XML Parsiing n all. But to no use. The Common XML's used are like Attached in EMP.XML..!!

But in my application I have an XML which looks like this:

<MGGCDR xmlns="<Some_Link>" xmlns:xsi="Some_Schema_Instance" xsi:schemaLocation="Some_Schema_Location">
<Table1 Column1="zzz" Column2="yyy" Column3="xxx">
<Table2>
<Table3 Column1="A23Cb" />
</Table2>
<Table4>
<Table4_EXT Column1="1"/>
</Table4>
<Table5>
<Table5_EXT Column="AA34BB"/>
</Table5>
<Table6>
<Table7>
<Table7_EXT Column1="1"/>
</Table7>
<Table8 SettlementDate="2007-11-07+05:30"/>
</Table6>
<Table9 PaidDate="10-Jan-07"/>
</Table1>
</MGGCDR>

Can anyone please help me how do I extract the data from the columns.?? I need almost all the data stored in the XML..

Thanks in advance,
Ramya.
  • Attachment: emp.xml
    (Size: 1.89KB, Downloaded 127 times)
Re: Extract from unconventional XML. [message #281994 is a reply to message #281985] Tue, 20 November 2007 05:34 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
If you XML does not follow the Oracle one you have to create your own parser.

Regards
Michel
Re: Extract from unconventional XML. [message #282044 is a reply to message #281985] Tue, 20 November 2007 09:56 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
It looks like XML to me and my database.
I'd use EXTRACT and EXTRACTVALUE to get the data out personally.
create table xml_tab (col_1 number, xml xmltype);

insert into xml_Tab values (1,xmltype('<MGGCDR xmlns="" xmlns:xsi="Some_Schema_Instance" xsi:schemaLocation="Some_Schema_Location">
<Table1 Column1="zzz" Column2="yyy" Column3="xxx">
<Table2>
<Table3 Column1="A23Cb" />
</Table2>
<Table4>
<Table4_EXT Column1="1"/>
</Table4>
<Table5>
<Table5_EXT Column="AA34BB"/>
</Table5>
<Table6>
<Table7>
<Table7_EXT Column1="1"/>
</Table7>
<Table8 SettlementDate="2007-11-07+05:30"/>
</Table6>
<Table9 PaidDate="10-Jan-07"/>
</Table1>
</MGGCDR>'));

select extractvalue(xml,'MGGCDR/Table1/@Column1') from xml_tab;

EXTRACTVALUE(XML,'MGGCDR/TABLE1/@COLUMN1')
------------------------------------------
zzz                                       

1 rows selected

select xmltype.getstringval(extract(xml,'MGGCDR/Table1/Table6')) from xml_Tab;

XMLTYPE.GETSTRINGVAL(EXTRACT(XML,'MGGCDR/TABLE1/TABLE6'))
---------------------------------------------------------
<Table6 xmlns=""><Table7><Table7_EXT Column1="1"/></Table7><Table8 SettlementDate="2007-11-07+05:30"/></Table6>

1 rows selected
Previous Topic: union all in pro *C
Next Topic: Recycle sequence
Goto Forum:
  


Current Time: Sat Dec 03 10:16:25 CST 2016

Total time taken to generate the page: 0.10155 seconds