Mi dispiace,

thought I had answered this long time ago:-D

😎

USE tempdb;

GO

create table #prova

( Valuta varchar(2),

Misura DECIMAL(18,5)

)

insert into #prova values ('EU',1000)

insert into #prova values ('$',2000)

/* Adding a row number */

;WITH DIM_BASE AS

(

SELECT

ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS RID

,PX.Misura

,PX.Valuta

FROM #prova PX

)

/* Splitting each column in two attributes */

,DIM_DET AS

(

SELECT

D.RID

,'Valuta' AS [name]

,D.Valuta AS [value]

FROM DIM_BASE D

UNION ALL

SELECT

D.RID

,'Misura' AS [name]

,CAST(D.Misura AS VARCHAR(20)) AS [value]

FROM DIM_BASE D

)

/* Join the sub-elements on the row number */

SELECT

DB.RID AS '@id'

,(SELECT

DS.name AS 'dim/@name'

,DS.value AS 'dim/@value'

FROM DIM_DET DS

WHERE DS.RID = DB.RID

FOR XML PATH(''), TYPE)

FROM DIM_BASE DB

FOR XML PATH('obs'), TYPE, ROOT('root')

DROP TABLE #prova

Results

<root>

<obs id="1">

<dim name="Valuta" value="EU" />

<dim name="Misura" value="1000.00000" />

</obs>

<obs id="2">

<dim name="Valuta" value="$" />

<dim name="Misura" value="2000.00000" />

</obs>

</root>