June 4, 2014 at 8:05 am
Hello everybody,
I need to export the records of a table in xml format.
create table ##prova
( Valuta varchar(2),
Misura float
)
insert into ##prova values ('EU',1000)
I used this:
select * from ##prova for xml path('obs'),root('root')
My result is:
<root>
<obs>
<Valuta>EU</Valuta>
<Misura>1.000000000000000e+003</Misura>
</obs>
</root>
But I need to have this format:
<root>
<obs id=”0”>
<dim name=”Valuta” value=”EU” />
<dim name=”Misura” value=”1000” />
</obs>
</root>
Anybody know how I could obtain this result?
First of all I need to put a fix value id="0" after obs and then I have to obtain another format <dim name=”” value=”” /> for every field.
Thank you
June 4, 2014 at 8:17 am
try this:
select 0 as [@id]
, (select 'Valuta' as [@name]
, Valuta as [@value]
from #prova
for xml path('dim'), type)
, (select 'Misura' as [@name]
, Misura as [@value]
from #prova
for xml path('dim'), type)
for xml path('obs'), root('root')
June 4, 2014 at 2:56 pm
Thank you very much, I didn't have any idea how to do it.....
But if I insert another record in the table
insert into ##prova values ('$',2000)
Is it a way to have this result?
<root>
<obs id=”0”>
<dim name=”Valuta” value=”EU” />
<dim name=”Misura” value=”1000” />
</obs>
<obs id=”0”>
<dim name=”Valuta” value=”$” />
<dim name=”Misura” value=”2000” />
</obs>
</root>
with the your code I have:
<root>
<obs id="0">
<dim name="Valuta" value="EU" />
<dim name="Valuta" value="$" />
<dim name="Misura" value="1.000000000000000e+003" />
<dim name="Misura" value="2.000000000000000e+003" />
</obs>
</root>
June 21, 2014 at 11:17 am
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>
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply