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 </Intervalo> on line 37 doesn't have a corresponding opening tag — Glenn G
– Glenn G, Commented Oct 29, 2021 at 19:05
Sorry, fail preparing the example. That is not the problem, thanks. — H_pwr
– H_pwr, Commented Oct 29, 2021 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 for how to work around the problem or also see this Microsoft article — barrowc
– barrowc, Commented Oct 29, 2021 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
– Ken White, Commented Oct 29, 2021 at 23:06

H_pwr · Accepted Answer · 2021-10-31 09:44:34Z

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

Collectives™ on Stack Overflow

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

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related