#眉標=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 ==========程式==========