Format XML output using FOR XML
-
If I understand correctly, try this
with cte as (select dense_rank() over(order by ClientID) as id,
count(*) over(partition by ClientID) as DocCount,
ClientId,
row_number() over(partition by ClientID order by id) as DocId,
data
from mytable)
select c.id as "@id",
c.DocCount as "@DocCount",
c.ClientId as "@ClientId",
(select c2.DocId as "@id",
c2.data.query('/Doc/*')
from cte c2
where c2.id=c.id
order by c2.DocId
for xml path('Doc'),type)
from cte c
group by c.id,c.ClientId,c.DocCount
order by c.ClientId
for xml path('Envelope'),root('Envelopes'),type
____________________________________________________
Deja View - The strange feeling that somewhere, sometime you've optimised this query before
How to get the best help on a forum
http://www.sqlservercentral.com/articles/Best+Practices/61537
-
Just amazing ! 😉
Working exactly how I expect
You really deserve to be a MVP !!
I'm still questioning myself about the keywords "OVER" and "PARTITION" its really interesting to do this in DB for me, like a challange (I use to solve all problem in .NET codes)
When it uses "WITH cte ()" is it building something like a context of run or is it really requeting the table(s)/column(s) ?
Thank you again for this support.
-
The clauses that appear in the WITH section are just definitions. The server does not do anything with them until they are invoked in the SELECT section.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
Viewing 3 posts - 16 through 18 (of 18 total)
You must be logged in to reply to this topic. Login to reply