Get XPaths from workbook – update

In Get XPath objects from Excel workbook I described a method to retrieve all mapped XPaths from a workbook. In this post I describe another method, using the Worksheet.XmlMapQuery function. It’s simpler, and faster.

The Worksheet.XmlMapQuery method returns a range of cells mapped to a particular XPath. So, if the element with XPath /Catalog/Book/Title is mapped to a ListColumn on Sheet1 we can find the range of this ListColumn with the command

set rTitles = Sheet1.XmlMapQuery("/Catalog/Book/Title")

Then rTitles.Address will return “$C$3:$C$15”, for example. And obviously, rTitles.XPath will return “/Catalog/Book/Title”.
When I wrote Get XPath objects from Excel workbook a few months ago I was convinced that the XmlMapQuery command would be of no use to retrieve all XPath objects. You would need to know all individual XPaths, and unfortunately they are not easy to retrieve from the schema. However, I recently discovered that we only need the root to obtain all addresses of the mapped ranges! The line

Debug.Print Sheet1.XmlMapQuery("/Catalog").Address

will for example return “$B$8:$B$26,$C$8:$C$26,$D$8:$D$26,$D$4”: the ranges of all mapped elements.
Since the Rootname is available as a property of the XmlMap object, it is not difficult to get a list of all mapped ranges on a sheet and their XPaths:

    Dim r As Excel.Range, rXPaths As Excel.Range
    With ThisWorkbook.XmlMaps(1)
        Set rXPaths = S01.XmlMapQuery("/" & .RootElementName)
        For Each r In rXPaths.Areas
            Debug.Print r.Address; Tab(20); r.XPath
        Next
    End With

Note that – although the XmlMap is defined on Workbook level – XmlMapQuery is a worksheet function. So, in case you have mapped your elements to ranges in more than one sheet you must loop through the sheets as well:

    Dim ws As Excel.Worksheet
    Dim r As Excel.Range
    With ThisWorkbook.XmlMaps(1)
        Debug.Print .Name; Tab(20); .RootElementName; Tab(40); .RootElementNamespace
        For Each ws In ThisWorkbook.Worksheets
            For Each r In ws.XmlMapQuery("/" & .RootElementName).Areas
                Debug.Print ws.Name; Tab(20); r.Address; Tab(40); r.XPath
            Next
        Next
    End With

An example of a list produced by this procedure:

LeagueMap          League              urn:pixcels:nl
Teams              $B$8:$B$26          /League/Teams/Id
Teams              $C$8:$C$26          /League/Teams/TeamCode
Teams              $D$8:$D$26          /League/Teams/TeamName
Teams              $D$4                /League/Season
Matches            $B$4:$B$310         /League/Matches/Round
Matches            $E$4:$E$310         /League/Matches/Home
Matches            $F$4:$F$310         /League/Matches/Away
Matches            $G$4:$G$310         /League/Matches/For
Matches            $H$4:$H$310         /League/Matches/Against

Warning

Please note that function XmlMapQuery is not perfect. In the above example: when table Teams has one row (listrows.count = 1) then XmlMapQuery(“/League/Teams/Id”) will return $B$8:$B$9. That’s fine. However, when the table is empty (listrows.count = 0) then XmlMapQuery(“/League/Teams/Id”) will also return $B$8:$B$9 (expected: $B$8).
Conclusion: to determine IF an XPath is mapped you should use XmlMapQuery. To determine the mapped data range you use XmlDataQuery. Note that XmlDataQuery returns Nothing when the table is empty AND the XPath is not mapped at all, so you really need XmlMapQuery to determine IF an XPath is mapped.

I hope this pixcel helps. Excel!