OLEDB Simple Provider - Xml Reader

So I have chanced upon something call the OLEDB Simple Provider which allows VBA to convert an Xml document into a recordset. Worth investigating some more in its own right because it is said to support shaped, i.e. hierarchical recordsets. But right now, I want to find how to implement a custom OLEDB Simple Provider for an in memory array, a goal I've had for some time.

It has to be said that a recordset can be saved to Xml and indeed a saved Xml recordset can be synthesised as I did in this blog post.

Option Explicit

'* Tools->References
'MSXML2     Microsoft XML, v6.0                         C:\Windows\SysWOW64\msxml6.dll
'ADODB      Microsoft ActiveX Data Objects 6.1 Library  C:\Program Files (x86)\Common Files\System\ado\msado15.dll

Sub Test()
    Dim xmlDom As MSXML2.DOMDocument60
    Set xmlDom = New MSXML2.DOMDocument60
    
    Dim sColorsXml As String
    sColorsXml = "<colors>" & _
            "<color><colorname>Red</colorname><colorRGB>FF0000</colorRGB></color>" & _
            "<color><colorname>Green</colorname><colorRGB>00FF00</colorRGB></color>" & _
            "<color><colorname>Blue</colorname><colorRGB>0000FF</colorRGB></color></colors>"

    
    xmlDom.LoadXML sColorsXml
    Debug.Assert xmlDom.parseError = 0
    
    Const sFileName As String = "N:Colors.xml"
    xmlDom.Save sFileName

    Dim oConn As ADODB.Connection
    Set oConn = New ADODB.Connection
    'oConn.Open "Provider=MSDAOSP;Data Source=MSXML2.DSOControl.5.0"
    oConn.Open "Provider=MSDAOSP;Data Source=MSXML2.DSOControl"

    Dim rs As ADODB.Recordset
    Set rs = New ADODB.Recordset
    Set rs.ActiveConnection = oConn
    
    rs.Open sFileName
    rs.MoveFirst
    Debug.Assert rs.RecordCount = 3
    Debug.Assert rs.Fields.Count = 3  '* this surprised me
    Debug.Assert rs.Fields.Item(0).Name = "colorname"
    Debug.Assert rs.Fields.Item(1).Name = "colorRGB"
    Debug.Assert rs.Fields.Item(2).Name = "$Text"  '* this is the additional unexpected field, it concatentates the others
        
    '* lets get the recordset to a variant array
    Dim vVarArray
    vVarArray = Application.WorksheetFunction.Transpose(rs.GetRows)
    Debug.Assert vVarArray(1, 1) = "Red"
    Debug.Assert vVarArray(1, 2) = "FF0000"
    Debug.Assert vVarArray(1, 3) = "RedFF0000"
    Debug.Assert vVarArray(2, 1) = "Green"
    Debug.Assert vVarArray(2, 2) = "00FF00"
    Debug.Assert vVarArray(2, 3) = "Green00FF00"
    Debug.Assert vVarArray(3, 1) = "Blue"
    Debug.Assert vVarArray(3, 2) = "0000FF"
    Debug.Assert vVarArray(3, 3) = "Blue0000FF"
    
    Stop
End Sub

Appendix A - Registry Entries

Just doing some digging into the load sequence etc....

So "MSXML2.DSOControl" is a ProgId and we can go find it in the registry ...

Windows Registry Editor Version 5.00

[HKEY_CLASSES_ROOT\Msxml2.DSOControl.5.0]
@="XML Data Source Object 5.0"

[HKEY_CLASSES_ROOT\Msxml2.DSOControl.5.0\CLSID]
@="{88D969E9-F192-11D4-A65F-0040963251E5}"

So we can lookup the CLSID, {88D969E9-F192-11D4-A65F-0040963251E5} ...

Windows Registry Editor Version 5.00

[HKEY_CLASSES_ROOT\Wow6432Node\CLSID\{88D969E9-F192-11D4-A65F-0040963251E5}]
@="XML Data Source Object 5.0"

[HKEY_CLASSES_ROOT\Wow6432Node\CLSID\{88D969E9-F192-11D4-A65F-0040963251E5}\InProcServer32]
@="C:\Program Files (x86)\Common Files\Microsoft Shared\OFFICE11\MSXML5.DLL"
"ThreadingModel"="Apartment"

[HKEY_CLASSES_ROOT\Wow6432Node\CLSID\{88D969E9-F192-11D4-A65F-0040963251E5}\ProgID]
@="Msxml2.DSOControl.5.0"

[HKEY_CLASSES_ROOT\Wow6432Node\CLSID\{88D969E9-F192-11D4-A65F-0040963251E5}\TypeLib]
@="{F5078F18-C551-11D3-89B9-0000F81FE221}"

[HKEY_CLASSES_ROOT\Wow6432Node\CLSID\{88D969E9-F192-11D4-A65F-0040963251E5}\Version]
@="5.0"

We can find the type library with {F5078F18-C551-11D3-89B9-0000F81FE221} There were multiple versions, I'm showing version 5 ...

Windows Registry Editor Version 5.00

[HKEY_CLASSES_ROOT\Wow6432Node\TypeLib\{F5078F18-C551-11D3-89B9-0000F81FE221}]

[HKEY_CLASSES_ROOT\Wow6432Node\TypeLib\{F5078F18-C551-11D3-89B9-0000F81FE221}\5.0]
@="Microsoft XML, v5.0"

[HKEY_CLASSES_ROOT\Wow6432Node\TypeLib\{F5078F18-C551-11D3-89B9-0000F81FE221}\5.0\0]
@=""

[HKEY_CLASSES_ROOT\Wow6432Node\TypeLib\{F5078F18-C551-11D3-89B9-0000F81FE221}\5.0\0\win32]
@="C:\\Program Files (x86)\\Common Files\\Microsoft Shared\\OFFICE11\\MSXML5.DLL"

[HKEY_CLASSES_ROOT\Wow6432Node\TypeLib\{F5078F18-C551-11D3-89B9-0000F81FE221}\5.0\FLAGS]
@="0"

[HKEY_CLASSES_ROOT\Wow6432Node\TypeLib\{F5078F18-C551-11D3-89B9-0000F81FE221}\5.0\HELPDIR]
@="C:\\Program Files (x86)\\Common Files\\Microsoft Shared\\OFFICE11\\"

Loading the type library C:\Program Files (x86)\Common Files\microsoft shared\OFFICE11.MSXML5.DLL to see what interfaces DSOControl supports we can see it only has one

[
  uuid(88D969E9-F192-11D4-A65F-0040963251E5),
  helpstring("XML Data Source Object")
]
coclass DSOControl50 {
    [default] interface IDSOControl;
};


[
  odl,
  uuid(310AFA62-0575-11D2-9CA9-0060B0EC3D39),
  helpstring("DSO Control"),
  hidden,
  dual,
  nonextensible,
  oleautomation
]
interface IDSOControl : IDispatch {
    [id(0x00010001), propget]
    HRESULT XMLDocument([out, retval] IXMLDOMDocument** ppDoc);
    [id(0x00010001), propput]
    HRESULT XMLDocument([in] IXMLDOMDocument* ppDoc);
    [id(0x00010002), propget]
    HRESULT JavaDSOCompatible([out, retval] long* fJavaDSOCompatible);
    [id(0x00010002), propput]
    HRESULT JavaDSOCompatible([in] long fJavaDSOCompatible);
    [id(0xfffffdf3), propget]
    HRESULT readyState([out, retval] long* state);
};

And there the trail goes cold, nothing interesting about that interface, certainly nothing that can help our custom implementation of OLEDB Simple Provider.