CODEWITHSUNDEEP
sql-serverxmlt-sqlattributeselement
user1803870
user1803870

Reputation: 1

SQL Server for XML mix Element and dynamic Attribute

Not able to to get the desired XML output, when I execute the code below I dont want the tag amount, I need the amount as a value from vCod tag.

Can I get that ouput with FOR XML? what am I doing wrong ?

This code sql code :

declare @t table (vKey int,  vCod varchar(4) , amount decimal(16,2) )

insert @t values ( 1 , 'v110' , 12.34 )
insert @t values ( 1 , 'v115' , 18.40 )
insert @t values ( 1 , 'v120' , 2.55 )
insert @t values ( 2 , 'v110' , 22.33 )
insert @t values ( 2 , 'v120' , 8.05 )
insert @t values ( 3 , 'v120' , 13.23 )
insert @t values ( 3 , 'v125' , 28.05 )
insert @t values ( 3 , 'v130' , 44.00 )

select vkey as '@key', vCod as '@vCod' , amount as '@amount' from @t for xml 
path('Root')`

Produces:

<Root key="1" vCod="v110" amount="12.34" />
<Root key="1" vCod="v115" amount="18.40" />
<Root key="1" vCod="v120" amount="2.55" />
<Root key="2" vCod="v110" amount="22.33" />
<Root key="2" vCod="v120" amount="8.05" />
<Root key="3" vCod="v120" amount="13.23" />
<Root key="3" vCod="v125" amount="28.05" />
<Root key="3" vCod="v130" amount="44.00" />

I need this:

<Root>
    <Group key="1">
        <vCod v110="12.24"
              v115="18.40"
              v120="2.55"/>
    </Group>
    <Group key="2">
        <vCod v110="22.33"
              v120="8.05"/>
    </Group>
    <Group key="3">
        <vCod v120="13.23"
              v125="28.05"
              v130="44.00"/>
    </Group>
</Root>

Upvotes: 0

Views: 71

Answers (1)

AlwaysLearning
AlwaysLearning

Reputation: 8829

You'll need to PIVOT the amount values around vCod so that you can use those vCod values as attribute names, e.g.:

declare @t table (vKey int,  vCod varchar(4) , amount decimal(16,2) )

insert @t values ( 1 , 'v110' , 12.34 )
insert @t values ( 1 , 'v115' , 18.40 )
insert @t values ( 1 , 'v120' , 2.55 )
insert @t values ( 2 , 'v110' , 22.33 )
insert @t values ( 2 , 'v120' , 8.05 )
insert @t values ( 3 , 'v120' , 13.23 )
insert @t values ( 3 , 'v125' , 28.05 )
insert @t values ( 3 , 'v130' , 44.00 )

select
  vkey as [@key],
  v110 as [vCod/@v110],
  v115 as [vCod/@v115],
  v120 as [vCod/@v120],
  v125 as [vCod/@v125],
  v130 as [vCod/@v130]
from (
  select vKey, vCod, amount from @t
) Src
pivot (max(amount) for vCod in ([v110], [v115], [v120], [v125], [v130])) p
for xml path('Group'), root('Root');

Which produces:

<Root>
    <Group key="1">
        <vCod v110="12.34" v115="18.40" v120="2.55"/>
    </Group>
    <Group key="2">
        <vCod v110="22.33" v120="8.05"/>
    </Group>
    <Group key="3">
        <vCod v120="13.23" v125="28.05" v130="44.00"/>
    </Group>
</Root>

Upvotes: 1

Related Questions