Sample Visual Basic Application to Update Records Using OPENXML and ADO

The application is based on the assumption that the client has an XML document (created with some other application) that is to be used to apply updates to the database.

This example shows:

• Writing a simple Microsoft® Visual Basic® application to update the database using XML documents.
  
  
• Using ADO to execute XML templates.
  
  
• Creating and execute templates and pass parameters to the templates.
  
  
• Creating a rowset from an XML document using OPENXML.

These are the steps to create a working sample:

1. Create this table:

   CREATE TABLE Employee(eid int, fname varchar(20), lname varchar(20))
   

2. Add sample data:

   INSERT INTO Employee VALUES (1, 'Nancy', 'Davolio')
   INSERT INTO Employee VALUES (2, 'Andrew', 'Fuller')
   

3. Create the following stored procedure in the database:

   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   
   

4. Create a Visual Basic project (a standard EXE project is sufficient).
   
   
5. Add Microsoft ActiveX® Data Objects 2.6 Library to the project references.
   
   
6. Add the following code:

   '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 = 0
   
   Set 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