Need help writing VBA code to automatically add attributes and fields from XMLMap

[V_Malkoti]

I am trying to write code which will help me verify a very large number of XML files. Each of these XML files will have a corresponding XSD which will be provided with it.
So far I was able to write code to create XMLMap, then set XPath (repeatable) of some fields to ranges, and then import the XML to that map.

set oXMLMap = ActiveWorkbook.XmlMaps.Add(strXSDPath, strRootName)
oXMLMap.Name = strMapName
ActiveSheet.Range("A1").Xpath.SetValue oXMLMap, "/myRootElement/myRepeatingElement/myDataElement1", , true
ActiveSheet.Range("B1").Xpath.SetValue oXMLMap, "/myRootElement/myRepeatingElement/myDataElement2", , true
' and so on
oXMLMap.Import URL:=strXMLPath

This works fine with my own test XMLs and I can get the data imported in the spreadsheet.

Now the problem is that the actual XMLs have 50+ data elements and thousands of rows each and there are hundreds of those XMLs. I feel that mapping each data element or attribute in this way (hardcoded) is highly impractical. I've spent past few hours looking for a way to create a mechanism with which mapping elements from any XML with no hardcoding would be possible. But no luck.

so what I'm looking for is

(psuedocode)
For Each element (dataElement or dataAttribute) in the map
    ActiveSheet.cells(1, i).Xpath.SetValue oXMLMap, element.xpath, , true
    i++
next

In Excel UI, this mapping can be done by simply dragging and dropping the root element from XML Source pane. But this action does not get recorded in Macro recorder.

It is possible that somebody else asked this question before and got answer, but I have not been able to find such post on MrExcel or any other forum so far.

Any help is appreciated.

 

[V_Malkoti]

Each of these XML files has its own structure which is described in its own XSD. Writing down all XPaths for mapping will be quite an effort, and prone to mistakes.
This is why I'm want to add logic to program which will identify data elements and attributes, and maps them without user having to input all those XPaths.
I used XMLImport method of workbook object but it does not import data with correct data type - ID are rounded off or truncated because those are longer than 15 digits, and zeros in the left are dropped. Data types of each element are defined in XSD with xs:simpleType and imports correctly when I create XMLMap and then call oXMLMap.Import method.

 

[V_Malkoti]

Bump