How to read this complex .xml in vba? Problem with .SelectNodes()

Question

I have this .xml file:

<P48Cierre xmlns="http://sujetos.esios.ree.es/schemas/2007/03/07/P48Cierre-esios-MP/">
    <IdentificacionMensaje v="p48cierre_20160101"/>
    <VersionMensaje v="1"/>
    <TipoMensaje v="Z66"/>
    <TipoProceso v="Z04"/>
    <TipoClasificacion v="A01"/>
    <IdentificacionRemitente v="10XES-REE------E" codificacion="A01"/>
    <FuncionRemitente v="A04"/>
    <IdentificacionDestinatario v="DESTINATARIO" codificacion="A01"/>
    <FuncionDestinatario v="A08"/>
    <FechaHoraMensaje v="2016-01-02T01:17:30Z"/>
    <Horizonte v="2015-12-31T23:00Z/2016-01-01T23:00Z"/>
    <SeriesTemporales>
        <IdentificacionSeriesTemporales v="STP0"/>
        <TipoNegocio v="Z21"/>
        <UPEntrada v="ESANAG" codificacion="NES"/>
        <UnidadMedida v="MWH"/>
        <Periodo>
            <IntervaloTiempo v="2015-12-31T23:00Z/2016-01-01T23:00Z"/>
            <Resolucion v="PT60M"/>
            <Intervalo>
                <Pos v="1"/>
                <Ctd v="14.6"/>
            </Intervalo>
        </Periodo>
    </SeriesTemporales>
    <SeriesTemporales>
        <IdentificacionSeriesTemporales v="STP1"/>
        <TipoNegocio v="Z21"/>
        <UPEntrada v="ETINEO" codificacion="NES"/>
        <UnidadMedida v="MWH"/>
        <Periodo>
            <IntervaloTiempo v="2015-12-31T23:00Z/2016-01-01T23:00Z"/>
            <Resolucion v="PT60M"/>
                            <Intervalo>
                <Pos v="23"/>
                <Ctd v="41.7"/>
            </Intervalo>
            <Intervalo>
                <Pos v="24"/>
                <Ctd v="41.6"/>
            </Intervalo>
        </Periodo>
    </SeriesTemporales>
    </P48Cierre>

I need an excel output table like this:

UPEntrada	Pos	Ctd
ESANAG	1	14.6
ETINEO	23	41.7
ETINEO	24	41.6

I have tried accesing the nodes following this method found on the web:

Sub XMLParse()
    Dim oXml As New MSXML2.DOMDocument60
    Dim lNodes  As IXMLDOMNodeList, xNode As IXMLDOMNode
    oXml.Load ("C:\archive.xml")
    
    With oXml.DocumentElement
        Set lNodes = .SelectNodes("SeriesTemporales")
    End With
    For Each xNode In lNodes
        With xNode
            Debug.Print .ParentNode.nodeName & "/" & .nodeName & " : " & .Text
        End With
    Next xNode
End Sub

However, xNode is always empty no matter what I put in Set lNodes = .SelectNodes("*") and the compilator never enters in the For part of the code.

Could you help me please? Thanks in advance

EDIT: I have tried the solution of @barrowc and it works perfect:

Sub XMLParse()
    Dim oXml As New MSXML2.DOMDocument60
    Dim lNodes  As IXMLDOMNodeList, xNode As IXMLDOMNode
    oXml.SetProperty "SelectionNamespaces", "xmlns:r='http://sujetos.esios.ree.es/schemas/2007/03/07/P48Cierre-esios-MP/'"
    oXml.async = False
    oXml.Load ("C:archivo.xml")
    
    Set lNodes = oXml.SelectNodes("/r:P48Cierre/r:SeriesTemporales/r:Periodo/r:Intervalo/r:Pos")
    
    For Each xNode In lNodes
        With xNode
            Debug.Print .ParentNode.nodeName & "/" & .nodeName & " : " & .Attributes.Item(0).NodeValue
        End With
    Next xNode
    
End Sub

It looks like you have a malformed XML document. The tag `` on line 37 doesn't have a corresponding opening tag — Glenn G, Oct 29 '21 at 19:05
Sorry, fail preparing the example. That is not the problem, thanks. — H_pwr, Oct 29 '21 at 19:13
It's the default namespace in the document (i.e. the `xmlns=` part) which causes a known issue with MSXML2's implementation of XPath - see [this answer](https://stackoverflow.com/a/21057913/2127508) for how to work around the problem or also see [this Microsoft article](https://learn.microsoft.com/en-us/previous-versions/troubleshoot/msxml/use-xpath-against-default-namespace) — barrowc, Oct 29 '21 at 19:31
*fail preparing the example* is not acceptable. If you copy and paste directly here, you don't have that problem. If you want help with reading your XML, then post your **actual XML** and not something you made up for the questinon. — Ken White, Oct 29 '21 at 23:06
@KenWhite do you prefer that I use my actual XML with more than 3000 entries? I do not understand such an unfriendly comment. Luckily, 99.99 % people here is willing to help and admit silly errors as mine. — H_pwr, Oct 31 '21 at 09:49
No, I prefer that you post valid XML that is truly representative of your problem, that you didn't just type into your post with typos or errors, so that you don't waste our time telling you about what may be causing your problem only to have you say "Oh, my mistake when I typed it here*. Also, the [help/on-topic] says clearly that when asking questions about code that isn't working, you **must** provide a [mre]] that demonstrates the issue, and your XML is part of that [mre].. And there's nothing *unfriendly* about explaining to a relatively new user how the site works. — Ken White, Oct 31 '21 at 14:56
@KenWhite the mistake was corrected days ago, check before commenting. You can say whatever you want, you have been rude and has no offered any help, so thank you and goodbye — H_pwr, Oct 31 '21 at 15:46

score 0 · Accepted Answer · answered Oct 31 '21 at 09:44

Solution aported by @barrowc:

"It's the default namespace in the document (i.e. the xmlns= part) which causes a known issue with MSXML2's implementation of XPath - see this answer for how to work around the problem or also see this Microsoft article" Excel VBA getting specific node from XML https://learn.microsoft.com/en-us/previous-versions/troubleshoot/msxml/use-xpath-against-default-namespace

An example of succesful code:

Sub XMLParse()
    Dim oXml As New MSXML2.DOMDocument60
    Dim lNodes  As IXMLDOMNodeList, xNode As IXMLDOMNode
    oXml.SetProperty "SelectionNamespaces", "xmlns:r='http://sujetos.esios.ree.es/schemas/2007/03/07/P48Cierre-esios-MP/'"
    oXml.async = False
    oXml.Load ("C:archivo.xml")
    
    Set lNodes = oXml.SelectNodes("/r:P48Cierre/r:SeriesTemporales/r:Periodo/r:Intervalo/r:Pos")
    
    For Each xNode In lNodes
        With xNode
            Debug.Print .ParentNode.nodeName & "/" & .nodeName & " : " & .Attributes.Item(0).NodeValue
        End With
    Next xNode
    
End Sub

How to read this complex .xml in vba? Problem with .SelectNodes()

1 Answers1