XML automation - part III (test parameters)

Now it is time to set some test parameters in our XML template (see previous post). In my test framework I handle test parameters in an SQL Server database but in my examples here I use good old Excel instead.

First, create and save an Excel file with your test parameters:


Second, new functions for reading parameters into Dictionary Object and set parameters in an xml:

strXMLTemplateFile = "c:\Templates.xml"
intTestCaseID = 1
Set objMyTestParameters = getTestParametersIntoDictionary ("c:\MyTestData.xls", "TestCase", "TestCaseID", intTestCaseID)
objMyTestParameters.Item("XMLRequest") = getXMLTemplate (strXMLTemplateFile, objMyTestParameters.Item("XMLTemplate"))
objMyTestParameters.Item("XMLRequest") = setTestParametersInXMLRequest (objMyTestParameters)
Msgbox objMyTestParameters.Item("XMLRequest")

Function getTestParametersIntoDictionary (strFileName, strSheetName, strKeyColumn, intKeyID)
'Initiate Dictionary
Set getTestParametersIntoDictionary = CreateObject("Scripting.Dictionary")
'Initiate Database connection
Set objDB = CreateObject("ADODB.Connection")
'Open connection
objDB.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source="&strFileName&_
";Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1"";"
strQuery="select * from [" & strSheetName & "$] where " & strKeyColumn & "=" & intKeyID
'Create a record set, execute query and store in dictionary
Set objRec = objDB.Execute(strQuery)
Do Until objRec.EOF
    For Each x In objRec.Fields
        'Convert NULL to empty string or clean from spaces
        strCurrentValue = x.Value
        If IsNull(strCurrentValue) Then
            strCurrentValue = ""
            Else
            strCurrentValue = Trim(strCurrentValue)
        End If
        'Add to dictionay
        getTestParametersIntoDictionary.Add Trim(x.Name), strCurrentValue
    Next
    objRec.MoveNext
Loop
'Clean up
objRec.Close
Set objRec = Nothing
objDB.Close
Set objDB = Nothing
End Function

Function setTestParametersInXMLRequest (objDictionary)
Set xmlDoc = CreateObject("Msxml2.DOMDocument")
'loadXML
xmlDoc.loadXML(objDictionary.Item("XMLRequest"))
'Check XML syntax
If (xmlDoc.parseError.errorCode <> 0) Then
   Set myErr = xmlDoc.parseError
   Msgbox "ERROR: " & myErr.reason
End If
Set root = xmlDoc.documentElement
'Put all test parameters in array
arrAllKeys = objDictionary.Keys
arrAllValues = objDictionary.Items
'Update values
For i = 0 to Ubound(arrAllKeys)
    'Only process $-type parameters and where values <> ""
    If Left(arrAllKeys(i),1) = "$" AND arrAllValues(i) <> "" Then
        strParameterElementName = arrAllKeys(i)
        strParameterElementName = Right(strParameterElementName,Len(strParameterElementName)-1)
        strParameterElementValue = arrAllValues(i)
        'Insert element and value
        Set node = root.getElementsByTagName(strParameterElementName)
        If node.Length > 0 Then
            For n = 0 to node.Length-1
                'Replace all
                node.Item(n).Text = strParameterElementValue
            Next
        End If
        Set node = Nothing
    End If
Next
'Return xml request
setTestParametersInXMLRequest = root.xml
'Clean up
Set root = Nothing
Set xmlDoc = Nothing
End Function

Up next: Part IV - Sending XML request