XML from AS/400 into ADO recordset?

[jbradley]

Is there any way to get what I think is a pretty generic XML file created on an AS/400 into an ADO recordset in VB6? I know that a recordset can be persisted as an XML file and opened back up, but our AS/400 file is formatted differently than one persisted by ADO and returns an error message when I try yo open it.

This is my first attempt to work with XML so any and all suggestions or examples will be greatly appreciated.

 

[DarenNM]

From what I have read, the XML file needs to have a specific format for it to be opened via ADO. Do you absolutely have to open it in this way or could you open it via the XML DOM and parse the contents into a database?

Daren


Must think of a witty signature

 

[jbradley]

Daren,

Since this is my first foray into the world of XML I'm not married to any particular methodology. I just thought opening the file directly into an ADO recordset would be the cleanest way to do it, if it had worked.

Any suggestions or examples on doing it via the XML DOM and parsing the contents into a database would be greatly appreciated.

Brad

 

[DarenNM]

Hi Brad

As part of my current project I did this the other way around - I extracted data from a database and wrote it to an XML file. I got a book on XML (although there are plenty of sources on the web - I just prefer books ) and used some code examples from the web to create a basic XML viewer just to get the hang of the DOM. If you don't have the time or the inclination to do that, example code is certainly worth a look. I guess a web-address would be useful....

http://abstractvb.com/code.asp?F=64

This has a few examples that might be worth looking at.

To access an XML file's nodes you create an instance of a DOM Document (I'm currently using ver. 3):

Private mxmlDoc   As DOMDocument30

'* * * * * * * * * * * * * * * * * *

Set mxmlDoc = New DOMDocument30

Use the DOMDocument to open the XML file:

If mxmlDoc.Load(sFName) Then

	'Cycle through nodes

Else

	'Log/report error

End If 'XML file opened ok

The DOMDocument object is essentially the root node. You can use its "ChildNodes" collection to iterate through to get the data you want.

Dim xnChildNode     As IXMLDOMNode

'* * * * * * * * * * * * * * * * * *

For Each xnChildNode In mxmlDoc 
            
    Select Case xnChildNode.nodeType

            Case NODE_ELEMENT

                'Code

            Case NODE_COMMENT

                'Code

            Case NODE_TEXT

                'Code
            
            Case NODE_PROCESSING_INSTRUCTION
                
                'Code
                        
    End Select 'xnChildNode.nodeType


Next xnChildNode

I believe you can also access the nodes directly if you know the node-names ahead of time (which I would imagine you will).

I used user-defined types to store my database data, integrity check it and add/tidy data before creating XML nodes and populating them from the UDTs. I don't see why you can't do a similar thing but in reverse for your application - store the data from the XML nodes, build INSERT queries and then execute them.

I hope this has been of use (even if it has pointed you towards a different solution!).

Let us know how you get on.

Regards

Daren


Must think of a witty signature

 

[jbradley]

Daren,

Thanks for the tips. I'll play with it next week. Would you reccommend the book you used? If so, what's the name? Otherwise I'll pick up something from Sams or O'Riley. I'll let you know how it works out.

Brad

 

[DarenNM]

The book I bought was "Beginning XML 2nd Edition" by Hunter, Cagle and a few others, published by Wrox. It has a chapter on the DOM as well as an appendix which lists the DOM interfaces. I found it very useful for this, and if I need to go deeper into XML there is a lot more in the book.

Good luck!

Regards

Daren


Must think of a witty signature