Solved

selecting from an xmltype table

Posted on 2006-07-12
11
1,130 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
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.

 

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
 
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.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Converting a row into a column 2 60
execute immediate plsql block 5 57
exp/imp 25 84
having some issue on pl sql procedure 1 23
Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
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…
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.
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database

790 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