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:?
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
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.
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');