使用 OPENXML 和 ADO 更新记录的示例 Visual Basic 应用程序 该应用程序基于客户端已有 XML 文档(用某些其它应用程序创建)的假设,并且该文档用以将更新应用到数据库。 下例显示: 编写一个简单的 Microsoft? Visual Basic? 应用程序以更新使用 XML 文档的数据库。 用 ADO 执行 XML 模板。 创建及执行模板,并将参数传递给模板。 用 OPENXML 从 XML 文档中创建一个行集。 创建工作示例包含以下几步: 创建下表: CREATE TABLE Employee(eid int, fname varchar(20), lname varchar(20))添加示例数据: INSERT INTO Employee VALUES (1, 'Nancy', 'Davolio') INSERT INTO Employee VALUES (2, 'Andrew', 'Fuller')在数据库中创建下面的存储过程: CREATE PROC update_employee @empdata nvarchar(4000) AS DECLARE @hDoc int exec sp_xml_preparedocument @hDoc OUTPUT,@empdata UPDATE Employee SET Employee.fname = XMLEmployee.fname, Employee.lname = XMLEmployee.lname FROM OPENXML(@hDoc, 'update/Employee') WITH Employee XMLEmployee WHERE Employee.eid = XMLEmployee.eid EXEC sp_xml_removedocument @hDoc 创建 Visual Basic 项目(一个标准的 EXE 项目就足够了)。 将 Microsoft ActiveX? Data Objects 2.6 Library 添加到项目引用中。 添加下列代码: 'The code uses ADO to establish a SQL Server connection and passes in a 'template to the server. The template executes a stored procedure '(update_employee) which accepts an XML document as input. The stored 'procedure uses OPENXML to shred the document and generate a rowset 'which is used to update the records in the Employee table. 'The template is then executed on the server and the resulting stream 'is returned to the client. The stream contains the resulting XML 'document.Dim cmd As New ADODB.Command Dim conn As New ADODB.Connection Dim strmIn As New ADODB.Stream Dim strmOut As New ADODB.Stream' Open a connection to the SQL Server. conn.Provider = "SQLOLEDB" conn.Open "server=(local); database=Northwind; uid=sa; "Set cmd.ActiveConnection = conn' Build the command string in the form of an XML template. SQLxml = "<root xmlns:sql=""urn:schemas-microsoft-com:xml-sql""><sql:query><![CDATA[" SQLxml = SQLxml & "exec update_employee N'<update><Employee eid=""1"" lname=""Leverling"" fname=""Janet""/>" SQLxml = SQLxml & "<Employee eid=""2"" lname=""Peacock"" fname=""Margaret""/></update>']]>" SQLxml = SQLxml & "</sql:query></root>"' Set the command dialect to XML. cmd.Dialect = "{5d531cb2-e6ed-11d2-b252-00c04f681b71}"' Open the command stream and write our template to it. strmIn.Open strmIn.WriteText SQLxml strmIn.Position = 0Set cmd.CommandStream = strmIn' Execute the command, open the return stream, and read the result. strmOut.Open strmOut.LineSeparator = adCRLF cmd.Properties("Output Stream").Value = strmOut cmd.Execute , , adExecuteStream strmOut.Position = 0 Debug.Print strmOut.ReadText
你先说明你使用什么开发工具,如果象楼上说的使用C#的话,这个就非常容易,在Dataset中有一个WriteXml()方法
该应用程序基于客户端已有 XML 文档(用某些其它应用程序创建)的假设,并且该文档用以将更新应用到数据库。 下例显示: 编写一个简单的 Microsoft? Visual Basic? 应用程序以更新使用 XML 文档的数据库。
用 ADO 执行 XML 模板。
创建及执行模板,并将参数传递给模板。
用 OPENXML 从 XML 文档中创建一个行集。
创建工作示例包含以下几步: 创建下表:
CREATE TABLE Employee(eid int, fname varchar(20), lname varchar(20))添加示例数据:
INSERT INTO Employee VALUES (1, 'Nancy', 'Davolio')
INSERT INTO Employee VALUES (2, 'Andrew', 'Fuller')在数据库中创建下面的存储过程:
CREATE PROC update_employee @empdata nvarchar(4000)
AS
DECLARE @hDoc int
exec sp_xml_preparedocument @hDoc OUTPUT,@empdata
UPDATE Employee
SET
Employee.fname = XMLEmployee.fname,
Employee.lname = XMLEmployee.lname
FROM OPENXML(@hDoc, 'update/Employee')
WITH Employee XMLEmployee
WHERE Employee.eid = XMLEmployee.eid
EXEC sp_xml_removedocument @hDoc 创建 Visual Basic 项目(一个标准的 EXE 项目就足够了)。
将 Microsoft ActiveX? Data Objects 2.6 Library 添加到项目引用中。
添加下列代码:
'The code uses ADO to establish a SQL Server connection and passes in a
'template to the server. The template executes a stored procedure
'(update_employee) which accepts an XML document as input. The stored
'procedure uses OPENXML to shred the document and generate a rowset
'which is used to update the records in the Employee table.
'The template is then executed on the server and the resulting stream
'is returned to the client. The stream contains the resulting XML
'document.Dim cmd As New ADODB.Command
Dim conn As New ADODB.Connection
Dim strmIn As New ADODB.Stream
Dim strmOut As New ADODB.Stream' Open a connection to the SQL Server.
conn.Provider = "SQLOLEDB"
conn.Open "server=(local); database=Northwind; uid=sa; "Set cmd.ActiveConnection = conn' Build the command string in the form of an XML template.
SQLxml = "<root xmlns:sql=""urn:schemas-microsoft-com:xml-sql""><sql:query><![CDATA["
SQLxml = SQLxml & "exec update_employee N'<update><Employee eid=""1"" lname=""Leverling"" fname=""Janet""/>"
SQLxml = SQLxml & "<Employee eid=""2"" lname=""Peacock"" fname=""Margaret""/></update>']]>"
SQLxml = SQLxml & "</sql:query></root>"' Set the command dialect to XML.
cmd.Dialect = "{5d531cb2-e6ed-11d2-b252-00c04f681b71}"' Open the command stream and write our template to it.
strmIn.Open
strmIn.WriteText SQLxml
strmIn.Position = 0Set cmd.CommandStream = strmIn' Execute the command, open the return stream, and read the result.
strmOut.Open
strmOut.LineSeparator = adCRLF
cmd.Properties("Output Stream").Value = strmOut
cmd.Execute , , adExecuteStream
strmOut.Position = 0
Debug.Print strmOut.ReadText