selecting from an xmltype table

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;
/
dplinnaneAsked:
Who is Participating?
 
actonwangConnect With a Mentor Commented:
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
 
actonwangCommented:
you might do:

select EXTRACTVALUE(SYS_NC_ROWINFO$,'/PDATA/HDR') "HDR"
from XMLTABLE;
0
 
actonwangCommented:
>> CREATE TABLE XMLTABLE OF XMLType;
      if you create table as this, a column of "SYS_NC_ROWINFO$" will be generated by Oracle.
0
Cloud Class® Course: CompTIA Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

 
actonwangCommented:
>>How can retrieve the data and what other functions can I use
EXTRACT?
COUNT?

as above.
0
 
dplinnaneAuthor Commented:
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
 
actonwangCommented:
run this:

SELECT COUNT(*)
FROM XMLTABLE;

how many record yo have?
0
 
actonwangCommented:
>>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
 
actonwangCommented:
>>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
 
dplinnaneAuthor Commented:
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
 
actonwangCommented:
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
 
dplinnaneAuthor Commented:
Opening another post on performance of extractvalue from a table or string.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.