OraFAQ Forum: JDeveloper, Java & XML » How to retrieve Number of occurences of a specic node in xml

Home » Developer & Programmer » JDeveloper, Java & XML » How to retrieve Number of occurences of a specic node in xml (Oracle 11g)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

How to retrieve Number of occurences of a specic node in xml [message #601211]

Mon, 18 November 2013 12:42

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

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.

Report message to a moderator

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

gentleman777us
Messages: 122
Registered: April 2005

Senior Member

Iam new to xmltype. Could u please provide the query

Report message to a moderator

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

gentleman777us
Messages: 122
Registered: April 2005

Senior Member

I just need the syntax. I will make it work.

Report message to a moderator

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

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=

Report message to a moderator

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

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.

Report message to a moderator

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

gentleman777us
Messages: 122
Registered: April 2005

Senior Member

once I figure out that query I can use it whereclause to get the # of occurences.

Report message to a moderator

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

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;

Report message to a moderator

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

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

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"?

Report message to a moderator

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

gentleman777us
Messages: 122
Registered: April 2005

Senior Member

yes

Report message to a moderator

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

Barbara Boehmer
Messages: 9104
Registered: November 2002
Location: California, USA

Senior Member

Here are a couple of methods:

Code: [Select all] [Show/ hide]

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.

Report message to a moderator

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

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Mar 13 23:51:37 CDT 2025