export query to xml

Question

Post reply

export query to xml

sam-433147

Ten Centuries

Points: 1221
More actions
October 21, 2015 at 2:50 am

#151441

I need to export a query to xml
the query i use is:
declare @Person table(Salerid int, Saler varchar(20))
insert into @Person values(1, 'Ben')
declare @Sales table(Salerid int, Prodact varchar(20), Quantity int)
insert into @Sales values(1, 'Laptop', 10)
insert into @Sales values(1, 'Smartphone', 20)
select
Person.Saler,
Prodacts.Prodact,
Prodacts.Quantity,
Total.Units
from @Person Person
inner join @Sales Prodacts on Person.Salerid=Prodacts.Salerid
inner join (select Salerid, sum(Quantity) Units from @Sales group by Salerid) Total on Person.Salerid=Total.Salerid
FOR XML AUTO, ROOT ('Sales'), ELEMENTS XSINIL
the xml i get is:
Sales xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<Person>
<Saler>Ben</Saler>
<Prodacts>
<Prodact>Laptop</Prodact>
<Quantity>10</Quantity>
<Total>
<Units>30</Units>
</Total>
</Prodacts>
<Prodacts>
<Prodact>Smartphone</Prodact>
<Quantity>20</Quantity>
<Total>
<Units>30</Units>
</Total>
</Prodacts>
</Person>
</Sales>
how can i manege the query to get the net result:
Sales xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<Person>
<Saler>Ben</Saler>
<Prodacts>
<Prodact>Laptop</Prodact>
<Quantity>10</Quantity>
</Prodacts>
<Prodacts>
<Prodact>Smartphone</Prodact>
<Quantity>20</Quantity>
</Prodacts>
[highlight=#ffff11] <Total>
<Units>30</Units>
</Total>[/highlight]
</Person>
</Sales>
Forum Etiquette: How to post Reporting Services problems

[/url]

Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
[/url]

How to Post Performance Problems - by Gail Shaw
[/url]

mister.magoo SSC-Forever Points: 47068 More actions · Answer 1

You can use a subquery to grab the Prodacts, like this:

select Person.Saler

, (

select Prodacts.Prodact

, Prodacts.Quantity

from @Sales Prodacts

where Person.Salerid = Prodacts.Salerid

for xml auto

, elements

, type

)

, Total.Units

from @Person Person

inner join (

select Salerid

, sum(Quantity) Units

from @Sales

group by Salerid

) Total

on Person.Salerid = Total.Salerid

for xml AUTO

, ROOT('Sales')

, ELEMENTS XSINIL

MM

select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);