Home » Developer & Programmer » JDeveloper, Java & XML » count node in xmltype data
count node in xmltype data [message #343275] Tue, 26 August 2008 17:12 Go to next message
casttree
Messages: 83
Registered: August 2008
Member
I need to count the number of node in a varchar2 variable.

The xml data like below, I hope to count how many node test_id in the xml file. In this xml data, we have 2 test_id (as 5 and 51). so I need to get 2 as result in a PL/SQL package.


I looked extract and extractvalue

<report db_version="11.2.0.0.1">

<report_id><![CDATA[/orarep/sqltune/auto_summary?section=task_stats]]></report_id>

<test_id>5</test_id>
<test_id>51</tets_id>
</report>


The blow query returns 551 as result, Do you have any suggestions to sepearte the data and get the count for the node number?


select nvl(XMLType(:rept).extract('//test_id/').getstringval(), '(NULL)') into :te from dual;



Thanks,
Re: count node in xmltype data [message #343279 is a reply to message #343275] Tue, 26 August 2008 17:54 Go to previous messageGo to next message
Kevin Meade
Messages: 1955
Registered: December 1999
Location: Connecticut USA
Senior Member
Unfortunately this may not work. Consider this data:

<rowset>
   <row>
      <rowkey>1</rowkey>
      <testid>5</testid>
   </row>
   <row>
      <rowkey>2</rowkey>
      <testid>51</testid>
   </row>
   <row>
      <rowkey>3</rowkey>
      <testid></testid>
   </row>
</rowset>


as compared to this data

<rowset>
   <row>
      <rowkey>1</rowkey>
      <testid>5</testid>
   </row>
   <row>
      <rowkey>2</rowkey>
      <testid>51</testid>
   </row>
   <row>
      <rowkey>3</rowkey>
   </row>
</rowset>


To my understanding, both these data streams have the same data; more specifically, each contains a row with a null testid.

What do you expect as a count from each of these. If all you want to know is if the tag exists then the counts are 3 and 2 respectively. But if you are looking to see how many rows have a non-null testid then the counts are 2 and 2. Be clear about what "COUNT" really means to you.

What do you think?

Can anyone confirm my understanding of basic XML data semantics?

Kevin
Re: count node in xmltype data [message #343284 is a reply to message #343279] Tue, 26 August 2008 18:21 Go to previous messageGo to next message
casttree
Messages: 83
Registered: August 2008
Member
Yes, I need to know how many tag for test_id , so the above case could be 3 and 2 respectively.

I tried the following query, looks it is working, but I am not 100% sure if there is bug or better query to do it?

(Note: rept is the variable with xml data)
Quote:
select count(*) into :te from table(xmlsequence(XMLType(:rept).extract('//test_id'))) t;

Re: count node in xmltype data [message #343292 is a reply to message #343275] Tue, 26 August 2008 18:57 Go to previous messageGo to next message
Kevin Meade
Messages: 1955
Registered: December 1999
Location: Connecticut USA
Senior Member
Unfortunately I am seeing several opportunities for failure in this question. However, I can point you here:

Quote:
Kevin Meade's blog

Easy XML - a Programming Oriented Approach

Easy XML - Let the Database do the Work


Additionally I would ask you, how long is the data you are going to be working with? Maybe your solution can be someting as simple as this:

(length(<xml>)-length(replace(<xml>,'<testid>')))/length('<testid>')


In the above solution we do not even treat the data as xml, just a bunch of characters. If we assume that <testid> is a unique text string in the data (which is to say that it cannot be found as a substring of a larger string), then the above calculation will tell you how many there are of <testid> in <xml>. Of course this simple test substitution method only works if the data fits the solution so think about how it might fail and if this works for you.

Good luck, Kevin
Re: count node in xmltype data [message #343316 is a reply to message #343292] Tue, 26 August 2008 23:08 Go to previous messageGo to next message
casttree
Messages: 83
Registered: August 2008
Member
Yeah, I think it is a good method for the issue. My data is not much long. maybe 1K at most. And all test_id should be the tag name, but not data.


I try to compare the time spend for two ways by set timing on, the result is random and can't distiguish it obviously.

It looks both way are working,

Quote:
length(:rept>)-length(replace(:rept,'<test_id>')))/length('<test_id>')


Quote:
select count(*) into :te from table(xmlsequence(XMLType(:rept).extract('//test_id'))) t


Looks the way of length is more simple, is that only reason we should use the length way? (I feel it is not strong enough )

Is there other reasons that one method is better than another one?

Thanks,
Re: count node in xmltype data [message #343330 is a reply to message #343316] Tue, 26 August 2008 23:55 Go to previous messageGo to next message
Kevin Meade
Messages: 1955
Registered: December 1999
Location: Connecticut USA
Senior Member
I look at it this way.

In one of these two solutions, we are treating data with respect. We recognize it has a format and expect it to adhear to that format.

In the other solution we show little respect for the data as actual data.

You tell me; based on this simple observation which solution is "stronger".

All this said, there is a significant difference in the basic complexity between the two solutions. This too should be considered.

It is your call. The two biggest issues I see with the calculation method are:

1) you might get a string that exceeeds the limitations of (length,replace)

2) you might get an XML data stream that contains as its data other XML datastreams. In this situations, your embedded data might also contain the <test_id> tag. Should these be counted too?

Good luck, Kevin
Re: count node in xmltype data [message #343337 is a reply to message #343330] Wed, 27 August 2008 00:12 Go to previous message
casttree
Messages: 83
Registered: August 2008
Member
Good analysis! Thanks a lot.
Previous Topic: Cannot Select Territory Independent for XML Publisher template
Next Topic: Export into xml
Goto Forum:
  


Current Time: Sun Nov 23 08:53:18 CST 2014

Total time taken to generate the page: 0.11183 seconds