How to parse XML using vba

Thanks for the pointers.

I don't know, whether this is the best approach to the problem or not, but here is how I got it to work.I referenced the Microsoft XML, v2.6 dll in my VBA, and then the following code snippet, gives me the required values

Dim objXML As MSXML2.DOMDocumentSet objXML = New MSXML2.DOMDocumentIf Not objXML.loadXML(strXML) Then  'strXML is the string with XML'    Err.Raise objXML.parseError.ErrorCode, , objXML.parseError.reasonEnd If Dim point As IXMLDOMNodeSet point = objXML.firstChildDebug.Print point.selectSingleNode("X").TextDebug.Print point.selectSingleNode("Y").Text

This is a bit of a complicated question, but it seems like the most direct route would be to load the XML document or XML string via MSXML2.DOMDocument which will then allow you to access the XML nodes.

You can find more on MSXML2.DOMDocument at the following sites:

• Manipulating XML files with Excel VBA & Xpath
• MSXML - http://msdn.microsoft.com/en-us/library/ms763742(VS.85).aspx
• An Overview of MSXML 4.0

You can use a XPath Query:

Dim objDom As Object        '// DOMDocumentDim xmlStr As String, _    xPath As StringxmlStr = _    "<PointN xsi:type='typens:PointN' " & _    "xmlns:xsi='http://www.w3.org/2001/XMLSchema-instance' " & _    "xmlns:xs='http://www.w3.org/2001/XMLSchema'> " & _    "    <X>24.365</X> " & _    "    <Y>78.63</Y> " & _    "</PointN>"Set objDom = CreateObject("Msxml2.DOMDocument.3.0")     '// Using MSXML 3.0'/* Load XML */objDom.LoadXML xmlStr'/*' * XPath Query' */        '/* Get X */xPath = "/PointN/X"Debug.Print objDom.SelectSingleNode(xPath).text'/* Get Y */xPath = "/PointN/Y"Debug.Print objDom.SelectSingleNode(xPath).text