存储过程间XML参数传递的实例

--测试数据准备
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