中文XML论坛--Sqlserver中有关操作OpenXML的代码!(有人问,我只好在这里贴了)

1 Active Server Page
This is the complete listing of the ASP discussed previously.

<HTML>
<HEAD>
<META NAME="GENERATOR" Content="Microsoft Developer Studio"/>
<META HTTP-EQUIV="Content-Type" content="text/html" charset="iso-8859-1"/>
<TITLE>ADO 2.6 OpenXML Example - OpenXML.asp</TITLE>

<STYLE>
   BODY
   {
      FONT-FAMILY: Tahoma;
      FONT-SIZE: 8pt;
      OVERFLOW: auto
   }

   H3
   {
      FONT-FAMILY: Tahoma;
      FONT-SIZE: 8pt;
      OVERFLOW: auto
   }

</STYLE>

<%
Response.Write "Page Generated @ " & Now() & "<BR/>"

Dim sConn
sConn = "Provider=SQLOLEDB;Data Source=MYSERVER1;Initial Catalog=Northwind;User ID=SA;Password=;"

Response.write "Connect String = " & sConn & "<BR/>"

   Dim adoConn
   Set adoConn = Server.CreateObject("ADODB.Connection")
   adoConn.ConnectionString = sConn
   adoConn.CursorLocation = adUseClient
   adoConn.Open

Response.write "ADO Version = " & adoConn.Version & "<BR/>"
Response.write "adoConn.State = " & adoConn.State & "<BR/>"

Dim sXMLDoc, sQuery

   sXMLDoc = "<ROOT>"
   sXMLDoc = sXMLDoc & "<Customers CustomerID='VINET' ContactName='Paul Henriot'>"
   sXMLDoc = sXMLDoc & "<Orders CustomerID='VINET' EmployeeID='5' OrderDate='1996-07-04T00:00:00'>"
   sXMLDoc = sXMLDoc & "<Order_0020_Details OrderID='10248' ProductID='11' Quantity='12'/>"
   sXMLDoc = sXMLDoc & "<Order_0020_Details OrderID='10248' ProductID='42' Quantity='10'/>"
   sXMLDoc = sXMLDoc & "</Orders>"
   sXMLDoc = sXMLDoc & "</Customers>"
   sXMLDoc = sXMLDoc & "<Customers CustomerID='LILAS' ContactName='Carlos Gonzlez'>"
   sXMLDoc = sXMLDoc & "<Orders CustomerID='LILAS' EmployeeID='3' OrderDate='1996-08-16T00:00:00'>"
   sXMLDoc = sXMLDoc & "<Order_0020_Details OrderID='10283' ProductID='72' Quantity='3'/>"
   sXMLDoc = sXMLDoc & "</Orders>"
   sXMLDoc = sXMLDoc & "</Customers>"
   sXMLDoc = sXMLDoc & "</ROOT>"

sQuery = "SP_OpenXML_Example"
Response.write "sQuery = " & sQuery & "<BR/>"

   Dim adoCmd
   Set adoCmd = Server.CreateObject("ADODB.Command")
   Set adoCmd.ActiveConnection = adoConn
   adoCmd.CommandText = sQuery
   adoCmd.CommandType = adCmdStoredProc
   adoCmd.Parameters.Refresh
   adoCmd.Parameters.Item(1).Value = sXMLDoc

Dim adoRS
Set adoRS = adoCmd.Execute()

Response.write "Data = " & adoRS.Fields(0).Value & "<BR/>"
adoRS.Close

Response.write "ReturnValue = " & adoCmd.Parameters.Item(0).Value & "<BR/>"

%>
</HEAD>
<BODY>
</BODY>
</HTML>

2 SQL Server2000提供了OpenXML特性
我们可以很方便的用OpenXML特性来代替传统的行集结果,同样我们也可以使用定义好的XML文档作为输入的数据,这样也为我们的数据移植带来的巨大的便利。

同时,SQL Server2000中也提供了存储过程,用微软的话说，只有设计成存储过程，才算是”cleaner separation of code from the middle-tier”，这样做是很好的。应用程序访问数据库的数据并不是直接的与数据库表打交道，而是通过存储过程的运行来获取所需要的数据。这样的设计有一个好处就是，避免了频繁的表操作，而通过运行在服务器端的存储过程可以极大的提高运作效率和提升访问数据的速度，同时也很好的屏蔽了数据库表的逻辑，使得数据库访问变成了数据库提供的服务访问。

既然OpenXML和存储过程都可以提高SQL Server2000的性能,那么能否把这种技术结合起来了?答案是肯定的,下面我们就来看看怎么在存储过程中使用OpenXML.

以下是一个使用了OpenXML特性的存储过程:

*******************************************************************************

-------------------------------------------------------------------------------

-- upArchiveMsgInsertMsg

-------------------------------------------------------------------------------

/******************************************************************************

这是一个公文消息xml文档的实例

declare @xml varchar(8000)

SET @xml='<?xml version="1.0" encoding="gb2312" ?>

<ArchiveMsg

Title="jiangsuer" UserID="Admin" Author="湖北"

SendTime="1/1/2001" Department="mse"

AttachFile="hust"

ReceiveList="younther" Body="hustwelcome">

</ArchiveMsg>'

exec upArchiveMsgInsertMsg @xml

*******************************************************************************/

