Solved

Import XML into recordset *NOT PERSIST FORMAT*

Posted on 2004-08-11
5
1,302 Views
Last Modified: 2007-12-19
I'm looking for some sample code that will allow me to convert an XML file into an ADO recordset.  Excel seems to have functions built in that convert XML straight into a table format in exactly the style I need, but it seems clumsy to have to open the XML file in Excel, save that down and then open that as a recordset - surely there is an easier way?

PLEASE NOTE this XML file was not created using the XML Persist option from the Save method of ADO.  It is just a bog standard XML file:

<?xml version="1.0" encoding="utf-8" standalone="yes"?>
<Devices>
<Device>
<Device_Name />
<LC_Description>LC GBR</LC_Description>
<Site_ID>WLH</Site_ID>
<Device_ID>10005999</Device_ID>
</Device>
<Device>
<Device_Name />
<LC_Description>LC GBR</LC_Description>
<Site_ID>WLH</Site_ID>
<Device_ID>10006005</Device_ID>
</Device>
</Devices>

Can anyone help?

I'll need some sample code to get the points I'm afraid, pointing me in the right direction won't be enough.  A complete answer will get a further 500 points in a separately posted question.
0
Comment
Question by:Belazir
  • 2
  • 2
5 Comments
 
LVL 19

Expert Comment

by:arif_eqbal
ID: 11780776
Well it can be done easily if you use ADO 2.5 Library, it allows you to create a ADO recordset from an XML file opened as a straem.

You want sample code, a full blown sample is available visit:

http://support.microsoft.com/default.aspx?scid=http://support.microsoft.com:80/support/kb/articles/Q263/2/47.ASP&NoWebContent=1

OR

search for string

"How To Obtain an ADO Recordset from XML"

in MSDN
0
 

Author Comment

by:Belazir
ID: 11781114
Arif

From MSDN:

"For more information regarding the schema an XML document must conform to to be opened correctly by an ADO 2.5 recordset, see the "XML Persistence Format" section of the following article:
XML Integration Features in ADO 2.5"

As stated, my XML file does not conform so I cannot open it this way.  If this is possible without the format being as described please show me how as I have tried this with no success.
B

0
 
LVL 19

Expert Comment

by:arif_eqbal
ID: 11781176
Well If this does not work then I think then you will need to parse the xml file using some xml parser and create a disconnected recordset manually.
0
 
LVL 1

Accepted Solution

by:
LeighWardle earned 500 total points
ID: 11791453
Try this code:

Sub import_xml_to_recordset(strDBFilePath As String, strTableName As String, strXMLFileName As String, strXMLParentNodeName As String)

Dim MyDB As Database, Fd() As New Field

Dim rsMaterialTypes As Recordset

ReDim Fd(1 To 3)

Const DB_DOUBLE = 7  ' Set field type constant.
Set MyDB = OpenDatabase(strDBFilePath)   ' Open database.
Set rsMaterialTypes = MyDB.OpenRecordset(strTableName, dbOpenDynaset)

    Dim oDom As MSXML2.DOMDocument
    Dim oRoot As MSXML2.IXMLDOMNode

    Set oDom = New MSXML2.DOMDocument
    If Not oDom.Load(strXMLFileName) Then
        Debug.Print "XML ParseError:"
        Debug.Print oDom.parseError.reason
    Else
        Set oRoot = oDom.documentElement

        Dim oItems As MSXML2.IXMLDOMNodeList
        Dim oItem As MSXML2.IXMLDOMNode
        Dim oChildNodes As MSXML2.IXMLDOMNodeList
        Dim oChildNode As MSXML2.IXMLDOMNode
        Set oItems = oRoot.selectNodes(strXMLParentNodeName)
       
        Debug.Print "found records in XML file:" & oItems.length
       
       
        For Each oItem In oItems
       
                'Debug.Print "number of child nodes:" & oItem.childNodes.length
               
               
       
            Debug.Print
            Debug.Print "MaterialTypes_ImportTest:"
           
               
                rsMaterialTypes.AddNew
               
               
                intFieldNo = 0
                For Each oChildNode In oItem.childNodes
               
                    Debug.Print vbTab & oChildNode.nodeName & "=" & oChildNode.Text


                    rsMaterialTypes(oChildNode.nodeName) = oChildNode.Text
                   
                Next oChildNode
       
                rsMaterialTypes.Update
               
        Next oItem

    End If
    Set oRoot = Nothing
    Set oDom = Nothing

End Sub

Regards, Leigh
0
 

Author Comment

by:Belazir
ID: 11791528
Thanks Leigh, just what I was after.
B
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you have ever used Microsoft Word then you know that it has a good spell checker and it may have occurred to you that the ability to check spelling might be a nice piece of functionality to add to certain applications of yours. Well the code that…
Article by: Martin
Here are a few simple, working, games that you can use as-is or as the basis for your own games. Tic-Tac-Toe This is one of the simplest of all games.   The game allows for a choice of who goes first and keeps track of the number of wins for…
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

864 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now