Home » Developer & Programmer » JDeveloper, Java & XML » How to retrieve Number of occurences of a specic node in xml (Oracle 11g)
How to retrieve Number of occurences of a specic node in xml [message #601211] Mon, 18 November 2013 12:42 Go to next message
gentleman777us
Messages: 122
Registered: April 2005
Senior Member
Hi,

I have my xml stored in a clob column. I need a query to get the number of occurences of a specific node

EX: How do I get number of occurences of node <SHIPMENT> FROM the table T_SHIPPERS.

Note: the xml document is saved in CLOB column.

your prompt reply is greatly appreciated.

Thanks


[Updated on: Mon, 18 November 2013 12:43]

Report message to a moderator

Re: How to retrieve Number of occurences of a specic node in xml [message #601212 is a reply to message #601211] Mon, 18 November 2013 12:46 Go to previous messageGo to next message
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
Senior Member
Did you use xmltype to retrieve?

Please post your query, rather, post the complete sqlplus session.
Re: How to retrieve Number of occurences of a specic node in xml [message #601213 is a reply to message #601212] Mon, 18 November 2013 12:52 Go to previous messageGo to next message
gentleman777us
Messages: 122
Registered: April 2005
Senior Member
Iam new to xmltype. Could u please provide the query
Re: How to retrieve Number of occurences of a specic node in xml [message #601215 is a reply to message #601213] Mon, 18 November 2013 13:12 Go to previous messageGo to next message
gentleman777us
Messages: 122
Registered: April 2005
Senior Member
I just need the syntax. I will make it work.
Re: How to retrieve Number of occurences of a specic node in xml [message #601217 is a reply to message #601215] Mon, 18 November 2013 13:16 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
unable or unwilling to use SEARCH or GOOGLE yourself?

http://www.oracle.com/pls/db121/search?remark=quick_search&word=xmltype&partno=
Re: How to retrieve Number of occurences of a specic node in xml [message #601220 is a reply to message #601217] Mon, 18 November 2013 13:41 Go to previous messageGo to next message
gentleman777us
Messages: 122
Registered: April 2005
Senior Member
when I execute the following query I get inconsistent datatypes error (ora-00932)

select extractvalue((render_xml),'/shipment') from t_shippers
where shipper_id = 76545;

where render_xml is the clob column where the xml is stored.

Re: How to retrieve Number of occurences of a specic node in xml [message #601222 is a reply to message #601220] Mon, 18 November 2013 13:42 Go to previous messageGo to next message
gentleman777us
Messages: 122
Registered: April 2005
Senior Member
once I figure out that query I can use it whereclause to get the # of occurences.
Re: How to retrieve Number of occurences of a specic node in xml [message #601226 is a reply to message #601222] Mon, 18 November 2013 14:04 Go to previous messageGo to next message
gentleman777us
Messages: 122
Registered: April 2005
Senior Member
Here is the query

select count(*) from shippers
where shipper_id = 76545
and extractvalue(xmltype.(render_xml),'/shipment').getclobval() is not null;
Re: How to retrieve Number of occurences of a specic node in xml [message #601236 is a reply to message #601226] Mon, 18 November 2013 15:47 Go to previous messageGo to next message
gentleman777us
Messages: 122
Registered: April 2005
Senior Member
Iam able to make the query work


select count(*)
from shippers
where shipper_id = 76545
and existsnode(xmltype,createxml(render_xml),'//Placemark','xmlns="http://www.opengis.net/kml/2.2"') = 1;

This is returning me value 1
but the "Placemark" node exists 2 times.

How do I modify the query so that count returns 2(# of occurences) instead of 1?

Thanks

[Updated on: Mon, 18 November 2013 15:47]

Report message to a moderator

Re: How to retrieve Number of occurences of a specic node in xml [message #601324 is a reply to message #601236] Tue, 19 November 2013 12:38 Go to previous messageGo to next message
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
Senior Member
Do you just want to get the number of occurrences for the pattern "Placemark"?
Re: How to retrieve Number of occurences of a specic node in xml [message #601330 is a reply to message #601324] Tue, 19 November 2013 13:29 Go to previous messageGo to next message
gentleman777us
Messages: 122
Registered: April 2005
Senior Member
yes
Re: How to retrieve Number of occurences of a specic node in xml [message #606979 is a reply to message #601330] Fri, 31 January 2014 10:18 Go to previous message
Barbara Boehmer
Messages: 9077
Registered: November 2002
Location: California, USA
Senior Member
Here are a couple of methods:

SCOTT@orcl12c> CREATE TABLE t_shippers
  2    (shipper_id  NUMBER,
  3  	render_xml  CLOB)
  4  /

Table created.

SCOTT@orcl12c> INSERT INTO t_shippers VALUES (76545,
  2  '<SHIPMENT>
  3  	<PLACEMARK>first row of data</PLACEMARK>
  4  	<PLACEMARK>second row of data</PLACEMARK>
  5   </SHIPMENT>'
  6  )
  7  /

1 row created.

SCOTT@orcl12c> SELECT COUNT (*)
  2  FROM   t_shippers,
  3  	    XMLTABLE ('//PLACEMARK' PASSING XMLTYPE (t_shippers.render_xml))
  4  WHERE  shipper_id = 76545
  5  /

  COUNT(*)
----------
         2

1 row selected.

SCOTT@orcl12c> SELECT XMLQUERY
  2  	      ('count (//PLACEMARK)'
  3  	       PASSING XMLTYPE (t_shippers.render_xml)
  4  	       RETURNING CONTENT)
  5  FROM   t_shippers
  6  WHERE  shipper_id = 76545
  7  /

XMLQUERY('COUNT(//PLACEMARK)'PASSINGXMLTYPE(T_SHIPPERS.RENDER_XML)RETURNINGCONTE
--------------------------------------------------------------------------------
2

1 row selected.

Previous Topic: How to start Learning
Next Topic: Java with Forms6i
Goto Forum:
  


Current Time: Fri Mar 29 03:09:18 CDT 2024