Home » SQL & PL/SQL » SQL & PL/SQL » Extract value from xml (Oracle 12c)
Extract value from xml [message #654244] Thu, 28 July 2016 13:14 Go to next message
Raj12345
Messages: 1
Registered: July 2016
Junior Member
Hi Experts,

Need you quick and urgent help on the below.

I have a log table with huge number of records in,130 Millions which has a column of XMLTYPE and contains XML.
Another table (EMP) which needs to be joined to it to extract value of 4 nodes from the above table.

I have tried using the extractvalue function and the XML table with parallel but it takes a lot of time when the records in EMP table increases.
Could you please help how it can be done quickly or any other alternative

create table temp
as (
WITH mydata as (select data xx from log_table log where emp.id = log.id)
select * from mydata,xmltable('*/*[local-name()=''partya''][@id=''123'']'
passing mydata.xx
columns party_a_name varchar2(20) PATH '/',
issuer varchar2(1000) PATH '//@idscheme'
)

This takes around 27~30 mins to create the above table,when emp has around 1L records.

Name Rows
Operation
SELECT STATEMENT 661182611
NESTED LOOPS 661182611
NESTED LOOPS 80948
TABLE ACCESS FULL EMP 77816
TABLE ACCESS BY INDEX ROWID BATCHED Log_table 1
INDEX RANGE SCAN RRLD_UK1 1
COLLECTION ITERATOR PICKLER FETCH XQSEQUENCEFROMXMLTYPE 8168


Kindly excuse the brevity and typo

Report message to a moderator

Re: Extract value from xml [message #654248 is a reply to message #654244] Thu, 28 July 2016 13:32 Go to previous messageGo to next message
Michel Cadot
Messages: 68757
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator

1L? 1 liter?

The only thing you can do (apart from using faster hardware) is to give the complete and exact path instead of */* or //.

And the moderator bit:
Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Also always post your Oracle version, with 4 decimals, as solution depends on it.

With any SQL or PL/SQL question, please, Post a working Test case: create table (including all constraints) and insert statements along with the result you want with these data then we will be able work with your table and data. Explain with words and sentences the rules that lead to this result.

For any performances question, please read http://www.orafaq.com/forum/mv/msg/84315/433888/102589/#msg_433888 and post the required information.

Report message to a moderator

Re: Extract value from xml [message #654250 is a reply to message #654248] Thu, 28 July 2016 13:45 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
https://community.oracle.com/thread/3955198

Report message to a moderator

Re: Extract value from xml [message #654253 is a reply to message #654250] Thu, 28 July 2016 14:12 Go to previous message
Bill B
Messages: 1971
Registered: December 2004
Senior Member
Why not store your XML into an XMLType column. The following link talks about it and you can simply use the values in the XML in update/select queries

http://docs.oracle.com/cd/B28359_01/appdev.111/b28369/xdb03usg.htm

Report message to a moderator

Previous Topic: place set values next to each other
Next Topic: Creating different worksheets in a single excel
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Sat May 24 02:31:10 CDT 2025
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.