Home » Developer & Programmer » JDeveloper, Java & XML » XML Extract Using XMLTable (Oracle 10.2)
XML Extract Using XMLTable [message #561547] Mon, 23 July 2012 14:59 Go to next message
ravikiran.kv@gmail.com
Messages: 11
Registered: July 2012
Junior Member
Trying to extract name and ID from the below XML File using XMLTable but it is returning no data

XML File is - please check attachment

Query I am using:
select p."uid",p."uname" from warehouses w,
xmltable('/ArrayOfAnyType/anyType'
passing w.warehouse_spec
columns "uname" varchar2(100) PATH '/name',"uid" varchar2(100) path '/id'
)p where warehouse_id = 6;

Please guide me on this. Thanks in advance for your help
  • Attachment: test1.txt
    (Size: 0.62KB, Downloaded 98 times)
Re: XML Extract Using XMLTable [message #561549 is a reply to message #561547] Mon, 23 July 2012 15:31 Go to previous messageGo to next message
BlackSwan
Messages: 22718
Registered: January 2009
Senior Member
Please read and follow the forum guidelines, to enable us to help you:

http://www.orafaq.com/forum/t/88153/0/
Re: XML Extract Using XMLTable [message #561572 is a reply to message #561549] Tue, 24 July 2012 01:29 Go to previous messageGo to next message
_jum
Messages: 490
Registered: February 2008
Senior Member
Here a first approach with your (slightly modified) data and query:
WITH warehouses AS 
 (SELECT XMLTYPE('
<ArrayOfAnyType xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
  <anyType xsi:type="Data">
    <name>John</name>
    <id>xxxx</id>
    <No>123</No>
    <Division />
    <Days>0</Days>
    <PTO_Days>0</PTO_Days>
    <Transfer_Date />
    <CURRENT_LOCATION>US</CURRENT_LOCATION>
  </anyType>
  <anyType xsi:type="Data">
    <name>Jim</name>
    <id>PPPP</id>
    <No>900</No>
    <Division />
    <Days>0</Days>
    <PTO_Days>0</PTO_Days>
    <Transfer_Date />
    <CURRENT_LOCATION>UK</CURRENT_LOCATION>
  </anyType>
</ArrayOfAnyType>') warehouse_spec FROM dual)
SELECT p."uid", p."uname"
  FROM warehouses w,
       XMLTABLE (
         '/ArrayOfAnyType/anyType'
         PASSING w.warehouse_spec
         COLUMNS "uname" VARCHAR2 (100) PATH '//name',
                 "uid"   VARCHAR2 (100) PATH '//id') p
-- WHERE warehouse_id = 6;

uid     name
---------------
xxxx	John
PPPP	Jim

There is a problem in the last namespace and no warehouse_id to query for.
Re: XML Extract Using XMLTable [message #561593 is a reply to message #561572] Tue, 24 July 2012 02:26 Go to previous messageGo to next message
ravikiran.kv@gmail.com
Messages: 11
Registered: July 2012
Junior Member
THanks _jum for your advice

When I was trying to execute the query using Toad.Seeing an error as

ORA-29900: operator binding does not exist
ORA-06540: PL/SQL: compilation error
ORA-06553: PLS-907: cannot load library unit SYS.XQSEQUENCEFROMXMLTYPE (referenced by SYS.XQSEQUENCE)

Oracle Version is - Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bi. Could you please help on this
Re: XML Extract Using XMLTable [message #561596 is a reply to message #561547] Tue, 24 July 2012 02:35 Go to previous messageGo to next message
Michel Cadot
Messages: 58938
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
SQL> With data as ( select xmltype(
  2  '<ArrayOfAnyType xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" 
  3  xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns="http://tempuri.org/">
  4    <anyType xsi:type="Data">
  5      <name>John</name>
  6      <id>xxxx</id>
  7      <No>123</No>
  8      <Division />
  9      <Days>0</Days>
 10      <PTO_Days>0</PTO_Days>
 11      <Transfer_Date />
 12      <CURRENT_LOCATION>US</CURRENT_LOCATION>
 13    </anyType>
 14    <anyType xsi:type="Data">
 15      <name>Jim</name>
 16      <id>PPPP</id>
 17      <No>900</No>
 18      <Division />
 19      <Days>0</Days>
 20      <PTO_Days>0</PTO_Days>
 21      <Transfer_Date />
 22      <CURRENT_LOCATION>UK</CURRENT_LOCATION>
 23    </anyType>
 24  </ArrayOfAnyType>') val from dual )
 25  select extractValue(value(x), '//name',
 26                 'xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" 
 27                  xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns="http://tempuri.org/"') 
 28           name,
 29         extractValue(value(x), '//id',
 30                 'xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" 
 31                  xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns="http://tempuri.org/"') 
 32           id
 33  from data, table(xmlsequence(extract(val, '//anyType',
 34                 'xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" 
 35                  xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns="http://tempuri.org/"'))) x
 36  /
NAME                 ID
-------------------- ----------
John                 xxxx
Jim                  PPPP

Regards
Michel
Re: XML Extract Using XMLTable [message #561687 is a reply to message #561547] Tue, 24 July 2012 13:58 Go to previous messageGo to next message
ravikiran.kv@gmail.com
Messages: 11
Registered: July 2012
Junior Member
Thanks Michel and _jum
It worked when I execute queries using sql developer with version details as follows
Java(TM) Platform 1.6.0_11
Oracle IDE 3.1.07.42
Versioning Support 3.1.07.42

and DB version as "Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production"

But when I run the same in Toad with DB Version as
Oracle Version is - Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bi getting an error

ORA-29900: operator binding does not exist
ORA-06540: PL/SQL: compilation error
ORA-06553: PLS-907: cannot load library unit SYS.XQSEQUENCEFROMXMLTYPE (referenced by SYS.XQSEQUENCE)

Is it because of some lib files missing in Oracle 10.2? I read in some forum that XMLTABLE are available from 10.2.So i guess it should work.Could you please guide me what went wrong.
Re: XML Extract Using XMLTable [message #561688 is a reply to message #561687] Tue, 24 July 2012 14:09 Go to previous messageGo to next message
Michel Cadot
Messages: 58938
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Which one did you execute?
Execute it with SQL*Plus; do you get the error?
Copy and paste what you do and get as we did it.

Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code, use code tags and align the columns in result.
Use the "Preview Message" button to verify.

By the way, I did it in 10.2.0.4.

Regards
Michel

[Updated on: Wed, 25 July 2012 00:48]

Report message to a moderator

Re: XML Extract Using XMLTable [message #561695 is a reply to message #561687] Tue, 24 July 2012 15:09 Go to previous messageGo to next message
Solomon Yakobson
Messages: 2007
Registered: January 2010
Senior Member
Post results of:

COLUMN COMP_NAME FORMAT A40
SELECT  COMP_NAME,
        VERSION,
        STATUS
  FROM  DBA_REGISTRY
/


SY.
Re: XML Extract Using XMLTable [message #561696 is a reply to message #561695] Tue, 24 July 2012 15:13 Go to previous messageGo to next message
BlackSwan
Messages: 22718
Registered: January 2009
Senior Member
13:12:58 SQL> set lines 132
13:13:14 SQL> /

COMP_NAME				 VERSION			STATUS
---------------------------------------- ------------------------------ --------------------------------------------
OWB					 11.2.0.1.0			VALID
Oracle Application Express		 3.2.1.00.10			VALID
Oracle Enterprise Manager		 11.2.0.1.0			VALID
OLAP Catalog				 11.2.0.1.0			VALID
Spatial 				 11.2.0.1.0			VALID
Oracle Multimedia			 11.2.0.1.0			VALID
Oracle XML Database			 11.2.0.1.0			VALID
Oracle Text				 11.2.0.1.0			VALID
Oracle Expression Filter		 11.2.0.1.0			VALID
Oracle Rules Manager			 11.2.0.1.0			VALID
Oracle Workspace Manager		 11.2.0.1.0			VALID

COMP_NAME				 VERSION			STATUS
---------------------------------------- ------------------------------ --------------------------------------------
Oracle Database Catalog Views		 11.2.0.1.0			VALID
Oracle Database Packages and Types	 11.2.0.1.0			VALID
JServer JAVA Virtual Machine		 11.2.0.1.0			VALID
Oracle XDK				 11.2.0.1.0			VALID
Oracle Database Java Packages		 11.2.0.1.0			VALID
OLAP Analytic Workspace 		 11.2.0.1.0			VALID
Oracle OLAP API 			 11.2.0.1.0			VALID

18 rows selected.

13:13:16 SQL> 
Re: XML Extract Using XMLTable [message #561697 is a reply to message #561696] Tue, 24 July 2012 15:16 Go to previous messageGo to next message
Solomon Yakobson
Messages: 2007
Registered: January 2010
Senior Member
Are you saying it doesn't work for you either?

SY.
Re: XML Extract Using XMLTable [message #561744 is a reply to message #561596] Wed, 25 July 2012 02:05 Go to previous messageGo to next message
ravikiran.kv@gmail.com
Messages: 11
Registered: July 2012
Junior Member
Sorry Michel I was following your guidelines to upload the error message but getting and error as "You cannot use links until you have posted more than 5 messages". So prepare a text file as an attachment.

THe following are version Details.

SQL*PLUS Version is:10.2.0.4.0

DB Version Details
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bi
PL/SQL Release 10.2.0.1.0 - Production
CORE 10.2.0.1.0 Production
TNS for 64-bit Windows: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production

Please check the attachment for the execution process I followed for both approaches.
Re: XML Extract Using XMLTable [message #561747 is a reply to message #561744] Wed, 25 July 2012 02:22 Go to previous messageGo to next message
Michel Cadot
Messages: 58938
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
We did not use any attachment or link to post our SQL*Plus session, so please do the same thing.
And also post the result of the statement John gave like BlackSwan did.

Regards
Michel

[Updated on: Wed, 25 July 2012 02:23]

Report message to a moderator

Re: XML Extract Using XMLTable [message #561784 is a reply to message #561747] Wed, 25 July 2012 05:35 Go to previous messageGo to next message
ravikiran.kv@gmail.com
Messages: 11
Registered: July 2012
Junior Member
SQL> COLUMN COMP_NAME FORMAT A40
SQL> SELECT  COMP_NAME,
  2          VERSION,
  3          STATUS
  4    FROM  DBA_REGISTRY
  5  /
  FROM  DBA_REGISTRY
        *
ERROR at line 4:
ORA-00942: table or view does not exist
Re: XML Extract Using XMLTable [message #561785 is a reply to message #561784] Wed, 25 July 2012 05:36 Go to previous messageGo to next message
ravikiran.kv@gmail.com
Messages: 11
Registered: July 2012
Junior Member
Approach -1

SQL>  WITH warehouses AS 
  2       (SELECT XMLTYPE('
  3      <ArrayOfAnyType xmlns:xsi="http://www.w3.
org/2001/XMLSchema-instance" xmlns:xsd="http://www
.w3.org/2001/XMLSchema">
  4        <anyType xsi:type="Data">
  5          <name>John</name>
  6          <id>xxxx</id>
  7          <No>123</No>
  8          <Division />
  9          <Days>0</Days>
 10         <PTO_Days>0</PTO_Days>
 11         <Transfer_Date />
 12         <CURRENT_LOCATION>US</CURRENT_LOCATION>
 13       </anyType>
 14       <anyType xsi:type="Data">
 15         <name>Jim</name>
 16         <id>PPPP</id>
 17         <No>900</No>
 18         <Division />
 19         <Days>0</Days>
 20         <PTO_Days>0</PTO_Days>
 21         <Transfer_Date />
 22         <CURRENT_LOCATION>UK</CURRENT_LOCATION>
 23       </anyType>
 24     </ArrayOfAnyType>') warehouse_spec FROM dual)
 25     SELECT p."uid", p."uname"
 26       FROM warehouses w,
 27            XMLTABLE (
 28              '/ArrayOfAnyType/anyType'
 29             PASSING w.warehouse_spec
 30              COLUMNS "uname" VARCHAR2 (100) PATH '//name',
 31                      "uid"   VARCHAR2 (100) PATH '//id') p
 32     /
        <No>123</No>
           *
ERROR at line 7:
ORA-29900: operator binding does not exist
ORA-06540: PL/SQL: compilation error
ORA-06553: PLS-907: cannot load library unit SYS.XQSEQUENCEFROMXMLTYPE
(referenced by SYS.XQSEQUENCE)


====================================
Appraoch -2
SQL> With data as ( select xmltype(
  2     '<ArrayOfAnyType xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"  
  3      xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns="http://tempuri.org/">
  4      <anyType xsi:type="Data">
  5      <name>John</name>
  6      <id>xxxx</id>
  7      <No>123</No>
  8      <Division />
  9      <Days>0</Days>
 10      <PTO_Days>0</PTO_Days>
 11      <Transfer_Date />
 12      <CURRENT_LOCATION>US</CURRENT_LOCATION>
 13      </anyType>
 14      <anyType xsi:type="Data">
 15      <name>Jim</name>
 16      <id>PPPP</id>
 17     <No>900</No>
 18      <Division />
 19      <Days>0</Days>
 20      <PTO_Days>0</PTO_Days>
 21      <Transfer_Date />
 22      <CURRENT_LOCATION>UK</CURRENT_LOCATION>
 23      </anyType>
 24      </ArrayOfAnyType>') val from dual )
 25       select extractValue(value(x), '//name',
 26                   'xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" 
 27                     xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns="http://tempuri.org/"') 
 28               name,
 29             extractValue(value(x), '//id',
 30                 'xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" 
 31                      xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns="http://tempuri.org/"') 

 32               id
 33       from data, table(xmlsequence(extract(val, '//anyType',
 34                      'xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" 
 35                       xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns="http://tempuri.org/"')
))x
 36  /
     from data, table(xmlsequence(extract(val, '//anyType',
                      *
ERROR at line 33:
ORA-29900: operator binding does not exist
ORA-06540: PL/SQL: compilation error
ORA-06553: PLS-907: cannot load library unit SYS.XMLSEQUENCEFROMXMLTYPE(referenced by SYS.XMLSEQUENCE)


Re: XML Extract Using XMLTable [message #561789 is a reply to message #561784] Wed, 25 July 2012 05:54 Go to previous messageGo to next message
Michel Cadot
Messages: 58938
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
ravikiran.kv@gmail.com wrote on Wed, 25 July 2012 12:35
SQL> COLUMN COMP_NAME FORMAT A40
SQL> SELECT  COMP_NAME,
  2          VERSION,
  3          STATUS
  4    FROM  DBA_REGISTRY
  5  /
  FROM  DBA_REGISTRY
        *
ERROR at line 4:
ORA-00942: table or view does not exist


Ask your DBA to query it and also:
select * from v$version;
And copy and paste his session here.

Regards
Michel
Re: XML Extract Using XMLTable [message #562143 is a reply to message #561785] Sun, 29 July 2012 11:28 Go to previous messageGo to next message
Michel Cadot
Messages: 58938
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
No feedback?
Did you solve your problem?

Regards
Michel
Re: XML Extract Using XMLTable [message #562167 is a reply to message #562143] Mon, 30 July 2012 02:28 Go to previous messageGo to next message
ravikiran.kv@gmail.com
Messages: 11
Registered: July 2012
Junior Member
SQL> /
COMP_NAME               				VERSION                        STATUS
Oracle Data Mining      				10.2.0.1.0                     INVALID
OLAP Catalog            				10.2.0.1.0                     INVALID
Oracle Ultra Search     				 9.2.0.1.0                     NO SCRIPT
Oracle Text            					10.2.0.1.0                     VALID
Spatial                 				10.2.0.1.0                     VALID
Oracle interMedia       				10.2.0.1.0                     INVALID
Oracle Workspace Manager                                10.2.0.1.0                     VALID
Oracle Database Catalog Views                           10.2.0.1.0                     VALID
Oracle Database Packages and Types                      10.2.0.1.0                     VALID
JServer JAVA Virtual Machine                            10.2.0.1.0                     VALID
Oracle XDK					        10.2.0.1.0                     VALID
Oracle Database Java Packages                           10.2.0.1.0                     VALID
OLAP Analytic Workspace                                 10.2.0.1.0                     VALID
Oracle OLAP API				                10.2.0.1.0                     VALID
14 rows selected.
Re: XML Extract Using XMLTable [message #562170 is a reply to message #562167] Mon, 30 July 2012 02:44 Go to previous messageGo to next message
Michel Cadot
Messages: 58938
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
select owner, object_name, object_type from dba_objects 
where status != 'VALID' and owner like '%SYS%' order by 1, 2, 3
/

Regards
Michel
Re: XML Extract Using XMLTable [message #562384 is a reply to message #562170] Wed, 01 August 2012 02:53 Go to previous messageGo to next message
ravikiran.kv@gmail.com
Messages: 11
Registered: July 2012
Junior Member
select owner, object_name, object_type from dba_objects 
where status != 'VALID' and owner like '%SYS%' order by 1, 2, 3;

Query output is right here (prepare for almost 10.000 lines!)


[EDITED by LF: applied [spoiler] tags]

[Updated on: Wed, 01 August 2012 03:28] by Moderator

Report message to a moderator

Re: XML Extract Using XMLTable [message #562385 is a reply to message #562384] Wed, 01 August 2012 03:00 Go to previous messageGo to next message
Michel Cadot
Messages: 58938
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
This is too hard to read, please reexecute using the following settings:
set lines 90
col object_name format a30
set pages 10000
set trimout on
set trimspool on

Regards
Michel
Re: XML Extract Using XMLTable [message #562387 is a reply to message #562385] Wed, 01 August 2012 03:41 Go to previous messageGo to next message
ravikiran.kv@gmail.com
Messages: 11
Registered: July 2012
Junior Member
Nicely formatted query output is right here (puny ~1200 lines)


[EDITED by LF: applied [spoiler] tags to cut the long story short]

[Updated on: Wed, 01 August 2012 03:51] by Moderator

Report message to a moderator

Re: XML Extract Using XMLTable [message #562396 is a reply to message #562387] Wed, 01 August 2012 04:17 Go to previous messageGo to next message
Michel Cadot
Messages: 58938
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Almost all SYS packages are invalid including XML functions!
First, as SYS, reexecute (in Windows notation):
%ORACLE_HOME%\rdbms\admin\catalog.sql
%ORACLE_HOME%\rdbms\admin\catproc.sql
then come back with the following:
select owner, object_type, count(*) from dba_objects 
where status != 'VALID' and owner like '%SYS%' order by 1, 2;

Regards
Michel
Re: XML Extract Using XMLTable [message #562417 is a reply to message #562396] Wed, 01 August 2012 07:27 Go to previous messageGo to next message
ravikiran.kv@gmail.com
Messages: 11
Registered: July 2012
Junior Member
What are the XML packages among the list I gave above?
Re: XML Extract Using XMLTable [message #562429 is a reply to message #562417] Wed, 01 August 2012 07:51 Go to previous messageGo to next message
Michel Cadot
Messages: 58938
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
What is the purpose of the question?
Your database is a total mess, try to put it in a correct state then xml will work fine.
So do what I said.

Regards
Michel
Re: XML Extract Using XMLTable [message #562540 is a reply to message #562429] Thu, 02 August 2012 01:42 Go to previous message
ravikiran.kv@gmail.com
Messages: 11
Registered: July 2012
Junior Member
Just want to know what are the basic things I need to check for XML Functions. SO asked it.Need to work with DBA on the query. Will post the results once I get that info from them
Previous Topic: Best Driver
Next Topic: Tnsnames doubt
Goto Forum:
  


Current Time: Fri Aug 29 20:05:53 CDT 2014

Total time taken to generate the page: 0.14579 seconds