CODEWITHSUNDEEP
excelxmlvbaparsingdomdocument
Golf
Golf

Reputation: 23

Having trouble with XML phase on VBA Excel

I need to parse a XML file having the structure as follows: (I can't show the data as it is confidential)

<?xml version="1.0" encoding="UTF-8"?>
<GACDWBulkLoadInterface xsi:schemaLocation="http://www.example.org/GACDWSchema GACDWSchema.xsd" xmlns="http://www.example.org/GACDWSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
       <BLConfiguration>
          <BLProperties>
             <BLProperty>
                <key>isEmpty</key>
                <value xmlns:xsd="http://www.w3.org/2001/XMLSchema" xsi:type="xsd:boolean">true</value>
             </BLProperty>
          </BLProperties>
          <version>api-ag-1.1.68-20210315-43</version>
       </BLConfiguration>
       <Customer>
          <CustomerID></CustomerID>
          <Customer_Name></Customer_Name>
          <ISOCountryCode></ISOCountryCode>
          <customerType></customerType>
          <dataSourceProvider></dataSourceProvider>
          <function></function>
          <TopLevelCI>
             <Contact Action="Create">
                <Id>4</Id>
                <artifactType></artifactType>
                <contactType></contactType>
                <email></email>
                <firstName></firstName>
                <lastName></lastName>
                <userID></userID>
                <ChildCI/>
             </Contact>
             <PhysicalComputerSystem Action="Create">
                <Id>2</Id>
                <artifactType></artifactType>
                <assetLifeCycleState></assetLifeCycleState>
                <category></category>
                <ciLifeCycleState></ciLifeCycleState>
                <custname></custname>
                <description></description>
                <discontinuedDate></discontinuedDate>
                <machineType></machineType>
                <managed></managed>
                <manufacturer></manufacturer>
                <model></model>
                <serialNumber></serialNumber>
                <virtualFlag></virtualFlag>
                <ChildCI>
                   <PhysicalComputerSystemContact Action="Create">
                      <contactUID></contactUID>
                   </PhysicalComputerSystemContact>
                   <PhysicalComputerSystemLocation Action="Create">
                      <buildingID></buildingID>
                      <buildingName></buildingName>
                      <city></city>
                      <countryName></countryName>
                      <isoCountryCode></isoCountryCode>
                      <postalCode></postalCode>
                      <siteName></siteName>
                      <stateProvince></stateProvince>
                      <streetAddress></streetAddress>
                   </PhysicalComputerSystemLocation>
                </ChildCI>
             </PhysicalComputerSystem>
             <OperatingSystem Action="Create">
                <Id>1</Id>
                <aliasHostname></aliasHostname>
                <artifactType></artifactType>
                <assetLifeCycleState></assetLifeCycleState>
                <backupContr></backupContr>
                <barSaId></barSaId>
                <cacfCustomGroup></cacfCustomGroup>
                <category></category>
                <ciLifeCycleState></ciLifeCycleState>
                <classification></classification>
                <cpuCount></cpuCount>
                <description></description>
                <discontinuedDate></discontinuedDate>
                <fqhn></fqhn>
                <hostsystemID></hostsystemID>
                <hostsystemType></hostsystemType>
                <installationDate></installationDate>
                <ipAddress></ipAddress>
                <majorBusProc></majorBusProc>
                <managed></managed>
                <memory></memory>
                <memoryUnit></memoryUnit>
                <osModLevel></osModLevel>
                <osName></osName>
                <osBuildNumber></osBuildNumber>
                <osProvider></osProvider>
                <osRelease></osRelease>
                <osVersion></osVersion>
                <prodDate></prodDate>
                <purgeDate></purgeDate>
                <purpose></purpose>
                <securityClass></securityClass>
                <serverType></serverType>
                <supportEnddate></supportEnddate>
                <useAliasHostname></useAliasHostname>
                <virtualFlag></virtualFlag>
                <ChildCI>
                   <OperatingSystemContact Action="Create">
                      <contactUID></contactUID>
                   </OperatingSystemContact>
                   <OperatingSystemEnvironmentProfile Action="Create">
                      <authFlag></authFlag>
                      <authExceptDate></authExceptDate>
                      <authExceptReasonCode></authExceptReasonCode>
                      <authExceptText></authExceptText>
                      <disasterRecSys></disasterRecSys>
                      <domain></domain>
                      <extUsersMax></extUsersMax>
                      <hcAutoInterv></hcAutoInterv>
                      <hcExceptDate></hcExceptDate>
                      <hcExceptReasonCode></hcExceptReasonCode>
                      <hcExceptText></hcExceptText>
                      <hcUnit></hcUnit>
                      <healthchAutoFlag></healthchAutoFlag>
                      <highestClientDataClassification></highestClientDataClassification>
                      <highestDataClassification></highestDataClassification>
                      <highestIbmDataClassification></highestIbmDataClassification>
                      <ibmCbnInterval></ibmCbnInterval>
                      <ibmCbnRequired></ibmCbnRequired>
                      <ibmCbnType></ibmCbnType>
                      <ibmCbnUnit></ibmCbnUnit>
                      <ibmPrivIDUnit></ibmPrivIDUnit>
                      <ibmPrivType></ibmPrivType>
                      <ibmQevFlag></ibmQevFlag>
                      <ibmQevInterval></ibmQevInterval>
                      <ibmQevUnit></ibmQevUnit>
                      <ibmUsersMax></ibmUsersMax>
                      <internetAccFlag></internetAccFlag>
                      <personalData></personalData>
                      <privIDFlag></privIDFlag>
                      <privIDInterv></privIDInterv>
                      <regulatoryRequirements></regulatoryRequirements>
                      <remediationtimeHi></remediationtimeHi>
                      <remediationtimeLo></remediationtimeLo>
                      <remediationtimeMd></remediationtimeMd>
                      <rerFlag></rerFlag>
                      <rerInterv></rerInterv>
                      <rerType></rerType>
                      <rerUnit></rerUnit>
                      <scExceptDate></scExceptDate>
                      <scExceptReasonCode></scExceptReasonCode>
                      <scExceptText></scExceptText>
                      <secExemptionDesc></secExemptionDesc>
                      <secExemptionFlag></secExemptionFlag>
                      <securityPackage></securityPackage>
                      <securityPolicyCategory></securityPolicyCategory>
                      <securityPolicyOther></securityPolicyOther>
                      <securityPolicyType></securityPolicyType>
                      <serverPurpose></serverPurpose>
                      <sharedIDFlag></sharedIDFlag>
                      <shrdExceptDate></shrdExceptDate>
                      <shrdExceptReasonCode></shrdExceptReasonCode>
                      <shrdExceptText></shrdExceptText>
                      <vitalBusProcessYN></vitalBusProcessYN>
                   </OperatingSystemEnvironmentProfile>
                   <OperatingSystemIpAddress Action="Create">
                      <dotnotation></dotnotation>
                      <hostName></hostName>
                      <isPrimaryIpHost></isPrimaryIpHost>
                      <name></name>
                      <netMask></netMask>
                      <vsExceptDate></vsExceptDate>
                      <vsExceptReasonCode></vsExceptReasonCode>
                      <vsExceptText></vsExceptText>
                      <vulScanUnit></vulScanUnit>
                      <vulscanAutoFlag></vulscanAutoFlag>
                      <vulscanAutoInterval></vulscanAutoInterval>
                   </OperatingSystemIpAddress>
                </ChildCI>
             </OperatingSystem>
             <Subsystem Action="Create">
                <Id>3</Id>
                <applSIDataType></applSIDataType>
                <artifactType></artifactType>
                <assetLifeCycleState></assetLifeCycleState>
                <ciLifeCycleState></ciLifeCycleState>
                <controlProductName></controlProductName>
                <controlProductRelease></controlProductRelease>
                <controlProductType></controlProductType>
                <controlProductVendor></controlProductVendor>
                <controlProductVersion></controlProductVersion>
                <description></description>
                <discontinuedDate></discontinuedDate>
                <fqhn></fqhn>
                <managed></managed>
                <name></name>
                <parentUID></parentUID>
                <piFlag></piFlag>
                <productModLevel></productModLevel>
                <productName></productName>
                <productPatchLevel></productPatchLevel>
                <productRelease></productRelease>
                <productVersion></productVersion>
                <purgeDate></purgeDate>
                <scanTime></scanTime>
                <vendorName></vendorName>
                <ChildCI>
                   <SubsystemContact Action="Create">
                      <contactUID></contactUID>
                   </SubsystemContact>
                   <SubsystemEnvironmentProfile Action="Create">
                      <authFlag></authFlag>
                      <authExceptDate></authExceptDate>
                      <authExceptReasonCode></authExceptReasonCode>
                      <authExceptText></authExceptText>
                      <disasterRecSys></disasterRecSys>
                      <hcAutoInterv></hcAutoInterv>
                      <hcExceptDate></hcExceptDate>
                      <hcExceptReasonCode></hcExceptReasonCode>
                      <hcExceptText></hcExceptText>
                      <hcUnit></hcUnit>
                      <healthchAutoFlag></healthchAutoFlag>
                      <ibmCbnInterval></ibmCbnInterval>
                      <ibmCbnRequired></ibmCbnRequired>
                      <ibmCbnType></ibmCbnType>
                      <ibmCbnUnit></ibmCbnUnit>
                      <ibmPrivIDUnit></ibmPrivIDUnit>
                      <ibmPrivType></ibmPrivType>
                      <ibmQevFlag></ibmQevFlag>
                      <ibmQevInterval></ibmQevInterval>
                      <ibmQevUnit></ibmQevUnit>
                      <privIDFlag></privIDFlag>
                      <privIDInterv></privIDInterv>
                      <rerFlag></rerFlag>
                      <rerInterv></rerInterv>
                      <rerType></rerType>
                      <rerUnit></rerUnit>
                      <scExceptDate></scExceptDate>
                      <scExceptReasonCode></scExceptReasonCode>
                      <scExceptText></scExceptText>
                      <sharedIDFlag></sharedIDFlag>
                      <shrdExceptDate></shrdExceptDate>
                      <shrdExceptReasonCode></shrdExceptReasonCode>
                      <shrdExceptText></shrdExceptText>
                      <vitalBusProcessYN></vitalBusProcessYN>
                   </SubsystemEnvironmentProfile>
                </ChildCI>
             </Subsystem>
          </TopLevelCI>
          <Relationship>
             <RelationshipName>Hosted_by</RelationshipName>
             <sourceCIType>OperatingSystem</sourceCIType>
             <targetCIType>OperatingSystem</targetCIType>
          </Relationship>
          <Relationship>
             <RelationshipName>Runs_on</RelationshipName>
             <sourceCIType>Subsystem</sourceCIType>
             <targetCIType>OperatingSystem</targetCIType>
          </Relationship>
          <Relationship>
             <RelationshipName>Runs_on_hw</RelationshipName>
             <sourceCIType>OperatingSystem</sourceCIType>
             <targetCIType>PhysicalComputerSystem</targetCIType>
          </Relationship>
       </Customer>
</GACDWBulkLoadInterface>

I have researched and come up with my VBA code here

Sub ReadXML()
    Dim oXML As MSXML2.DOMDocument60
    Dim vaFile As Variant
    
    Set oXML = New MSXML2.DOMDocument60
    
    'Open Browse file dialog
    vaFile = Application.GetOpenFilename("XML Files (*.xml), *.xml", _
                  Title:="Select XML files", MultiSelect:=False)
    oXML.validateOnParse = True
    oXML.setProperty "SelectionLanguage", "XPath"   ' necessary in version 3.0, possibly redundant here
    oXML.async = False
        
        
         If Not oXML.Load(vaFile) Then 'Load XML has gone bad
            Dim xPE        As Object    ' Set xPE = CreateObject("MSXML2.IXMLDOMParseError")
            Dim strErrText As String
            Set xPE = oXML.parseError
            With xPE
               strErrText = "Load error " & .ErrorCode & " xml file " & vbCrLf & _
               Replace(.URL, "file:///", "") & vbCrLf & vbCrLf & _
              xPE.reason & _
              "Source Text: " & .srcText & vbCrLf & vbCrLf & _
              "Line No.:    " & .Line & vbCrLf & _
              "Line Pos.: " & .linepos & vbCrLf & _
              "File Pos.:  " & .filepos & vbCrLf & vbCrLf
            End With
            MsgBox strErrText, vbExclamation
            Set xPE = Nothing
            Exit Sub
        End If
        
    Debug.Print "|" & oXML.XML & "|"
    
    Dim nodeList  As MSXML2.IXMLDOMNodeList, iNode As MSXML2.IXMLDOMNode
    Dim Searched As String
    Searched = "/*/*"
    
    Set nodeList = oXML.SelectNodes(Searched)
    
    'Set Queries = oXML.DocumentElement.SelectNodes(Searched)
    
    For Each iNode In nodeList
         'Debug.Print "<" & iNode.BaseName & ">"
         Debug.Print "<" & iNode.nodeName & ">"
         
    Next

However, My problem is no matter what I come up with Xpath queries. None of them work except \* or \. I can't specific any path. Both .SelectSingleNode or .SelectNodes alway return length =0. Is there any problem with my code or my XML file or Xpath syntax?

Upvotes: 1

Views: 377

Answers (1)

T.M.
T.M.

Reputation: 9938

As mentioned in comments your xml document has namespace definitions in its DocumentElement <GACDWBulkLoadInterface> (xmlns stands for xml name space). Furthermore "it contains a default namespace so any attempted parsing on named nodes must map to this namespace URI otherwise returns nothing."

To allow eventual analysis it's necessary to include a user defined prefix (e.g. :s) into explicit namespace settings, which can be used in later XPath expressions:

    Dim oXML   As MSXML2.DOMDocument60
    Set oXML = New MSXML2.DOMDocument60
    oXML.validateOnParse = True

    Dim XMLNamespaces As String
    XMLNamespaces = "xmlns:s='http://www.example.org/GACDWSchema'"
    oXML.SetProperty "SelectionNamespaces", XMLNamespaces

Eventually you can define any XPath expression, e.g. the childnodes of Customer[1]:

    Dim nodeList As MSXML2.IXMLDOMNodeList, iNode As MSXML2.IXMLDOMNode
    Dim Searched As String
    Searched = "//s:Customer/s:*"

    Set nodeList = oXML.SelectNodes(Searched)

Related links

Upvotes: 2

Related Questions