?
Solved

selecting from an xmltype table

Posted on 2006-07-12
11
Medium Priority
?
1,193 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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

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

Get quick recovery of individual SharePoint items

Free tool – Veeam Explorer for Microsoft SharePoint, enables fast, easy restores of SharePoint sites, documents, libraries and lists — all with no agents to manage and no additional licenses to buy.

Question has a verified solution.

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

Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
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…
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
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.
Suggested Courses
Course of the Month16 days, 11 hours left to enroll

864 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