Import XML schema with repeated elements, but different attribute names

Question

I have the following schema:

<import>
    <node type="document" action="action">
        <location>Spain:Development</location>
        <title>Abono de factura</title>
        <file>D:\OPENTEXT\12343fewf.pdf</file>
        <category name="Content Server Categories:Non SAP Categories:Common:Migracion_Documentum">
            <attribute name="Autor">ppieroni</attribute>
            <attribute name="ID Documentum">E-0008749312</attribute>
        </category>
    </node>
    <node type="document" action="action">
        <location>Spain:Systems</location>
        <title>Factura pendiente</title>
        <file>D:\OPENTEXT\89443gs.xlsx</file>
        <category name="Content Server Categories:Non SAP Categories:Common:Migracion_Documentum">
            <attribute name="Autor">jcarballeira</attribute>
            <attribute name="ID Documentum">I-0001245366</attribute>
        </category>
    </node>
</import>

When I import an XML file following this schema, Excel lays out the data as follows:

https://i.ibb.co/8xj55jM/XML-mapping.jpg

It seems that Excel cannot map more than one attribute having repeated elements with different name tags, since I have to choose between mapping "Autor" or "ID Documentum".

I would need to be able to map more than one repeated row with different attributes and values in it.

How could I achieve this?

Regards

Answer 1

You could use a XML parser.

Required reference via vbe > tools> references to Micorsoft XML library (v.6 for me)

Option Explicit
Public Sub test()
    Dim xmlDoc As Object

    Application.ScreenUpdating = False
    Set xmlDoc = CreateObject("MSXML2.DOMDocument")
    With xmlDoc
        .validateOnParse = True
        .setProperty "SelectionLanguage", "XPath"
        .async = False
        If Not .Load("C:\Users\HarrisQ\Desktop\test.xml") Then
            Err.Raise .parseError.ErrorCode, , .parseError.reason
        End If
    End With
    Dim node As IXMLDOMElement, childNode As IXMLDOMElement, nextChildNode As IXMLDOMElement, r As Long, c As Long, attrib As Object
    r = 1
    For Each node In xmlDoc.SelectNodes("//node")
        r = r + 1
        With ActiveSheet
            .Cells(r, 1) = node.getAttribute("type")
            .Cells(r, 2) = node.getAttribute("action") '<== you can hardcode create here as varies from value of attribute
            .Cells(r, 3) = node.SelectSingleNode("location").Text
            .Cells(r, 4) = node.SelectSingleNode("title").Text
            Set childNode = node.SelectSingleNode("category")
            .Cells(r, 5) = childNode.getAttribute("name")
            c = 6
            For Each nextChildNode In childNode.SelectNodes("attribute")
                .Cells(r, c) = nextChildNode.getAttribute("name")
                c = c + 1
            Next
        End With
    Next
    Application.ScreenUpdating = True
End Sub