read XML file that has < /> with Excel VBA-CodePudding

I have an XML file that I need to read with EXCEL VBA. I am using ChildNodes to search the XML file to find a value within the child nodes. My problem when I try to read, for example

<strReport>&lt;NewDataSet&gt; ,

It does not show the other ChildNodes like

&lt; BookData&gt;

etc., it only shows the whole text.

What I understand is that this.

<strReport>&lt;NewDataSet&gt;

consider as text. So, my question is how to search to find a child node within it.

Thank you,

<Project xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
  <Products>
    <Product>
      <ExtensionData />
      <Authors>
        <Author>
          <ID>20260166</ID>
          <Name>6</Name>
          <X>-7.25</X>
          <y>-7.25</y>
        </Author>
      </Authors>
      <Books>
        <Book>
          <ExtensionData />
          <ID>65130346</ID>
            <strReport>&lt;NewDataSet&gt;
  &lt;BookData&gt;
    &lt;BookType&gt;top&lt;/BookType&gt;
    &lt;BookX&gt;0.00&lt;/BookX&gt;
    &lt;BookY&gt;22.00&lt;/BookY&gt;
    &lt;BookWidth&gt;16.94&lt;/BookWidth&gt;
    &lt;BookHeight&gt;22.00&lt;/BookHeight&gt;
    &lt;BookThick&gt;5.50&lt;/BookThick&gt;
  &lt;/BookData&gt;
  &lt;Price&gt;
    &lt;PriceType&gt;surface Price&lt;/PriceType&gt;
    &lt;XDim&gt;0.00&lt;/XDim&gt;
    &lt;YDim&gt;22.00&lt;/YDim&gt;
    &lt;Width&gt;16.94&lt;/Width&gt;
    &lt;Ecc /&gt;
    &lt;Sales&gt;W 20.00   E  41.25 psf&lt;/Sales&gt;
  &lt;/Price&gt;
&lt;/NewDataSet&gt;</strReport>
        </Book>
      </Books>
    </Product>  
  </Products>
</Project>

This the VBA code that I am using

Set XMLDOC = New MSXML2.DOMDocument60
    
    'Load & Wait till complete XML Data is loaded
    XMLDOC.async = False
    XMLDOC.validateOnParse = False
    XMLDOC.Load (xmlFileName)
    

    'XML Loaded. Now Read Elements One by One into XML DOM Objects
    Set xmlRoot = XMLDOC.DocumentElement
    Set xmlNodes = xmlRoot.FirstChild

 

    For Each xmlNodes In xmlRoot.ChildNodes
        If InStr(xmlNodes.XML, "<Products>") <> 0 Then

            For Each xmlNodesNext In xmlNodes.ChildNodes

                If InStr(xmlNodesNext.XML, "<Product>") = 1 Then


                    For Each xmlNodesNextNext In xmlNodesNext.ChildNodes

the rest of the code

CodePudding user response：

Here's an example of the type of thing you need to do:

Sub Tester()

    Dim doc As MSXML2.DOMDocument60, els As Object, el As Object
    Dim docReport As MSXML2.DOMDocument60
    
    Set doc = New MSXML2.DOMDocument60
    
    doc.LoadXML ActiveSheet.Range("A1").Text 'loading from a worksheet for testing
    'select the nodes with the XML content to be extracted
    Set els = doc.SelectNodes("//Project/Products/Product/Books/Book/strReport")
    Debug.Print els.Length '>> 1
    
    For Each el In els
        Set docReport = New MSXML2.DOMDocument60
        docReport.LoadXML el.nodeTypedValue  'load the XML from `strReport`
        
        Debug.Print docReport.SelectSingleNode("//NewDataSet/BookData/BookWidth").nodeTypedValue
        Debug.Print docReport.SelectSingleNode("//NewDataSet/Price/Sales").nodeTypedValue
    Next el
    
End Sub