Solved

selecting from an xmltype table

Posted on 2006-07-12
11
1,110 Views
Last Modified: 2010-05-19
CREATE TABLE XMLTABLE OF XMLType;

INSERT INTO XMLTABLE VALUES(XMLTYPE(getCLOBDocument('FlatPriceChangeFeed3.xml','UTF8')));

select EXTRACTVALUE(?????,'/PDATA/HDR') "HDR"
from XMLTABLE;

Not sure what to put where the ??? marks are?

How can retrieve the data and what other functions can I use
EXTRACT?
COUNT?
Thanks in advance.



CREATE OR REPLACE function getClobDocument(
filename in varchar2,
 charset in varchar2 default NULL)
 return CLOB deterministic
 is
    file            bfile := bfilename('XMLFILES',filename);
    charContent     CLOB := ' ';
    targetFile      bfile;
    lang_ctx        number := DBMS_LOB.default_lang_ctx;
    charset_id      number := 0;
    src_offset      number := 1 ;
    dst_offset      number := 1 ;
    warning         number;
 begin
   if charset is not null then
       charset_id := NLS_CHARSET_ID(charset);
   end if;
   targetFile := file;
   DBMS_LOB.fileopen(targetFile, DBMS_LOB.file_readonly);
   DBMS_LOB.LOADCLOBFROMFILE(charContent, targetFile,
           DBMS_LOB.getLength(targetFile), src_offset, dst_offset,
           charset_id, lang_ctx,warning);
   DBMS_LOB.fileclose(targetFile);
   return charContent;
   /*
   
   INSERT INTO XMLTABLE
  2  VALUES(XMLTYPE(getCLOBDocument('ExtractXML.xml','UTF8')))
 
  */
 end;
/
0
Comment
Question by:dplinnane
  • 8
  • 3
11 Comments
 
LVL 19

Expert Comment

by:actonwang
ID: 17099040
you might do:

select EXTRACTVALUE(SYS_NC_ROWINFO$,'/PDATA/HDR') "HDR"
from XMLTABLE;
0
 
LVL 19

Expert Comment

by:actonwang
ID: 17099049
>> CREATE TABLE XMLTABLE OF XMLType;
      if you create table as this, a column of "SYS_NC_ROWINFO$" will be generated by Oracle.
0
 
LVL 19

Expert Comment

by:actonwang
ID: 17099055
>>How can retrieve the data and what other functions can I use
EXTRACT?
COUNT?

as above.
0
 

Author Comment

by:dplinnane
ID: 17099283
How can you create the column with your own column name instead of SYS_NC_ROWINFO$

This returns a count of 1 but there should be > 2000 amd I doing something wrong.

SELECT COUNT(*)
FROM XMLTABLE
WHERE EXISTSNODE (SYS_NC_ROWINFO$, '/PDATA/EVH') = 1;

WHat part does xmlsequence have to play as That is what I used to count nodes from a clob as follows. Is it used similarly to count from a table.

SELECT COUNT (1)
           INTO idx_counter
           FROM TABLE (XMLSEQUENCE (EXTRACT (xml_data, '/PDATA/EVH')));

Thanks.

What is the best book out there for looking at xml db?
0
 
LVL 19

Expert Comment

by:actonwang
ID: 17099561
run this:

SELECT COUNT(*)
FROM XMLTABLE;

how many record yo have?
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 19

Expert Comment

by:actonwang
ID: 17099571
>>SELECT COUNT (1)
           INTO idx_counter
           FROM TABLE (XMLSEQUENCE (EXTRACT (xml_data, '/PDATA/EVH')));
 
     xmlsequene will create another table type composed of elements under '/PDATA/EVH'.

see http://www.oracle-base.com/articles/9i/XMLSEQUENCE.php
0
 
LVL 19

Expert Comment

by:actonwang
ID: 17099588
>>What is the best book out there for looking at xml db?

just look at Oracle books online:

http://web.njit.edu/info/limpid/DOC/appdev.920/a96616/toc.htm
http://web.njit.edu/info/limpid/DOC/appdev.920/a96620/toc.htm
http://web.njit.edu/info/limpid/DOC/appdev.920/a96621/toc.htm

see other books but not complete and clear as those from Oracle.
0
 

Author Comment

by:dplinnane
ID: 17100225
SELECT COUNT(*)
FROM XMLTABLE;
 yes that fine but I want a cound of nodes with tags EVD and EVH the above will not do that for me.
I'm researching right now but if anyone can send the sythax that will save me time.
Thanks.
0
 
LVL 19

Accepted Solution

by:
actonwang earned 250 total points
ID: 17101492
this is something you want:

select sum(a)
from
(
select( select count (*) from table(xmlsequence(extract(SYS_NC_ROWINFO$, '/PDATA/EVH')))) a
from xmltable
);
0
 
LVL 19

Expert Comment

by:actonwang
ID: 17101510
for EVD, just replace EVH by EVD:

select sum(a)
from
(
select( select count (*) from table(xmlsequence(extract(SYS_NC_ROWINFO$, '/PDATA/EVD')))) a
from xmltable
);
0
 

Author Comment

by:dplinnane
ID: 17101839
Opening another post on performance of extractvalue from a table or string.
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Subquery in Oracle: Sub queries are one of advance queries in oracle. Types of advance queries: •      Sub Queries •      Hierarchical Queries •      Set Operators Sub queries are know as the query called from another query or another subquery. It can …
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 video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.

708 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

14 Experts available now in Live!

Get 1:1 Help Now