OraFAQ Forum: JDeveloper, Java & XML » count node in xmltype data

Home » Developer & Programmer » JDeveloper, Java & XML » count node in xmltype data

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

count node in xmltype data [message #343275]

Tue, 26 August 2008 17:12

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,

Report message to a moderator

Re: count node in xmltype data [message #343279 is a reply to message #343275]

Tue, 26 August 2008 17:54

Kevin Meade
Messages: 2103
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

Report message to a moderator

Re: count node in xmltype data [message #343284 is a reply to message #343279]

Tue, 26 August 2008 18:21

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;

Report message to a moderator

Re: count node in xmltype data [message #343292 is a reply to message #343275]

Tue, 26 August 2008 18:57

Kevin Meade
Messages: 2103
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

Report message to a moderator

Re: count node in xmltype data [message #343316 is a reply to message #343292]

Tue, 26 August 2008 23:08

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,

Report message to a moderator

Re: count node in xmltype data [message #343330 is a reply to message #343316]

Tue, 26 August 2008 23:55

Kevin Meade
Messages: 2103
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

Report message to a moderator

Re: count node in xmltype data [message #343337 is a reply to message #343330]

Wed, 27 August 2008 00:12

casttree
Messages: 83
Registered: August 2008

Member

Good analysis! Thanks a lot.

Report message to a moderator

Previous Topic:	Cannot Select Territory Independent for XML Publisher template
Next Topic:	Export into xml

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Tue Dec 17 22:46:26 CST 2024