June 5, 2014 at 8:58 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)
insert into ##prova values ('$',2000)
The final result must be something like this:
<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>
Thank you
June 5, 2014 at 7:26 pm
This should get you started, notice I changed the data type of the monetary value, float is not the appropriate type!
😎
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>
June 6, 2014 at 1:59 am
thank you very much.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply