背景:在开发时,有时我们希望能够在客户一次传入一批数据,让存贮过程批量处理。用来代替循环调用存贮过程,减少客户端和数据库的交互。以便可以节省处理时间。
解决思路:把需要传递的批量数据(通常是一个数据集)格式化成一个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
DECLARE Rma_Cursor CURSOR LOCAL FAST_FORWARD FOR
SELECT RmaNumber, SoNumber
FROM OPENXML (@idoc, '/NewDataSet/Table',2) --从XML中读取数据RmaNuber,SoNumber。
WITH (
RmaNumber int,
SoNumber int
)
OPEN Rma_Cursor
FETCH NEXT FROM Rma_Cursor INTO @RMANumber, @SoNumber
WHILE @@FETCH_STATUS = 0
BEGIN
UPDATE dbo.CreditForRma
SET postdate = GETDATE()
WHERE rmanumber = @RMANumber
and sonumber = @SoNumber
FETCH NEXT FROM Rma_Cursor INTO @RMANumber, @SoNumber
END
CLOSE Rma_Cursor
DEALLOCATE Rma_Cursor
EXEC dbo.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();//
执行调用存贮过程。