Getting XML Node value when Namespace is present using VBA

Question

I want to navigate and get details of my xml document. But it has a namespace. Due to this namespace in Multiblock element, I am getting lists.length as zero.(Please refer vba code below)

Please guide. Thanks in advance for reading. :) My xml document is:

<?xml version="1.0" encoding="UTF-16"?>
<MultiBlock xmlns="x-schema:ConfigFileSchema.xml">
<ErdbVersion>
    <DbVersion>14.0</DbVersion>
    <DbDesc>ERDB Release EXP431.1-49.0</DbDesc>
    <DbGUID>56CFAF87-53A9-4042-8B4F-4CF94868416E</DbGUID>           
<DbLangID>ENU</DbLangID>
</ErdbVersion>
<Block>
    <BlockDef>
        <BlockName>J60AOV1136</BlockName>
        <EntityName>J60AOV1136</EntityName>
        <BlockId>20031267</BlockId>
        <BlockGUID>D11BF0DB-803D-49FC-A594-D234ABD1E156             
</BlockGUID>
        <BlockDesc>Exported on (MM-DD-YY HH:MM) 07-31-              2017  
10:12</BlockDesc>
        <TemplateName>SYSTEM:CONTROLMODULE</TemplateName>               
<ClassName>CONTROLMODULE</ClassName>                        
<BaseTemplateName>SYSTEM:CONTROLMODULE                      
</BaseTemplateName>
        <CreateType> </CreateType>
        <Attribute>1610613248</Attribute>
        <LifeCycleState>Loaded</LifeCycleState>                 
<AssignedTo>STFCEE8A_03</AssignedTo>
        <Container></Container>
    </BlockDef>
    <Parameters>
        <Parameter>
            <ParamName>ALIASOPT</ParamName>
            <ParamValue>OFF</ParamValue>
        </Parameter>
        <Parameter>
            <ParamName>DISCOVORDER</ParamName>                      
<ParamValue>"TPN"</ParamValue>
        </Parameter>
        <Parameter>
            <ParamName>METHODSCOPE</ParamName>                      
<ParamValue>"ALL"</ParamValue>
        </Parameter>
    </Parameters
</Block
</MultiBlock>

When I am trying to get Node details without xmlns="x-schema:ConfigFileSchema.xml" I am able to navigate through nodes and get the values.

But with the same document and xmlns attribute, i am having problem in navigating.

Here is my VBA code:

Sub AOVXML()
Dim XDoc As MSXML2.DOMDocument
Dim firstNamefield As MSXML2.IXMLDOMNodeList
Dim lists As MSXML2.IXMLDOMNodeList
Dim i, j As Integer
'Dim lists As MSXML2.IXMLDOMNode

Set XDoc = CreateObject("MSXML2.DOMDocument")
XDoc.async = False: XDoc.validateOnParse = False
XDoc.Load (ThisWorkbook.Path & "\J60AOV1136.cnf.xml")

'Reading the List Node
Set lists = XDoc.SelectNodes("//MultiBlock/Block")
MsgBox "Length of Lists nodes : " & lists.Length & vbCrLf & _
    "First XML List Node : "


'Getting First Child node under Lists
Set firstNamefield = lists(0).ChildNodes
MsgBox firstNamefield.Length

'Looping through all XML nodes under List node
For i = 0 To firstNamefield.Length - 1
MsgBox firstNamefield(i).XML
Next i

Set XDoc = Nothing
End Sub

