VBA code to validate XML using XSD files and retrieving all validation errors in Excel

Question

I am building an application using Excel VBA to validate xml files against xsd. I used below code snipped and added to my application.

https://stackoverflow.com/a/11709428/8854979

Now, the problem I am facing is, it doesn't provide full list of validation errors, instead it gives me only first error encountered.

While searching on Internet I came to know that I can use 'IXMLDOMParseError2' object with property allErrors, see below;

https://msdn.microsoft.com/en-us/library/ms759143(v=vs.85).aspx

this will give me list of all errors during xml validation.

I just wanted to know how to use this in VBA code.

Any help on this will be highly appreciated.

Thanks you in advance

score 2 · Answer 1 · edited Jan 02 '20 at 06:35

A bit late to the party, but hopefully this will help someone:

' PURPOSE:                                                                      '
' Validate XML Doc against Schema                                               '
Sub validateXDoc(ByRef xmlDoc As MSXML2.DOMDocument60)                          '

    Const SCHEMA_LOCATION As String = "C:\mySchema.xsd"
    Dim SCHEMA_NAMESPACE As String
    SCHEMA_NAMESPACE = "http://www.w3.org/2001/XMLSchema"


    Dim objSchemaCache As New XMLSchemaCache60
    Dim objErr As MSXML2.IXMLDOMParseError2

    objSchemaCache.Add SCHEMA_NAMESPACE, LoadXmlFile(SCHEMA_LOCATION)

    With xmlDoc
        Set .schemas = objSchemaCache
        .setProperty "MultipleErrorMessages", True
        Set objErr = .Validate()
    End With
    Dim oErr As Object

    For Each oErr In objErr.allErrors
        Debug.Print "Error parser: " & oErr.errorCode & "; " & oErr.reason
    Next oErr
End Sub

This code returns: Error parser: -1072897500; The node is neither valid nor invalid because no DTD/Schema declaration was found. — Shillington, May 27 '21 at 08:54

score 0 · Answer 2 · answered Oct 30 '17 at 08:41

0

Ypu can find syntax for the MultipleErrorMessages Property on this site. Something like this should work for you:

xmlDoc.setProperty("MultipleErrorMessages", TRUE)

answered Oct 30 '17 at 08:41

ashleedawg

20,365
9
72
105

I tried it but it gives me a syntax error, with message "Compile error: Expected: =" – Rajesh Suyal Oct 30 '17 at 09:02
Can you give me an example in VBA to retrieve all XML validation errors using XSD file. I want to list all error details and print them in excel worksheet. – Rajesh Suyal Oct 30 '17 at 11:32

user1898135 · Answer 3 · 2021-06-07T17:59:24.143

This is a working example (just improvised on previous answers) > No errors this time.

    Sub validateXMLDoc()
       'Schema
            Const SCHEMA_LOCATION As String = "C:\xsd.xsd"
            Dim SCHEMA_NAMESPACE As String
            SCHEMA_NAMESPACE = "http://www.w3.org/2001/XMLSchema" 
            
            Dim objSchemaCache
            Set objSchemaCache = New XMLSchemaCache60
            objSchemaCache.Add SCHEMA_NAMESPACE, SCHEMA_LOCATION
      
            Dim objerr As MSXML2.IXMLDOMParseError2
           
       'XML Document     
            Dim xmlDoc As MSXML2.DOMDocument60
            Set xmlDoc = New MSXML2.DOMDocument60
            Set xmlDoc.Schemas = objSchemaCache
            xmlDoc.async = False ': xmlDoc.validateOnParse = False
            xmlDoc.Load ("D:\xmlfilename.xml")
           
            With xmlDoc
                .setProperty "MultipleErrorMessages", True
                Set objerr = .Validate()
            End With
            Dim oErr As Object
        'Error code : Reason along with Line Number
            For Each oErr In objerr.allErrors
                MsgBox "Error parser: "& " " & oErr.Line & oErr.ErrorCode & "; " & oErr.reason
            Next oErr
        
            Set xmlDoc = Nothing
    End Sub

VBA code to validate XML using XSD files and retrieving all validation errors in Excel

3 Answers3