Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

export in xml Expand / Collapse
Author
Message
Posted Wednesday, June 4, 2014 8:05 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, October 23, 2014 4:44 AM
Points: 52, Visits: 91
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
Post #1577345
Posted Wednesday, June 4, 2014 8:17 AM This worked for the OP Answer marked as solution
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Sunday, October 12, 2014 4:00 PM
Points: 341, Visits: 751
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')

Post #1577355
Posted Wednesday, June 4, 2014 2:56 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, October 23, 2014 4:44 AM
Points: 52, Visits: 91
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>
Post #1577566
Posted Saturday, June 21, 2014 11:17 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 11:46 AM
Points: 2,419, Visits: 6,708
Mi dispiace,
thought I had answered this long time ago

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>

Post #1584687
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse