Export sql table to xml

1

helo,

declare @xml xml
set @xml=(SELECT Saler.Saler as SalerName, Sale.Product, Sale.Payment, Salary.Toal
FROM aSales AS Saler
INNER JOIN aSales AS Sale ON Saler.id = Sale.id
INNER JOIN (select Saler, sum(Payment) AS Toal from aSales group by Saler) Salary ON Saler.Saler = Salary.Saler
order by Saler.Saler
FOR XML AUTO, ROOT ('Salers'), ELEMENTS XSINIL)

when i use the above code
i get the Salary element inside each Sale element

Jhon Phone 100 400 Computer 300 400

how can i put the Salary under the Saler elemet and after the last Sale element, like:?

Jhon Phone 100 Computer 300 400

thanks ahead

2

That is the expected behavior when using XML AUTO. Use XML PATH instead. XML PATH is more flexible as well.

3

thanks for the replay
i'm new for xml

i try to get result like:


Jhon

Computer
300


Phone
100


400



one Saler element for each Saler
one Salary element for each Saler after the last Products element

with the next code:
CREATE TABLE aSales(Id int IDENTITY(1,1), Saler varchar(20),Product varchar(10) ,Payment int)
GO
insert into aSales(Saler, Product, Payment)
select 'Jhon', 'Phone', 100
insert into aSales(Saler, Product, Payment)
select 'Jhon', 'Computer', 300
GO
declare @xml xml
set @xml=(SELECT
Saler.Saler as "Saler/SalerName",
Sale.Product AS "SalerName/Product",
Sale.Payment AS "SalerName/Payment",
Salary.Toal AS "Salary/Toal"
FROM aSales AS Saler
INNER JOIN aSales AS Sale ON Saler.id = Sale.id
INNER JOIN (select Saler, sum(Payment) AS Toal from aSales group by Saler) Salary ON Saler.Saler = Salary.Saler
order by Saler.Saler
FOR XML PATH ('Salers'), ROOT ('Salers'), ELEMENTS XSINIL)

select @xml

but i can't reach the above result
i will be grateful for a piece of ש right code

thanks ahead

4

What you posted is not XML. Looking it it, it is hard to discern the XML you are trying to get. XML has elements that are specified based on some rules. See here. For example, this may be the XML you are looking for.

<Salers>
  <Salers>
    <Saler>Jhon</Saler>
    <Details>
      <Product>Phone</Product>
      <Payment>100</Payment>
      <Product>Computer</Product>
      <Payment>300</Payment>
    </Details>
    <Payment>400</Payment>
  </Salers>
</Salers>

If it is, what you could achieve that using something like shown below

SELECT
	a.Saler,
	(
		SELECT
			Product,
			Payment
		FROM
			dbo.aSales b
		WHERE
			b.Saler = a.Saler
		FOR XML PATH(''), TYPE
	) AS 'Details',
	SUM(a.Payment) AS Payment
FROM
	dbo.aSales a
GROUP BY
	a.Saler
FOR XML PATH('Salers'),ROOT('Salers');