invoicespathxml.sql

来自「wrox出版社的另一套经典的VB2005数据库编程学习书籍,收集了书中源码,郑重」· SQL 代码 · 共 62 行

USE Northwind
GO
DECLARE @Top int, @Country nvarchar(15)
SELECT @Top = 10 SELECT @Country = 'USA'
--Invoice groups
SELECT TOP(@Top) Orders.OrderID AS [@OrderID], Orders.CustomerID AS [@CustomerID],
Orders.EmployeeID AS [@EmployeeID], '1' AS [@PaymentID], '1' AS [@CurrencyID],
'1' AS [@FobID], Orders.ShipVia AS [@ShipperID],
Orders.OrderID + 210017 AS InvoiceNumber, Orders.ShippedDate AS InvoiceDate,
--Terms group
'Net 30 Days' AS [Terms/Payment], 'US$' AS [Terms/Currency],
--Shipment group
'Redmond, WA' AS [Shipment/FOB],
Shippers.CompanyName AS [Shipment/Shipper],
CONVERT(decimal(6,2), Orders.Freight) AS [Shipment/PrepaidFreight],
--BillTo group
Customers.CompanyName AS [BillTo/Name], Customers.Address AS [BillTo/Address],
Customers.City AS [BillTo/City], Customers.Region AS [BillTo/Region], Customers.PostalCode AS [BillTo/PostalCode],
Customers.Country AS [BillTo/Country],
--Buyer group
Customers.ContactName AS [BillTo/Buyer/Name], Customers.ContactTitle AS [BillTo/Buyer/Title],
Customers.Phone AS [BillTo/Buyer/Phone], REPLACE(Customers.ContactName, ' ', '_') + '@mail.msn.com' AS [BillTo/Buyer/EMail],
SUBSTRING(Customers.CustomerID, 1, 1) +
SUBSTRING(Customers.CustomerID, 5, 1) + STR(Orders.OrderID + 12345, 5, 0) AS [BillTo/Buyer/PurchaseOrder],
--SalesContact group
Employees.FirstName + ' ' + Employees.LastName AS [SalesContact/Name],
Employees.Title AS [SalesContact/Title], '[phone] X' + Employees.Extension AS [SalesContact/Phone],
LOWER(SUBSTRING(Employees.FirstName, 1, 1) + Employees.LastName) + '@northwind.com' AS [SalesContact/EMail],
--OrderDates group
Orders.OrderDate AS [OrderDates/Ordered],
Orders.RequiredDate AS [OrderDates/Required], Orders.ShippedDate AS [OrderDates/Shipped],
--ShipTo group
Orders.ShipName AS [ShipTo/Name], Orders.ShipAddress AS [ShipTo/Address], Orders.ShipCity AS [ShipTo/City],
Orders.ShipRegion AS [ShipTo/Region], Orders.ShipPostalCode AS [ShipTo/PostalCode], Orders.ShipCountry AS [ShipTo/Country],
--LineItems group inner query
(SELECT [Order Details].OrderID AS [@OrderID], [Order Details].ProductID AS [@ProductID],
ROW_NUMBER() OVER (ORDER BY [Order Details].ProductID) AS [@ItemID], [Order Details].Quantity AS Quantity, Products.ProductID AS SKU,
Products.ProductName AS Product, Products.QuantityPerUnit AS Package,
CONVERT(decimal(6,2), [Order Details].UnitPrice) AS ListPrice,
CONVERT(decimal(3,1), [Order Details].Discount * 100) AS Discount,
CONVERT(decimal(8,2), [Order Details].Quantity * [Order Details].UnitPrice * (1 - [Order Details].Discount)) AS Extended
FROM [Order Details], Products
WHERE [Order Details].OrderID = Orders.OrderID AND Products.ProductID = [Order Details].ProductID
FOR XML PATH('LineItem'), TYPE, ROOT('LineItems')),
--Summary group
(SELECT SUM(Quantity) FROM [Order Details] WHERE [Order Details].OrderID = Orders.OrderID) AS [Summary/NumberOfItems],
(SELECT CONVERT(decimal(8,2), SUM(Quantity * UnitPrice * (1 - Discount)))
FROM [Order Details] WHERE [Order Details].OrderID = Orders.OrderID) AS [Summary/Subtotal],
CONVERT(decimal(6,2), Freight) AS [Summary/Freight],
(SELECT CONVERT(decimal(8,2), SUM(Quantity * UnitPrice * (1 - Discount)) + Freight)
FROM [Order Details] WHERE [Order Details].OrderID = Orders.OrderID) AS [Summary/InvoiceTotal]
--Invoice groups and Invoices wrapper
FROM Customers, Orders, Employees, Shippers
WHERE Customers.CustomerID = Orders.CustomerID AND
Employees.EmployeeID = Orders.EmployeeID AND
Shippers.ShipperID = Orders.ShipVia AND Orders.ShipCountry = @Country AND
Orders.ShippedDate IS NOT NULL
ORDER BY Orders.OrderID DESC
FOR XML PATH('Invoice'), ELEMENTS XSINIL, ROOT('Invoices')
GO