specify local relative path to namespace for XML

Question

I'm having issues with referring to the schema file that is used in the XML files that are loaded. Schema file is located in folder="C:\Users\zander\Desktop\20211112094529\Recipe\RECIPEs\Data\Sequences\" and because the XML files are in multiple subfolders, I don't want to copy the schema file to each subfolder.

All the examples that I find are with http... locations, can anyone help me with referring to a local folder?

<?xml version="1.0"?>
<SEQUENCE Format="2.0" Name="hardware\bbx" xmlns="x-schema:sequence_schema.xml">
  <PASSPORT Frozen="No" Type="Production" AccessModifyGroups="All" AccessDisplayGroups="All" 
   Version="1.0" ReviseTime="28/11/2018 11:36:13.384" Revisor="Wesley" CreateTime="11/01/2012 
   17:38:14.765" Creator="Wes" Description="">
    <CUSTOM>
      <CUSTOM_PARAM Name="SendLotEndSignal" Value="0"/>
    </CUSTOM>
  </PASSPORT>
  <STEPS>
    <STEP SeqEndCleanRecipe="NO_RECIPE" Recipe="STANDARD\205C" Chambers="B2B35" SectionName="Steps">
      <CUSTOM>
        <CUSTOM_PARAM Name="Type" Value="PAAP"/>
        <CUSTOM_PARAM Name="Block" Value="B2"/>
      </CUSTOM>
    </STEP>
    <STEP SeqEndCleanRecipe="NO_RECIPE" Recipe="STANDARD\110C" Chambers="B2B14" SectionName="Steps">
      <CUSTOM>
        <CUSTOM_PARAM Name="Type" Value="PAHP"/>
        <CUSTOM_PARAM Name="Block" Value="B2"/>
      </CUSTOM>
    </STEP>
  </STEPS>
</SEQUENCE>

This is the code that I'm using (updated to full new code). The code works perfectly when I copy the schema file to the same folder as the XML, it works but I prefer not to do this because I have a lot of subfolders:

Option Explicit

'Sub ReadAllXML(Ffolder As String)
Sub ReadAllXML()
Dim Ffolder As String

    Dim FilePath, XMLFileName, RecipeID As String
    Dim main_folder As String
    Dim oXMLFile, Recipe As Object
    Dim Col, NumberOfElements, LastRow As Long
    Dim n As Object
    Dim RecipeName, ChamberName, UnitKind, BlockName As String
    Dim strtxt, Fromtxt, FromPos, PathWithoutSequences, FirstFolder As String
    Dim wks As Worksheet
    
    'main folder is the folder where the schema file is stored
    main_folder = "C:\Users\zander\Desktop\20211112094529\Recipe\RECIPEs\Data\Sequences\"
    'Ffolder are different folders where all the XML files are stored
    Ffolder = "C:\Users\zander\Desktop\20211112094529\Recipe\RECIPEs\Data\Sequences\Subfolder1\subfolder2\"
    
    Set oXMLFile = CreateObject("Microsoft.XMLDOM")
    Set wks = ThisWorkbook.Worksheets("Sheet1")
    
    XMLFileName = Dir(Ffolder & "*.xml")
    
        If XMLFileName <> "" Then
            
            FilePath = Ffolder & XMLFileName
           ' Namespace = "xmlns=x-schema:sequence_schema.xml" "xsi:schemaLocation=""file:///C:\Users\zander\Desktop\20211112094529\Recipe\RECIPEs\Data\Sequences\sequence_schema.xml""
           
            oXMLFile.Load FilePath
            'oXMLFile.SetProperty "SelectionLanguage", "XPath"
            'oXMLFile.SetProperty "SelectionNamespaces", "xmlns=x-schema:sequence_schema.xml"
            
            'oXMLFile.resolveExternals = False
            'oXMLFile.validateOnParse = False
            
            Debug.Print oXMLFile.parseError.ErrorCode & "   " & oXMLFile.parseError.reason & "    " & oXMLFile.parseError.Line
                          
            'Set elements = oXMLFile.getElementsByTagName("STEP")
            'RecipeName = oXMLFile.SelectSingleNode("//STEP").Attributes.getNamedItem("Recipe").Text
            'BlockName = oXMLFile.SelectSingleNode("//STEP/CUSTOM").Attributes.getNamedItem("CUSTOM_PARAM").Text

            Col = 10
            NumberOfElements = oXMLFile.getElementsByTagName("STEP").Length
        
            With wks
                LastRow = wks.Cells(wks.Rows.Count, "C").End(xlUp).Row + 1
                    strtxt = FilePath
                    Fromtxt = "Sequences"
                    'Totxt = "test"
                    FromPos = InStr(strtxt, Fromtxt)
                    'ToPos = InStr(strtxt, Totxt)
                    'extract the text string between the two words
                    'ExtractStr = Mid(strtxt, FromPos + Len(Fromtxt), ToPos - FromPos - Len(Fromtxt))
         
                    PathWithoutSequences = Right(strtxt, Len(strtxt) - FromPos - 9)
                    FirstFolder = Left(PathWithoutSequences, Len(PathWithoutSequences) - Len(XMLFileName) - 1)
                    
                        .Cells(LastRow, 3) = FirstFolder
                        .Cells(LastRow, 8) = XMLFileName
                
                
                For Each n In oXMLFile.SelectNodes("//STEP")
                        RecipeName = n.Attributes.getNamedItem("Recipe").Text
                        ChamberName = n.Attributes.getNamedItem("Chambers").Text
                        UnitKind = n.ChildNodes(0).ChildNodes(0).Attributes.getNamedItem("Value").Text
                        BlockName = n.ChildNodes(0).ChildNodes(1).Attributes.getNamedItem("Value").Text
                           
                        .Cells(LastRow, Col) = RecipeName
                        .Cells(LastRow, Col + 1) = ChamberName
                        .Cells(LastRow, Col + 2) = UnitKind
                        .Cells(LastRow, Col + 3) = BlockName
                        Col = Col + 4
                 Next
             End With
                            
        End If

