如何使用XML格式的参数在SQL server的存贮过程中。

 

背景：在开发时，有时我们希望能够在客户一次传入一批数据，让存贮过程批量处理。用来代替循环调用存贮过程，减少客户端和数据库的交互。以便可以节省处理时间。

 

解决思路：把需要传递的批量数据（通常是一个数据集）格式化成一个XML流的参数，然后传递这个XML格式的参数到存贮过程。让存贮过程循环处理。

 

解决方案：

利用SQLServer 的OPENXML命令来处理。此命令的主要功能是能读取以XML格式传入的参数。

 

一个例子：（本例中读取后的数据是放入游标中，由游标来控制，循环处理数据）

CREATE PROCEDURE dbo.UP_BatchPostRMA 

(

    @XMLNumbers text,           -- Save the Rma numbers and So numbers   

)

AS

 

    SET NOCOUNT ON

    -- Define local variables

    DECLARE@RMANumber      int,

            @SoNumber       int

    --Read data from xml text. 

   DECLARE@idoc int    

    EXEC dbo.sp_xml_preparedocument @idoc OUTPUT, @XMLNumbers   

   

    DECLARERma_Cursor CURSOR LOCAL FAST_FORWARD FOR

        SELECTRmaNumber, SoNumber

        FROM OPENXML (@idoc, '/NewDataSet/Table',2)   --从XML中读取数据RmaNuber,SoNumber。

            WITH (   

                RmaNumberint,

                SoNumber int

            )

   

    OPEN Rma_Cursor

   

    FETCH NEXT FROMRma_Cursor INTO @RMANumber, @SoNumber

   

    WHILE @@FETCH_STATUS = 0

    BEGIN    

        UPDATEdbo.CreditForRma

           SET postdate = GETDATE()

         WHERErmanumber = @RMANumber

               and sonumber = @SoNumber

 

        FETCH NEXT FROMRma_Cursor INTO @RMANumber, @SoNumber

    END

   

    CLOSE Rma_Cursor

    DEALLOCATERma_Cursor

   

    EXECdbo.sp_xml_removedocument @idoc

GO

说明： OPENXML (@idoc, '/NewDataSet/Table',2)。第一个参数：是 XML 文档的内部表式法的文档句柄。通过调用 sp_xml_preparedocument 创建。本例中中通过EXEC dbo.sp_xml_preparedocument @idoc OUTPUT, @XMLNumbers 来得到的。

第二个参数表示:用来标识要作为行处理的节点（这些节点在 XML 文档中，该文档的句柄由 idoc 参数传递）。即数据节点是从那个节点开始的。

另：由于客户端是采用DataSet的GetXML方法把包含数据的数据集，序列化成XML格式的文本。数据是存在 /NewDataSet/Table结点下的。NewDataSet 就是DataSet的缺省DataSetName值（你可以重新命名） Table 就是DataSet下table的缺省TableName（也可重新命名）例子使用了缺省值。

第三参数：2表示使用以元素为中心的映射。

其它用法请参见：microsofte的SQL sever所带的帮助文件，那里有详细的说明和例子。

 

总结：

其实读取XML格式的参数由上的例子可看出分三步走

 1 EXEC dbo.sp_xml_preparedocument @idoc OUTPUT, @XMLNumbers  --获取传入的XML格式参数的文档句柄。

 2 SELECT RmaNumber, SoNumber

        FROM OPENXML (@idoc,'/NewDataSet/Table',2)   --从XML中读取数据RmaNuber,SoNumber。

            WITH (   

                RmaNumber int,

                SoNumber int

            ) --解析XML数据。并读出数据

 3 EXEC dbo.sp_xml_removedocument @idoc --删除文档句柄。

 

附：在客户端的调用例子（ C# 编码）

SqlConnection sqlConn = new SqlConnection(connString); //connString：连接字符串。

              SqlCommand sqlCmd = new SqlCommand("dbo.UP_BatchPostRMA", sqlConn);

              sqlCmd.CommandType = CommandType.StoredProcedure;

              sqlCmd.CommandTimeout = 1800;// 30分钟 定义是否超时时间

              // 定义SQL参数

              SqlParameter[] parameters =new SqlParameter[4];

              parameters[0] = new SqlParameter("@XMLNumbers", SqlDbType.Text);

             

              parameters[0].Value = ds.GetXml();//使用GetXml方法，使参数变为一个XML格式的流。

             

              // 将参数添加到SqlCommand对象中

              for(int i=0; i<parameters.Length; i++)

              {

                   sqlCmd.Parameters.Add(parameters[i]);

              }

              sqlConn.Open();//执行调用存贮过程。