Summary Insert XML file into a table using ADO

I've seen several times this question being asked both at the talk back and the on-line discussion, so I've decide to try and make a few words on the subject…

It's easy to persist a recordset into XML using the ADO Persistence Provider, something like this…

rst.Save D:\SomefileName.xml, adPersistXML

And Loading it back into a ADO recordset

rst.Open D:\SomefileName.xml

For more information on this see the excellent article by Mark Wilson at

http://www.topxml.com/xml/guides/developers/ado_persist_xml.asp

The Problem here is having a ADO recordset with the data how do you put it on the table. Well This works like any ADO disconnected recordset… I will try and explain what is needed in order for this to work.

The first step is the persistence is of key importance, in order for the disconnected recordset to be updateable you must specify the LockType of the recordset as being adLockBatchOptimistic

Something like this: (NOTE: All Procedures made with Access97)

Sub ADO2XML_Upt()
Dim cnn As ADODB.Connection
Dim rst As ADODB.Recordset
 
  Set cnn = New ADODB.Connection
  cnn.Open Provider=Microsoft.Jet.OLEDB.4.0; & _
            Data Source= & CurrentDb.Name & ;
  Set rst = New ADODB.Recordset
  With rst
    Set .ActiveConnection = cnn
    .Source = SELECT * FROM Customers
    .CursorType = adOpenStatic
    .LockType = adLockBatchOptimistic
    .Open
    .Save C:\Temp\pmpg.xml, adPersistXML
    .Close
  End With
  Set rst = Nothing
  Set cnn = Nothing
End Sub

In terms of the XML resultant of this, there will be some differences that make possible the batch processing of the records on that XML file, The most important ones are:

On the tag <s:ElementType> is added the attribute rs:updatable='true'

<s:ElementType name='row' content='eltOnly' rs:updatable='true'>

And for each tag <s:AttributeType> is added the two following attributes

rs:basetable='Customers' rs:basecolumn='Address'

This will help to known witch table and column the data come from.

Now the reverse operation is to load the XML/XDR file into a ADO recordset

Sub XML2ADO()
Dim cnn As ADODB.Connection
Dim rst As ADODB.Recordset

  Set cnn = New ADODB.Connection
  Set rst = New ADODB.Recordset
  With rst
    .CursorLocation = adUseServer
    .Open C:\Temp\pmpg.xml
    cnn.Open Provider=Microsoft.Jet.OLEDB.4.0; & _
            Data Source= & CurrentDb.Name & ;
    .ActiveConnection = cnn
    .UpdateBatch
  End With
End Sub

But this will do nothing isn't it, because you didn't made any change to the ADO recordset have you?

If you add a new record to the disconnected recordset then you will see that that record will be added.

Sub XML2ADO_addnew()
Dim cnn As ADODB.Connection
Dim rst As ADODB.Recordset
 
  Set cnn = New ADODB.Connection
  cnn.Open Provider=Microsoft.Jet.OLEDB.4.0; & _
            Data Source= & CurrentDb.Name & ;
  Set rst = New ADODB.Recordset
  With rst
    Set .ActiveConnection = cnn
    .Source = SELECT * FROM Customers
    .CursorLocation = adUseClient
    .CursorType = adOpenStatic
    .LockType = adLockBatchOptimistic
    .Open
    Set .ActiveConnection = Nothing 'Disconnect
    .AddNew
      !CUSTOMERID = PMPG
    .Update
    .Save C:\Temp\pmpg.xml, adPersistXML
    .Close
  End With
  Set rst = Nothing
  Set cnn = Nothing
End Sub

If you check the XML created you will see that one element like this was created.
<rs:insert>
  <z:row CustomerID='PMPG' rs:forcenull='Address City CompanyName ContactName ContactTitle Country Fax Phone PostalCode Region'/>
</rs:insert>

Like wise you have a different operation for INSERT (above), UPDATE and DELETE

<rs:update>
  <rs:original>
    <z:row CustomerID='ALFKI' CompanyName='Alfreds Futterkiste' ContactName='Maria Anders' ContactTitle='Sales Representative' Address='Obere Str. 57' City='Berlin' PostalCode='12209' Country='Germany' Phone='030-0074321' Fax='030-0076545'/>
    </rs:original>
<z:row CustomerID='PMPG'/>
</rs:update>

<rs:delete>
<z:row CustomerID='ALFKI' CompanyName='Alfreds Futterkiste' ContactName='Maria Anders' ContactTitle='Sales Representative' Address='Obere Str. 57' City='Berlin' PostalCode='12209' Country='Germany' Phone='030-0074321' Fax='030-0076545'/>
</rs:delete>

So after you made this INSERT, UPDATE and DELETE, if you reconnect the recordset and use the UpdateBatch this changes will be made in the table.

I've been using the ADO DML capabilities, but nothing makes you use those, you can persist the ADO recordset into XML, and then use whatever tools you want to make the changes, the only thing that is mandatory is the syntax of the changes.

INSERT
  <rs:insert><z:row SomeField=NewValue/></rs:insert>
UPDATE
  <rs:update>
  <rs:original><z:row SomeField=OldValue/></rs:original>
  <z:row SomeField=NewValue/>
  </rs:update>
DELETE
<rs:delete>
<z:row SomeField=OldValue/>
</rs:delete>

Hope that this word help you better understand ADO disconnected recordsets and the persistante provider.

Pedro Gil

Additional information

n/a