Retrieving Information from Element Nodes

Let's assume that you want to read the information only from the text element nodes and place it in an Excel spreadsheet. Use the getElements-ByTagName method of the DOMDocument object to retrieve an IXMLDOMNodeList object containing all the element nodes. The getEle-mentsByTagName method takes one argument specifying the tag name for which to search. To search for all the element nodes, use the "*" as the tag to search for (as illustrated in the procedure below).

The following exercise demonstrates how to obtain data from XML document element nodes.

1. Enter the following procedure in a module. Remember to add References to the MSXML type library and change the path of the XML document to point to the file location on your disk.

2. Run the procedure in a step mode by pressing F8.

Sub IterateThruElements()

Dim xmldoc As MSXML2.D0MDocument30 Dim xmlNodeList As MSXML2.IXMLD0MNodeList Dim xmlNode As MSXML2.IXMLD0MNode Dim myNode As MSXML2.IXMLD0MNode

' Create an instance of the DOMDocument Set xmldoc = New MSXML2.D0MDocument30 xmldoc.async = False ' Load XML information from a file xmldoc.Load ("C:\ExcelVBA2002\Chap17\Courses.xml") ' find out the number of child nodes in the document Set xmlNodeList = xmldoc.getElementsByTagName("*") Workbooks.Add

Range("A1:B1").Formula = Array("Element Name", "Text") For Each xmlNode In xmlNodeList

For Each myNode In xmlNode.childNodes If myNode.nodeType = N0DE_TEXT Then

ActiveCell.0ffset(0, 0).Formula = xmlNode.nodeName ActiveCell.0ffset(0, 1).Formula = xmlNode.Text End If Next myNode

ActiveCell.0ffset(1, 0).Select Next xmlNode End Sub

The IterateThruElements procedure fills in two spreadsheet columns with the XML element name and the corresponding text for all the text elements in the Courses.xml document (see the procedure result in Figure 17-25). Notice that this procedure uses two For...Each... Next loops. The first one (outer For...Each...Next loop) iterates through the entire collection of element nodes. The second one (inner For...Each...Next loop) uses the nodeType property to find only those element nodes that contain a single text node.

0 0

Post a comment