SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


export in xml


export in xml

Author
Message
antonela
antonela
SSC Veteran
SSC Veteran (271 reputation)SSC Veteran (271 reputation)SSC Veteran (271 reputation)SSC Veteran (271 reputation)SSC Veteran (271 reputation)SSC Veteran (271 reputation)SSC Veteran (271 reputation)SSC Veteran (271 reputation)

Group: General Forum Members
Points: 271 Visits: 176
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
gbritton1
gbritton1
SSCrazy
SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)

Group: General Forum Members
Points: 2516 Visits: 893
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')


antonela
antonela
SSC Veteran
SSC Veteran (271 reputation)SSC Veteran (271 reputation)SSC Veteran (271 reputation)SSC Veteran (271 reputation)SSC Veteran (271 reputation)SSC Veteran (271 reputation)SSC Veteran (271 reputation)SSC Veteran (271 reputation)

Group: General Forum Members
Points: 271 Visits: 176
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>
Eirikur Eiriksson
Eirikur Eiriksson
SSC Guru
SSC Guru (70K reputation)SSC Guru (70K reputation)SSC Guru (70K reputation)SSC Guru (70K reputation)SSC Guru (70K reputation)SSC Guru (70K reputation)SSC Guru (70K reputation)SSC Guru (70K reputation)

Group: General Forum Members
Points: 70198 Visits: 19957
Mi dispiace,
thought I had answered this long time ago:-D
Cool
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>


Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum







































































































































































SQLServerCentral


Search