MS SQLServer 操作XML语句的存储过程

-- ================================================

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE PROCEDURE SP_Reports_GetParams    

    @ParamsString nvarchar(max),

    @Name nvarchar(100),

    @Value nvarchar(250) output

AS

BEGIN    

DECLARE @xmlDoc integer

EXEC sp_xml_preparedocument @xmlDoc OUTPUT, @ParamsString

SELECT top 1 @Value=Value FROM

OPENXML (@xmlDoc, 'Params/Item', 1)

WITH 

(

  Name nvarchar,

  Value nvarchar)

WHERE Name = @Name

EXEC sp_xml_removedocument @xmlDoc

END

GO

--调用示例

SET @doc = '<Params>

　<Item Name="a" Value="1"/>

　<Item Name="b" Value="2"/>

  </Params>';

DECLARE @aaa nvarchar(250);

exec AmwayFrameworkWorkflow.dbo.SP_Reports_GetParams @doc,'b' ,@aaa output;

select @aaa