CODEWITHSUNDEEP
xmlexcelvbaxpath
toop
toop

Reputation: 11294

any open-source tool or excel macro to extract a list of xpaths from an xml?

Basically I want to be able to specify an xml or 2 like so (would be great if you could select a folder and it would grab all the xml files from there):

Xml 1:

<Client>
              <LastName>Bill</LastName>
              <FirstName>Gates</FirstName>
              <MiddleName/>
              <Suffix/>
              <DateOfBirth>30-May-1968</DateOfBirth>
              <PlaceOfBirth/>
              <SSN>n/a</SSN>
              <Gender>Male</Gender>
              <District>
                <City>SHELTON</City>
                <Mayor>wong</Mayor>
              </District>
              <State>WA</State>
              <Zip>96484</Zip>
</Client>

Xml 2:

<Client>
              <LastName>Warron</LastName>
              <FirstName>Buffet</FirstName>
              <MiddleName>P</MiddleName>
              <Suffix/>
              <DateOfBirth>12-Aug-1957</DateOfBirth>
              <PlaceOfBirth>Mississippi</PlaceOfBirth>
              <SSN>n/a</SSN>
              <Gender>Male</Gender>
              <City>Missi</City>
              <State>KS</State>
              <Account>
                <Type>
                <Name>Cash</Name>
                <Currency>USD</Currency>
                <Country>USA</Country>
                </Type>
              </Account>
              <Zip>66096</Zip>
</Client>

Then put a list of xpaths in column A of an excel sheet (ie. 'Xpaths') such as:

/Client/DateOfBirth
/Client/Account/Type/Name
/Client/Zip
/Client/District/City

Desired behavior: Receive results in a table of a new excel sheet (ie. 'Results') with columns such as:

/Client/DateOfBirth  /Client/Account/Type/Name   /Client/Zip   /Client/District/City

----------------     ------------------------    ------------   --------------------
30-May-1968                                      96484              SHELTON
12-Aug-1957           Cash                       66096

Wouldn't mind if this was done using excel vba macro.

UPDATE - debugging Tim's answer: enter image description here

Upvotes: 0

Views: 2403

Answers (3)

Tim Williams
Tim Williams

Reputation: 166126

This worked for me using your examples (with the xml DTD added at the top)...

Sub Tester()
    ProcessFiles ThisWorkbook.Path, Sheet1.Range("A1:D1")
End Sub

Sub ProcessFiles(FolderPath As String, XPathRange As Range)
    Dim oXML As MSXML2.DOMDocument
    Dim oNode As MSXML2.IXMLDOMNode
    Dim fName As String
    Dim c As Range
    Dim x As Integer
    Dim rv

    x = 1
    fName = Dir(FolderPath & "\*.xml")
    Do While fName <> ""
        Set oXML = New MSXML2.DOMDocument
        oXML.Load FolderPath & "\" & fName

        For Each c In XPathRange.Cells
            rv = ""
            Set oNode = oXML.SelectSingleNode(c.Value)
            If Not oNode Is Nothing Then
                rv = oNode.nodeTypedValue
            End If
            c.Offset(x, 0).Value = rv
        Next c

        x = x + 1
        fName = Dir()
    Loop

End Sub

Upvotes: 4

Bruno Leite
Bruno Leite

Reputation: 1477

You can use this http://msdn.microsoft.com/en-us/library/ms675260(VS.85).aspx

To transfer xml to column use

            If adoRS.Fields(ndx).Type = adChapter Then
                'you have a new column
                Set adoChildRS = adoRS.Fields(ndx).Value
                WalkHier iLevel, adoChildRS
            Else
                'put value in column
                Debug.Print iLevel & ": adoRS.Fields(" & ndx & _
                   ") = " & adoRS.Fields(ndx).Name & " = " & _
                   adoRS.Fields(ndx).Value
            End If

Upvotes: 1

Filburt
Filburt

Reputation: 18061

You could simply add a Xml Map to your Workbook and pull the desired elements to your sheet:

  1. Open your Xml source file wiith Excel

  2. Select "Use the Xml Source task pane" option and click "OK"

  3. Accept that Excel will create a schema for you

  4. Drag the desired elements from the Xml Source pane to columns

  5. Right-click on any column and select "Xml" -> "Import"

  6. Open the desired Xml file.

Upvotes: 2

Related Questions