CODEWITHSUNDEEP
sql-serverxmlgroup-byxquery
Kofi Sarfo
Kofi Sarfo

Reputation: 3360

How to group elements by attribute values using XQuery on SQL Server?

Let's say I have a table in SQL Server which contains the results of a query with an inner join.

The following XQuery:

select @code.query
(
'for $s in /root/row
return 
<Foo Language="{data($s/@lang)}" Method="{data($s/@method)}" Returns="{data($s/@returnType)}">
<Bar ReferencedBy="{data($s/@callers)}" Static="{data($s/@static)}" />
</Foo>'
)

And its result:

<Foo Language="C#" Method="getFoos" Returns="FooCollection">
  <Bar ReferencedBy="Baz" Static="true" />
</Foo>
<Foo Language="C#" Method="getFoos" Returns="FooCollection">
  <Bar ReferencedBy="Bar" Static="false" />
</Foo>

What I would like in fact is the following:

<Foo Language="C#" Method="getFoos" Returns="FooCollection">
  <Bar ReferencedBy="Baz" Static="true" />
  <Bar ReferencedBy="Bar" Static="false" />
</Foo>

What's the best way to do this using XQuery in order to avoid resorting to LINQ and a hash table?

Upvotes: 0

Views: 1797

Answers (1)

Oliver Hallam
Oliver Hallam

Reputation: 4262

You need to enumerate over all nodes with each language, method and return value before constructing the results

for $lang in distinct-values(/root/row/@lang)
let $nodes := /root/row[@lang=$lang]
for $method in distinct-values($nodes/@method)
let $nodes := $nodes[@method=$method]
for $return in distinct-values($nodes/@returnType)
let $nodes := $nodes[@returnType=$returnType]
return
  <Foo Language="{$lang}"
       Method="{$method}"
       Returns="{$returnType}">
  {
    for $bar in $nodes
    return 
    <Bar ReferencedBy="{data($node/@callers)}"
         Static="{data($node/@static)}" />
  }
  </Foo>

I do not use SQL Server myself, so I can't guarantee that this will work, but it is a valid XQuery solution.

Upvotes: 1

Related Questions