Grouping by first letter in XQuery

hello,

i use this fragment for grouping:

declare @.data as xml;

set @.data='<r>

<item letter="A" title="Alabama"/>

<item letter="A" title="Arizona"/>

<item letter="C" title="California"/>

<item letter="C" title="Colorado"/>

</r>';

select @.data.query('

<root>

{for $c in distinct-values(/*/*/@.letter)

return

<letter is="{$c}">

{

for $x in /*/*

where $x/@.letter = $c

return $x

}

</letter>

}</root>

');

it produce this xml

<root>

<letter is="A">

<item letter="A" title="Alabama" />

<item letter="A" title="Arizona" />

</letter>

<letter is="C">

<item letter="C" title="California" />

<item letter="C" title="Colorado" />

</letter>

</root>

but when i try use substring for avoid using attribute letter in source xml (it not so hard to produce it), but it appeared in result xml too and it not so easy to remove it.

when i replace

for $c in distinct-values(/*/*/@.letter)

with

for $c in distinct-values(substring(/*/*/@.letter,1,1))

then i get this error

XQuery [query()]: 'substring()' requires a singleton (or empty sequence), found operand of type 'xdt:untypedAtomic *'

p.s. Another question: why Microsoft not use FCKEditor as xhtml online editor?

You need to use the data function to pull out each individual value. However you will need to do a two step process for example:

DECLARE @.y xml;
set @.y = @.data.query('
<r>{
for $item in r/item
return
element item {
attribute title {$item/@.title},
attribute l {substring($item/@.title, 1, 1)}
}}</r>
')

SELECT @.y.query('
<r>{
for $l in distinct-values(data(r/item/@.l))
return
<letter l="{$l}">{
for $i in (/r/item)
where $i/@.l = $l
return $i
}</letter>
}
</r>')

SQL Server 2005 only supports a subset of XQuery. In a full implementation of XQuery this would work:

<r>{
for $letter in distinct-values(/r/item/substring(@.title, 1 ,1 ))
return
<letter l="{$letter}">
{
for $item in /r/item[substring(@.title,1,1) = $letter]
return $item
}
</letter>
}</r>

Dan

|||

The problem that you have is that you pass an expression to substring that can be a list of values. and the substring function only operates on a single value. So you need to rewrite it by adding an additional for return as below.

That's why Dan's solution also would fail.

and SQL Server 2005 can solve the problem with a single expression:

declare @.data as xml;

set @.data='<r>
<item letter="A" title="Alabama"/>
<item letter="A" title="Arizona"/>
<item letter="C" title="California"/>
<item letter="C" title="Colorado"/>
</r>';

select @.data.query('
<root>{
for $c in distinct-values(
for $i in /r/item/@.letter return substring($i,1,1))
return
<letter is="{$c}">{
for $x in /r/item
where $x/@.letter = $c
return $x
}</letter>
}</root>');

Note that Dan is right in replacing the wildcards with the correct path names. Wildcards should only be used if you really do not know what the path name is...

Best regards
Michael

sql