Results 1 to 1 of 1
Thread: More XML fun (Acc2K, Win2K Pro)
2007-06-21, 17:28 #1
- Join Date
- Jan 2001
- austin, Texas, USA
- Thanked 0 Times in 0 Posts
More XML fun (Acc2K, Win2K Pro)
Thanks to all the loungers who've been helping me with my project! I now have a VBA method for reading a parsing the target XML file using the following code:
Dim xmlDoc As New MSXML2.DOMDocument
Dim answers As MSXML2.IXMLDOMNodeList, answer As MSXML2.IXMLDOMElement
Dim lngIndex As Long
xmlDoc.async = False
xmlDoc.Load "C:InetpubwwwrootOIGSurveyPortalCASO_Survey_Test_a nswers.xml"
Set answers = xmlDoc.getElementsByTagName("Answer")
For lngIndex = 0 To answers.length - 1
Set answer = answers.Item(lngIndex)
Debug.Print answer.getAttribute("questionId") & "<-->" & answer.Text
Which outputs data like so:
As you can see, and question followed by an answer...
As you may also notice, the output consists of two responses to the same set of questions. In order to read the answer set into a db table, I need to model just the question set as table columns and the answers as rows to these columns. I'm thinking I need to go 'up' one step in the DOM at least. One strategy I have been working on is to build a SELECT INTO statement in order to create a table 'on the fly' from a loaded XML.
Currently, however, I have not been able to figure out how to do simple things, like enumerate just the standard question set and find the total number of rows based on the XML file data. I'll keep slugging away, but was hoping some loungers who are more experienced in the way of the XML DOM might be of help...
Also, just out of interest, does Access 2003 Import to XML do all of this 'automatically'?