Solved

selecting from an xmltype table

Posted on 2006-07-12
11
1,159 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 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

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

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 video shows how to recover a database from a user managed backup
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

726 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