There are separate methods in MSXML to perform actions with namespaces (maybe ending with `NS`? It's been a long time since I last used MSXML.). Finding a copy of the MSXML documentation is left as an exercise... — Richard, Aug 20 '17 at 07:36
https://stackoverflow.com/questions/45404020/xpath-syntax-to-select-nodes-with-multiple-attributes-in-the-path/45404432#45404432 — Tim Williams, Aug 20 '17 at 07:47
Hi @TimWilliams, Thanks for answering, I used the link and tried to write for my xml document. but i am not getting values intended. Here is my code: `XDoc.setProperty "SelectionLanguage", "XPath" 'Set the default namespace and give it a prefix (e.g.) "xx" XDoc.setProperty "SelectionNamespaces", _ "xmlns:xx='x-schema:ConfigFileSchema.xml'" 'Reading the List Node Set lists = XDoc.SelectNodes("//xx:MultiBlock/xx:Block")` — Srikanth Chilivery, Aug 20 '17 at 08:14

score 1 · Accepted Answer · 2017-08-20T17:22:10.550

Create a schema file and name it ConfigFileSchema.xml in the same folder as J60AOV1136.cnf.xml

<?xml version="1.0" encoding="utf-8"?>
<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema">

Use the following modified sample:

Option Explicit
Sub AOVXML()
    Dim XDoc As MSXML2.DOMDocument60
    Dim firstNamefield As MSXML2.IXMLDOMNodeList
    Dim lists As MSXML2.IXMLDOMNodeList
    Dim i As Integer
    Dim thePath As String

    Set XDoc = CreateObject("MSXML2.DOMDocument.6.0") 'Must be 6 for schema parsing to pass
    XDoc.async = False
    thePath = ThisWorkbook.Path & "\J60AOV1136.cnf.xml"
'*
'* It is useful to have the reason for a possible error
'* Sample code found here:
'* https://msdn.microsoft.com/en-us/library/aa468547.aspx
'*
    If Not XDoc.Load(thePath) Then ' The document failed to load.
        Dim strErrText As String
        Dim xPE As MSXML2.IXMLDOMParseError
        Set xPE = XDoc.parseError
        With xPE
        strErrText = "Your XML Document failed to load due the following error." & vbCrLf & _
            "Error #: " & .ErrorCode & ": " & xPE.reason & _
            "Line #: " & .Line & vbCrLf & _
            "Line Position: " & .linepos & vbCrLf & _
            "Position In File: " & .filepos & vbCrLf & _
            "Source Text: " & .srcText & vbCrLf & _
            "Document URL: " & .Url
        End With
        MsgBox strErrText
        Exit Sub
    End If
'*
'* Reading the List Node
'* To accept all namespaces, use the form //*[local-name()='<nodename>']
'*
    Set lists = XDoc.SelectNodes("//*[local-name()='Block']") '<- here is the major change
    If lists.Length > 0 Then
        MsgBox "Length of Lists nodes : " & lists.Length & vbCrLf & "First XML List Node : "
'*
'* Getting First Child node under Lists
'*
        Set firstNamefield = lists(0).ChildNodes
        MsgBox firstNamefield.Length
'*
'* Looping through all XML nodes under List node
'*
        For i = 0 To firstNamefield.Length - 1
            MsgBox firstNamefield(i).XML
        Next i
    End If
    Set XDoc = Nothing
End Sub

Note that you need to add a reference to Microsoft XML, 6.0 to use this code.

score 0 · Answer 2 · answered Aug 21 '17 at 05:36

thankyou for helping. I did as you said. Now i am trying to understand what is local-name and how i can access using xpaths (if it is possible). Thankyou very much for sharing your knowledge. :) :) :)

Created a xml schema file using code below:

Sub Create_Schema()
Dim StrMyXml As String, MyMap As XmlMap
Dim StrMySchema As String
' Book.xml is the file created in section one of this topic.
StrMyXml = ThisWorkbook.Path & "\J60AOV1136.cnf.xml"

' Turn off async loading.
Application.DisplayAlerts = False
' Add the string to the XmlMaps collection.
Set MyMap = ThisWorkbook.XmlMaps.Add(StrMyXml)
Application.DisplayAlerts = True

' Create an empty file and output the schema.
StrMySchema = ThisWorkbook.XmlMaps(1).Schemas(1).XML
Open ThisWorkbook.Path & "\ConfigFileSchema.xml" For Output As #1
Print #1, StrMySchema
Close #1
End Sub

Getting XML Node value when Namespace is present using VBA

2 Answers2