//这个运行存储过程先在ArchiveMsg表中插入一条公文信息，并根据该条公文信息的MsgID和接受者列表，再在MsgList表插入每个接受者的UserID和MsgID，这些信息从xml中获取。

ALTER PROCEDURE upArchiveMsgInsertMsg

(

@xml varchar(8000)

)

DECLARE @idoc int -- xml doc

DECLARE @MsgID int -- new order

DECLARE @SendTime datetime

-- 解析XML文档

EXEC sp_xml_preparedocument @idoc output, @xml

SET NOCOUNT ON

DECLARE @CurrentError int

BEGIN TRANSACTION

-- 开始更新数据的事务

SELECT @SendTime=SendTime

FROM OpenXML(@idoc, '/ArchiveMsg')

WITH ArchiveMsg

INSERT INTO ArchiveMsg(Title,UserID,Author,SendTime,Department,ReceiveList,Body,AttachFile)

SELECT Title,UserID,Author,SendTime,Department,ReceiveList,Body,AttachFile

FROM OpenXML(@idoc, '/ArchiveMsg')

WITH ArchiveMsg

-- 错误检查

SELECT @CurrentError = @@Error

IF @CurrentError != 0

BEGIN

GOTO ERROR_HANDLER

END

SELECT @MsgID=@@IDENTITY

INSERT INTO MsgList(MsgID,UserID,SendTime)

SELECT @MsgID,UserID,@SendTime

FROM OpenXML(@idoc, '/ArchiveMsg/MsgList')

WITH (

MsgID int,

UserID varchar(80),

SendTime datetime

)

-- 错误检查

SELECT @CurrentError = @@Error

IF @CurrentError != 0

BEGIN

GOTO ERROR_HANDLER

END

-- 事务结束

COMMIT TRANSACTION

SET NOCOUNT OFF

-- 从内存中移除定义的XML文档

EXEC sp_xml_removedocument @idoc

RETURN 0

ERROR_HANDLER:

ROLLBACK TRANSACTION

SET NOCOUNT OFF

-- 从内存中移除定义的XML文档

EXEC sp_xml_removedocument @idoc

RETURN @CurrentError

*******************************************************************************

好了,存储过程就是这样了,我们得测试这个存储过程,测试函数代码如下:

首先我们得生成这个XML文档,为了简便起见,我们就用一个String变量来摸拟XML文档:

public string GetXmlString(ArchiveDetail myMsg,string[] useridList)

{

string xml="<?xml version=\"1.0\" encoding=\"gb2312\" ?>";

xml+="<ArchiveMsg ";

xml+="Title=\""+myMsg.Title+"\" ";

xml+="UserID=\""+myMsg.UserID+"\" ";

xml+="Author=\""+myMsg.Author+"\" ";

xml+="SendTime=\""+myMsg.SendTime.ToString()+"\" ";

xml+="Department=\""+myMsg.Department+"\" ";

xml+="AttachFile=\""+myMsg.AttachFilepath+"\" ";

xml+="ReceiveList=\""+myMsg.ReceiveList+"\" ";

xml+="Body=\""+myMsg.Body+"\">";

foreach(string i in useridList)

{

xml+="<MsgList UserID=\""+i+"\"/>";

}

xml+="</ArchiveMsg>";

return xml;

}

然后就是调用这个存储过程的函数:

public bool SendArchiveMsg(ArchiveDetail myMsg,string[] useridList)

{

string xml=this.GetXmlString(myMsg,useridList);

try

{

SqlParameter[] prams={ data.MakeInParam("@xml",SqlDbType.VarChar,8000,xml)

};

SqlConnection con = new

SqlConnection("server=127.0.0.1;uid=sa;pwd=sa;database=mydatabase");

con.Open();

SqlCommand cmd = new SqlCommand("upArchiveMsgInsertMsg", con);

cmd.CommandType = CommandType.StoredProcedure;

foreach (SqlParameter parameter in prams)

cmd.Parameters.Add(parameter);

cmd.Parameters.Add(

new SqlParameter("ReturnValue", SqlDbType.Int, 4,

ParameterDirection.ReturnValue, false, 0, 0,

string.Empty, DataRowVersion.Default, null));

cmd.ExecuteNonQuery();

con.Close();

int retval = (int)cmd.Parameters["ReturnValue"].Value;

if(retval==0)

return true;

else

return false;

}

catch(Exception ex)

{

Error.Log(ex.ToString());

return false;

}

在上述的代码中，代码量比较多，但是实际的处理过程其实比较简单，大致如下：

定义xml文

在存储过程中引入xml,利用sql server的open xml对之处理（具体就是几个与xml相关的函数）

在程序中调用存储过程

结束。


	W 3 C h i n a ( since 2003 ) 旗下站点苏ICP备05006046号《全国人大常委会关于维护互联网安全的决定》《计算机信息网络国际联网安全保护管理办法》	2,531.250ms