FOR XML PATH

Hi,
I have the following query:
SELECT
C.CompanyName,
O.OrderDate,
E.LastName AS 'Registered By'
FROM Customers C
JOIN Orders O
ON C.CustomerID=O.CustomerID
JOIN Employees E
ON E.EmployeeID=O.EmployeeID
ORDER BY C.CompanyName
I need all information of orders for particular company, appear under the
element of that company but the query that I have written returns the name
of company again and again for each order:
SELECT
C.CompanyName AS "@.Company",
O.OrderDate AS "Order/OrderDate",
E.LastName AS "Order/RegisteredBy"
FROM Customers C
JOIN Orders O
ON C.CustomerID=O.CustomerID
JOIN Employees E
ON E.EmployeeID=O.EmployeeID
ORDER BY C.CompanyName
FOR XML PATH
Any help would be greatly appreciated.
Leila
oops! seems that the problem solved:
SELECT
C.CompanyName AS "@.Company",
(SELECT
O.OrderDate AS 'Order/OrderDate',
E.LastName AS 'Order/RegisteredBy'
FROM Orders O
JOIN Employees E
ON E.EmployeeID=O.EmployeeID
WHERE O.CustomerID=C.CUstomerID
FOR XML PATH(''),type)
FROM Customers C
ORDER BY C.CompanyName
FOR XML PATH('Customers'),ROOT('ROOT')
Thanks to BOL documentation team :-)
"Leila" <Leilas@.hotpop.com> wrote in message
news:%23AjU8w2IGHA.1676@.TK2MSFTNGP09.phx.gbl...
> Hi,
> I have the following query:
> SELECT
> C.CompanyName,
> O.OrderDate,
> E.LastName AS 'Registered By'
> FROM Customers C
> JOIN Orders O
> ON C.CustomerID=O.CustomerID
> JOIN Employees E
> ON E.EmployeeID=O.EmployeeID
> ORDER BY C.CompanyName
>
> I need all information of orders for particular company, appear under the
> element of that company but the query that I have written returns the name
> of company again and again for each order:
> SELECT
> C.CompanyName AS "@.Company",
> O.OrderDate AS "Order/OrderDate",
> E.LastName AS "Order/RegisteredBy"
> FROM Customers C
> JOIN Orders O
> ON C.CustomerID=O.CustomerID
> JOIN Employees E
> ON E.EmployeeID=O.EmployeeID
> ORDER BY C.CompanyName
> FOR XML PATH
> Any help would be greatly appreciated.
> Leila
>
>
|||Good documentation can help ;)
You can make your query slightly easier to maintain by writing the subquery
as
(SELECT
O.OrderDate AS 'OrderDate',
E.LastName AS 'RegisteredBy'
FROM Orders O
JOIN Employees E
ON E.EmployeeID=O.EmployeeID
WHERE O.CustomerID=C.CUstomerID
FOR XML PATH('Order'),type)
Best regards
Michael
"Leila" <Leilas@.hotpop.com> wrote in message
news:%23LnD5B3IGHA.3696@.TK2MSFTNGP15.phx.gbl...
> oops! seems that the problem solved:
> SELECT
> C.CompanyName AS "@.Company",
> (SELECT
> O.OrderDate AS 'Order/OrderDate',
> E.LastName AS 'Order/RegisteredBy'
> FROM Orders O
> JOIN Employees E
> ON E.EmployeeID=O.EmployeeID
> WHERE O.CustomerID=C.CUstomerID
> FOR XML PATH(''),type)
> FROM Customers C
> ORDER BY C.CompanyName
> FOR XML PATH('Customers'),ROOT('ROOT')
> Thanks to BOL documentation team :-)
>
> "Leila" <Leilas@.hotpop.com> wrote in message
> news:%23AjU8w2IGHA.1676@.TK2MSFTNGP09.phx.gbl...
>