Home » Developer & Programmer » JDeveloper, Java & XML » XML 'IN' list (Oracle 9.2.0.5.0)
XML 'IN' list [message #332972] Thu, 10 July 2008 03:56 Go to next message
srraajesh
Messages: 63
Registered: May 2005
Member
Hi Gurus,

I'm trying to push XML data into a table and read it as a 'IN' list in a SQL. This is what I've done so far.
SQL> select * from v$version
  2  /

BANNER
----------------------------------------------------------------
Oracle9i Enterprise Edition Release 9.2.0.5.0 - 64bit Production
PL/SQL Release 9.2.0.5.0 - Production
CORE    9.2.0.6.0       Production
TNS for Solaris: Version 9.2.0.5.0 - Production
NLSRTL Version 9.2.0.5.0 - Production

SQL> create table test
  2  (
  3  tst_fld XMLTYPE
  4  )
  5  /

Table created.

SQL> insert into test
  2  select XMLELEMENT("TABLES",xmlagg(XMLELEMENT("TABLENAME",TABLE_NAME))) from
 all_tables where rownum < 5
  3  /

1 row created.

SQL> commit;

Commit complete.

SQL> select * from test
  2  /

TST_FLD
--------------------------------------------------------------------------------
<TABLES><TABLENAME>TAB$</TABLENAME><TABLENAME>USER$</TABLENAME><TABLENAME>BOOTSTRAP$</TABLENAME><TAB
LENAME>UNDO$</TABLENAME></TABLES>


Now, If I give

select extract(tst_fld,'TABLES/TABLENAME') from test



I can get the 'TABLENAME' as a set of nodes. Is it possible to give the values directly as a list to a SQL query. Something like

select * from tablea where fld1 in (<XML node values related to 'TABLENAME'>)



I hope I've made it clear. Please let me know otherwise.

[Updated on: Thu, 10 July 2008 04:04] by Moderator

Report message to a moderator

Re: XML 'IN' list [message #332974 is a reply to message #332972] Thu, 10 July 2008 03:59 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Use code tags and not quote ones to post code.

Post a test case (create table and insert statements) we can work with.

Regards
Michel
Re: XML 'IN' list [message #332975 is a reply to message #332972] Thu, 10 July 2008 04:05 Go to previous messageGo to next message
srraajesh
Messages: 63
Registered: May 2005
Member
hi,

create table and test case are already there.

regards
Re: XML 'IN' list [message #332977 is a reply to message #332975] Thu, 10 July 2008 04:10 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Ooops! Before I reformatted your post I didn't see it. Wink

Regards
Michel
Re: XML 'IN' list [message #333060 is a reply to message #332977] Thu, 10 July 2008 07:03 Go to previous messageGo to next message
rajatratewal
Messages: 507
Registered: March 2008
Location: INDIA
Senior Member
srraajesh wrote on Thu, 10 July 2008 03:56
select * from 
tablea 
where 
fld1 in 
(<XML node values related to 'TABLENAME'>)


fld1 is of xmltype or something else.

Regards,
Rajat
Re: XML 'IN' list [message #333065 is a reply to message #332972] Thu, 10 July 2008 07:20 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
SQL> create table test (tst_fld XMLTYPE);

Table created.

SQL> insert into test
  2  select XMLELEMENT("TABLES",xmlagg(XMLELEMENT("TABLENAME",TABLE_NAME))) 
  3  from all_tables 
  4  where rownum < 5
  5  /

1 row created.

SQL> commit;

Commit complete.

SQL> select * from test;
TST_FLD
--------------------------------------------------------------------------------
<TABLES><TABLENAME>CON$</TABLENAME><TABLENAME>UNDO$</TABLENAME><TABLENAME>CDEF$<
/TABLENAME><TABLENAME>CCOL$</TABLENAME></TABLES>

1 row selected.

SQL> select extractvalue(value(a), '/TABLENAME') tn
  2  from test, table(xmlsequence(extract(tst_fld, '/TABLES/TABLENAME'))) a
  3  /
TN
--------------------------------------------------------------------------------
CON$
UNDO$
CDEF$
CCOL$

4 rows selected.

Regards
Michel
Re: XML 'IN' list [message #333351 is a reply to message #333060] Fri, 11 July 2008 06:17 Go to previous messageGo to next message
srraajesh
Messages: 63
Registered: May 2005
Member
fld1 is a normal DB field. It is not of XML type.

Regards
Re: XML 'IN' list [message #333352 is a reply to message #333065] Fri, 11 July 2008 06:22 Go to previous messageGo to next message
srraajesh
Messages: 63
Registered: May 2005
Member
Michel,

One more slight twist..Suppose that I have the data like this


<root>
<b1>1,2,3,4,5</b1>
<b2>2,3,4,5,6</b2>
</root>


How can we rephrase the SQL to pick all the values of the tag b1 in a 'in' list? We can write a PL / SQL for this, but is this possible with a SQL itself directly?
Re: XML 'IN' list [message #333380 is a reply to message #333352] Fri, 11 July 2008 07:51 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
The previous query will return the lists, converting the lists to rows has been asked several times includin yesterday, have a look to SQL forum.

Regards
Michel
Re: XML 'IN' list [message #333381 is a reply to message #333351] Fri, 11 July 2008 07:52 Go to previous message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
srraajesh wrote on Fri, 11 July 2008 13:17
fld1 is a normal DB field. It is not of XML type.

Regards

Query returns strings not xmltype.

Regards
Michel

Previous Topic: SQL using Java
Next Topic: Entity Level Validation between panels using JDeveloper 11g TP3
Goto Forum:
  


Current Time: Sat Dec 03 14:22:40 CST 2016

Total time taken to generate the page: 0.06158 seconds