--测试数据准备
Create table Product (ProductID varchar(18),cName varchar(50))
insert into Product Select '80100212800','A'
insert into Product Select '80100250006','B'
insert into Product Select '80101038000','C'
-- 测试执行
Exec ProductGet
--- 第一层代码
Alter PROCEDURE ProductGet
AS
Begin
Declare @sql as nvarchar(max)
Declare @cXML as XML
Declare @cXML1 as XML
Declare @cXML2 as XML
Declare @cXML3 as XML
Exec ProductOne '80100212800',@cXML1 Out
Exec ProductOne '80100250006',@cXML2 Out
Exec ProductOne '80101038000',@cXML3 Out
--Set @cXML1 = @cXML1 + @cXML2 + @cXML3
Declare @cXML_ as XML
Set @cXML_ = (Select DISTINCT * From (
SELECT
x.item.value('@OrderID[1]', 'VARCHAR(20)') AS OrderID,
x.item.value('@ProductID[1]', 'VARCHAR(20)') AS ProductID
FROM @cXML3.nodes('//items/item') AS x(item)
Union all
SELECT
x.item.value('@OrderID[1]', 'VARCHAR(20)') AS OrderID,
x.item.value('@ProductID[1]', 'VARCHAR(20)') AS ProductID
FROM @cXML2.nodes('//items/item') AS x(item)
Union ALl
SELECT
x.item.value('@OrderID[1]', 'VARCHAR(20)') AS OrderID,
x.item.value('@ProductID[1]', 'VARCHAR(20)') AS ProductID
FROM @cXML1.nodes('//items/item') AS x(item)
) A FOR XML RAW('item'), ROOT('items'), TYPE)
SET @cXML = @cXML_
Select @cXML1
SELECT
x.item.value('@OrderID[1]', 'VARCHAR(20)') AS OrderID,
x.item.value('@ProductID[1]', 'VARCHAR(20)') AS ProductID
FROM @cXML1.nodes('//items/item') AS x(item)
End
--- 第二层
Alter PROCEDURE ProductOne
@cProductID as varchar(18)
,@cXML as XML Out
AS
Begin
Declare @sql as nvarchar(max)
--Declare @cXML as XML
SET @cXML = (SELECT Top 10 'T1' as OrderID,ProductID FROM Product
Where ProductID >= @cProductID
FOR XML RAW('item'), ROOT('items'), TYPE)
set @cXML.modify('insert<item OrderID = "T3" ProductID="11TEST" />
after (/items/item)[1]
')
Exec ProductTwO @cProductID,@cXML Out
End
--第三层
Alter PROCEDURE ProductTwO
@cProductID as varchar(18)
,@cXML as XML Out
AS
Begin
Declare @cXML_ as XML
Set @cXML_ = (Select * From (
Select 'T2' as OrderID,ProductID From Product Where ProductID = @cProductID
Union ALl
SELECT
x.item.value('@OrderID[1]', 'VARCHAR(20)') AS OrderID,
x.item.value('@ProductID[1]', 'VARCHAR(20)') AS ProductID
FROM @cXML.nodes('//items/item') AS x(item)
) A FOR XML RAW('item'), ROOT('items'), TYPE)
SET @cXML = @cXML_
End