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...
>

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.
Leilaoops! 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...
>

FOR INSERT Trigger

I've got 2 tables to collect orders. The ORDERS table collects the name,
totals etc. The DETAILS table collects the products. One product per each
row. When a customer places an order, their CType field may get populated
with QW. If CType = 'QW', With a trigger, I need to add a product to the
DETAILS table. The following doesn't work, but should give you an idea what
I need.
IF (SELECT ORDERS.CType FROM AffOrders) = 'QW'
INSERT INTO DETAILS
(OrderID, OrderNo, Description, Qty, PriceEach)
VALUES (ORDERS.OrderID,'SF','ServiceFee',1,4)
How do I get this done? Obviously, I need to query the order that was just
placed. I also need to place the corresponding OrderID into DETAILS.
thanks!Try this:
CREATE TRIGGER Orders_TrigAddQWDetails
ON dbo.Orders
FOR INSERT
As
Insert DETAILS (OrderID, OrderNo,
Description, Qty, PriceEach)
Select OrderID,'SF','ServiceFee',1,4
From Inserted Where CType = 'QW'
"shank" wrote:

> I've got 2 tables to collect orders. The ORDERS table collects the name,
> totals etc. The DETAILS table collects the products. One product per each
> row. When a customer places an order, their CType field may get populated
> with QW. If CType = 'QW', With a trigger, I need to add a product to the
> DETAILS table. The following doesn't work, but should give you an idea wha
t
> I need.
>
> IF (SELECT ORDERS.CType FROM AffOrders) = 'QW'
> INSERT INTO DETAILS
> (OrderID, OrderNo, Description, Qty, PriceEach)
> VALUES (ORDERS.OrderID,'SF','ServiceFee',1,4)
> How do I get this done? Obviously, I need to query the order that was just
> placed. I also need to place the corresponding OrderID into DETAILS.
> thanks!
>
>