#眉標=SQL Server 2005
#副標=SQL Server 2005練功房(3)
#大標=SQL Server 2005的XML資料整合
#作者=文/楊志強
==========程式==========
CREATE TABLE MyOrders
(OrderID int identity(1,1) not null, OrderLists XML)
==========程式==========
==========程式==========
DECLARE @OrdersList XML
SET @OrdersList=''
==========程式==========
==========程式==========
CREATE PROCEDURE orderSP
@OrdersList XML AS
INSERT INTO MyOrders VALUES(@OrdersList)
GO
==========程式==========
==========程式==========
CREATE FUNCTION fnOrderList
(@pID int) RETURNS XML AS
BEGIN
RETURN(SELECT OrderLists FROM MyOrders
WHERE OrderID=@pID ) END
GO
==========程式==========
==========程式==========
CREATE TABLE ProductDocs (
ID INT IDENTITY PRIMARY KEY,
ProductDoc XML(ProductSchema) NOT NULL)
GO
==========程式==========
==========程式==========
WHERE Invoices.exist('declare default element namespace
"http://schemas.adventure-works.com/Invoices";
InvoiceList/Invoice') = 1
==========程式==========
==========程式==========
SELECT Invoices.value('declare default element namespace
"http://schemas.adventure-works.com/Invoices";
(InvoiceList/Invoice/@InvoiceNo)[1]', 'int') FirstInvoice
==========程式==========
==========程式==========
SELECT Invoices.query('declare default element namespace
"http://schemas.adventure-works.com/Invoices";
{
for $i in InvoiceList/Invoice/Items/Item[@Product=1]
where $i/@Price < 4
order by $i/@Price descending
return {$i/@Product} {number($i/@Price)}
}
') SalesOfProduct1
==========程式==========
==========程式==========
SELECT nCol.value('../../@InvoiceNo[1]', 'int') InvoiceNo,
nCol.value('@Product[1]', 'int') ProductID,
nCol.value('@Price[1]', 'money') Price,
nCol.value('@Quantity[1]', 'int') Quantity
FROM #Stores CROSS APPLY
Invoices.nodes('declare default element namespace
"http://schemas.adventure-works.com/Invoices";
/InvoiceList/Invoice/Items/Item') AS nTable(nCol)
ORDER BY InvoiceNo
==========程式==========