End Sub

What error do you get? Is the XML parser complaining about a missing "sequence_schema.xml" file? Do you have it in the same folder as the XML? You might try oXMLFile.resolveExternals = false oXMLFile.validateOnParse = false. — William Walseth, Nov 13 '21 at 16:14
Yes, the sequence_schema.xml file is not in the same folder as the XML itself so it only works if I copy the schema file to the same folder but this I want to avoid. I tried to add the 2 additional comments but it doesn`t work. — Wesley, Nov 13 '21 at 16:24
What does `Debug.Print XMLFileNameOVL` return in Immediate window, after `Dir`? — FaneDuru, Nov 13 '21 at 16:35
it returns the XML file name, combined with the folder path it can open it. I use the `Dir` because in the end it will loop through all XML files in the folder — Wesley, Nov 14 '21 at 09:50

score 1 · Answer 1 · answered Nov 13 '21 at 17:57

1

There are several key concepts in play here:

xmlns="x-schema:sequence_schema.xml" is not the way to hint to the location of an XSD. See how to use schemaLocation and supporting material described in the following Q/A:
Your XPath must account for the default namespace (currently xmlns="x-schema:sequence_schema.xml"): See the VBA entry in this Q/A that explains how to specify XML namespaces in XPath expressions and their hosting evaluation libraries/tools:
- How does XPath deal with XML namespaces?

answered Nov 13 '21 at 17:57

kjhughes

106,133
27
181
240

hi kjhughes, thank you for the links. Unfortunately I cannot manage to get it working. When I change the `SelectionLanguage` to `XPath` my nodes become unreadable as well. Can you help me to put the reference to the local schema file? The full path of the schema file is: `C:\Users\zander\Desktop\20211112094529\Recipe\RECIPEs\Data\Sequences\sequence_schema.xml` I`ve also updated my first post with the full code that I have now for the XML read – Wesley Nov 14 '21 at 18:26
@Wesley: Sorry, no, there were many moving parts to begin with, and I see no evidence of their reduction or of your application of the guidance I've already provided. (I didn't provide random links; those references and my introducing guidance address conceptual shortcomings keeping you from achieving your desired results.) It's your responsibility to provide a ***minimal*** [mcve] of a *single* issue per question, but with your latest expansion of the code, this is going in the wrong direction. – kjhughes Nov 14 '21 at 23:29
hy kjhughes, I decides to make the workaround with adding a module that copies the schema file to each folder. It`s probably not the best approach but it's working now, thanks for the assistance – Wesley Nov 15 '21 at 06:38
To solve local schema access problems, follow the method described in my answer to [How to reference a local XML Schema file correctly?](https://stackoverflow.com/q/19253402/290085) – kjhughes Nov 15 '21 at 13:48

specify local relative path to namespace for XML

1 Answers1