<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META http-equiv=Content-Type content="text/html; charset=us-ascii">
<STYLE>.hmmessage P {
        PADDING-RIGHT: 0px; PADDING-LEFT: 0px; PADDING-BOTTOM: 0px; MARGIN: 0px; PADDING-TOP: 0px
}
BODY.hmmessage {
        FONT-SIZE: 10pt; FONT-FAMILY: Tahoma
}
</STYLE>
<META content="MSHTML 6.00.6000.16643" name=GENERATOR></HEAD>
<BODY class=hmmessage>
<DIV dir=ltr align=left><SPAN class=397054707-06062008><FONT face=Arial
color=#0000ff>Your XML isn't well-formed - displaySubject uses "Day" as an
attribute with no attribute name. Assuming you meant <display
subject="Day">, the answer would be</FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=397054707-06062008><FONT face=Arial
color=#0000ff></FONT></SPAN> </DIV>
<DIV dir=ltr align=left><SPAN class=397054707-06062008><FONT face=Arial
color=#0000ff>count((<A>//display/@subject</A> | //alt)[. =
$word]</FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=397054707-06062008><FONT face=Arial
color=#0000ff></FONT></SPAN> </DIV>
<DIV dir=ltr align=left><SPAN class=397054707-06062008><FONT face=Arial
color=#0000ff>where the variable $word is initialized to the word you are
looking for.</FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=397054707-06062008><FONT face=Arial
color=#0000ff></FONT></SPAN> </DIV>
<DIV dir=ltr align=left><SPAN class=397054707-06062008><FONT face=Arial
color=#0000ff>That looks for $word as the whole of the element or attribute
value. If you're interested in matching substrings of the value, it would
be</FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=397054707-06062008><FONT face=Arial
color=#0000ff></FONT></SPAN> </DIV>
<DIV dir=ltr align=left><SPAN class=397054707-06062008><FONT face=Arial
color=#0000ff></FONT></SPAN>
<DIV dir=ltr align=left><SPAN class=397054707-06062008><FONT face=Arial
color=#0000ff>count((<A>//display/@subject</A> | //alt)[contains(.,
$word)]</FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=397054707-06062008><FONT face=Arial
color=#0000ff></FONT></SPAN> </DIV>
<DIV dir=ltr align=left><SPAN class=397054707-06062008>I want to find all the
strings that occur more than 50 times in the document.<BR></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=397054707-06062008><FONT face=Arial
color=#0000ff>Again this depends rather whether you are looking for strings that
make up the whole of an element or attribute value, or for substrings, If the
latter, you need to define how they are delimited (e.g. on word boundaries). The
naive solution to this is something like this:</FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=397054707-06062008><FONT face=Arial
color=#0000ff></FONT></SPAN> </DIV>
<DIV dir=ltr align=left><SPAN class=397054707-06062008><FONT face=Arial
color=#0000ff>let $allWords := for $i in (//*, //@*) return tokenize(.,
'\W+')</FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=397054707-06062008><FONT face=Arial
color=#0000ff>let $distinctWords :=
distinct-values($allWords)</FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=397054707-06062008><FONT face=Arial
color=#0000ff>where count($allWords[. = $distinctWords] gt
50)</FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=397054707-06062008><FONT face=Arial
color=#0000ff>return $distinctWords</FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=397054707-06062008><FONT face=Arial
color=#0000ff></FONT></SPAN> </DIV>
<DIV dir=ltr align=left><SPAN class=397054707-06062008><FONT face=Arial
color=#0000ff>But this could be horrendously inefficient unless your XQuery
engine has a rather clever optimizer. There's an XSLT 2.0 solution on page 19 of
my XSLT 2.0 Programmers Reference (4th edition) that makes use of built-in
grouping facilities in XSLT, and is likely to run much
faster.</FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=397054707-06062008><FONT face=Arial
color=#0000ff></FONT></SPAN> </DIV>
<DIV dir=ltr align=left><SPAN class=397054707-06062008><FONT face=Arial
color=#0000ff>Michael Kay</FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=397054707-06062008><FONT face=Arial
color=#0000ff><A
href="http://www.saxonica.com/">http://www.saxonica.com/</A></FONT></DIV></SPAN></DIV><BR>
<BLOCKQUOTE
style="PADDING-LEFT: 5px; MARGIN-LEFT: 5px; BORDER-LEFT: #0000ff 2px solid; MARGIN-RIGHT: 0px">
<DIV class=OutlookMessageHeader lang=en-us dir=ltr align=left>
<HR tabIndex=-1>
<FONT face=Tahoma><B>From:</B> talk-bounces@x-query.com
[mailto:talk-bounces@x-query.com] <B>On Behalf Of </B>Mudita
Nain<BR><B>Sent:</B> 04 June 2008 17:21<BR><B>To:</B>
talk@x-query.com<BR><B>Subject:</B> [xquery-talk] Find count of a string in an
xml file<BR></FONT><BR></DIV>
<DIV></DIV>
<META content="Microsoft SafeHTML" name=Generator>
<STYLE>.ExternalClass .EC_hmmessage P {
        PADDING-RIGHT: 0px; PADDING-LEFT: 0px; PADDING-BOTTOM: 0px; PADDING-TOP: 0px
}
.ExternalClass BODY.EC_hmmessage {
        FONT-SIZE: 10pt; FONT-FAMILY: Tahoma
}
</STYLE>
<STYLE>.ExternalClass .EC_hmmessage P {
        PADDING-RIGHT: 0px; PADDING-LEFT: 0px; PADDING-BOTTOM: 0px; PADDING-TOP: 0px
}
.ExternalClass BODY.EC_hmmessage {
        FONT-SIZE: 10pt; FONT-FAMILY: Tahoma
}
</STYLE>
Hi all,<BR> <BR>I am using SQL Server 2005. I have a table with xml
column in which I have loaded an XML file. I want to write an xquery which
finds the number of occurrences of a string in the document in some defined
tags.<BR> <BR>The structure of the document is as
follows:<BR> <BR><subject><BR><displaySubject =
"Day"><BR><alt> </alt><BR><alt>
</alt><BR><alt> </alt><BR><alt>
</alt><BR></displaySubject><BR></subject><BR><subject><BR><displaySubject><BR><alt>
</alt><BR><alt> </alt><BR><alt>
</alt><BR><alt>
</alt><BR></displaySubject><BR></subject><BR> <BR> <BR>So,
I want to find how many time the string "Day" occurs whether in displaySubject
or alt anywhere in the document.<BR>Also, the "Day" string is not known. I
want to find all the strings that occur more than 50 times in the
document.<BR> <BR>I hope I am clear.<BR>I would appreciate any help from
you.<BR> <BR>Thanks<BR>Mudita<BR><BR> <BR> <BR><BR>
<HR>
Instantly invite friends from Facebook and other social networks to join you
on Windows Live™ Messenger. <A
href="https://www.invite2messenger.net/im/?source=TXT_EML_WLH_InviteFriends"
target=_new>Invite friends now!</A> </BLOCKQUOTE></BODY></HTML>