December 31, 2008
Revised: May 30, 2014
Synopsis
This concept was initially conceived as a SilkTest script which I was experimenting with while trying to find an alternative to storing test data in spreadsheets. I later implemented two variations of the algorithm in VBScript.
Introduction
It’s a common practice to maintain test data in spreadsheets. For the purpose of maintaining test data, each row in a spreadsheet represents an individual test case. If the application being tested has a large number of data fields, for the most part, Excel spreadsheets are much easier to manipulate than Word tables. However, spreadsheets can become cumbersome if you are dealing with a large number of columns. Most people are averse to horizontal scrolling back and forth through a sheet to find the data they are looking for. In addition, when entering numerical data in Excel you have to remember to set the cell Category as Text.
This paper presents a solution that can be implemented using tools that every computer user has—an editor and a Web browser. Even though this technique was developed to eliminate using spreadsheets for inputting data into an automated test tool, such as SilkTest, it can be used to maintain test data for manual test cases as well. Both 4Test and VBScript implementations are presented.
Note: while Windows Notepad can be used to view and edit XML files, I recommend using a good programmer’s editor that provides syntax highlighting. For example, the editor in Microsoft Visual Studio. Other commercially available editors that I personally recommend and use are UltraEdit by IDM Computer Solutions, Inc. and VEDIT by Greenview Data, Inc. If you prefer free software, then I recommend Notepad++ and jEdit.
Excel to XML Conversion
The process of converting spreadsheet data to an XML formatted text file is fairly straightforward. Table 1 below is an example of a customer’s name and address in spreadsheet format.
| Name | Address | City | State | Zip |
| Joe Green | 25 East Colonial Drive | Orlando | FL | 32825 |
Row(1) in Table 1 contains the column headings which represent the data fields in the application. Each of the remaining Rows(2–n) contain the customer data which will be used for the test procedure. The steps necessary to convert the data to XML format is to first create a tag for each column heading along with the corresponding cell as the tag’s value. For example:
<Name>Joe Green</Name> <Address>25 East Colonial Drive</Address> <City>Orlando</City> <State>FL</State> <Zip>32825</Zip>
The next step is to create a tag to group the customer name and address information. The convention for naming this tag is to use the application’s window or page name. For this example, let’s use “Customer.” The XML now looks like the following:
<Customer>
<Name>Joe Green</Name>
<Address>25 East Colonial Drive</Address>
<City>Orlando</City>
<State>FL</State>
<Zip>32825</Zip>
</Customer>
Finally, a declarations line is added at the beginning of the file so that errors are not generated when the file is opened. Comments can also be added.
<?xml version="1.0" encoding="UTF-8"?>
<Customer>
<Name>Joe Green</Name>
<Address>25 East Colonial Drive</Address>
<City>Orlando</City>
<State>FL</State>
<Zip>32825</Zip>
</Customer>
4Test Solution
I began work on this XML parsing script during the 2002–2003 timeframe and then completed it in 2008. The 4Test script is presented in the normalized or code format using braces (“{“, “}”) and semicolons (“;”) as opposed to the Visual 4Test format which displays the code folding notation: [ ], [-] and [+].
Table 2 lists the files associated with the 4Test implementation.
| File | Description |
| XML.t | XML Parsing Script |
| shipping_address.xml | Data Input File |
| xml_res.txt | Results File |
// -------------------------------------------------------------
// Script: XML.t
//
// Description: 4Test script which parses XML files containing
// test data where the tags correspond to objects
// in the application and the tag values are data
// to be populated on the page/window.
//
// Revised: 9/25/2011
//
// Author: Gerard Sczepura
// -------------------------------------------------------------
const lt = "<";
const gt = ">";
const amp = "&";
const apos = "'";
const quot = '"';
const ce_lt = "<";
const ce_gt = ">";
const ce_amp = "&";
const ce_apos = "'";
const ce_quot = """;
const decl = lt + "?";
const cmntplg = lt + "!";
const endtag = lt + "/";
const nil = "";
BOOLEAN ParseXML (STRING sXMLFile, out LIST OF STRING lsData, out LIST OF STRING lsControls)
{
HFILE hInputFile;
BOOLEAN bFirstLine = TRUE;
BOOLEAN bPass = TRUE;
STRING sTextLine = "";
STRING sDataLine = " ";
STRING sToken = nil;
INTEGER iLineEnd = 0;
INTEGER iListEnd = 0;
LIST OF STRING lsTokens; // Token stack
STRING sWinFound = "";
STRING sParent = "";
hInputFile = FileOpen (sXMLFile, FM_READ);
if (FileReadLine (hInputFile, sTextLine) == FALSE)
{
LogError ("### Abend: Input File is Empty! ###");
return (FALSE);
}
while (FileReadLine (hInputFile, sTextLine) == TRUE)
{
sTextLine = LTrim (sTextLine);
while (TRUE)
{
if (sTextLine == nil)
{
break;
}
if (sTextLine[1] == lt) // Opening tag?
{
iLineEnd = StrPos (gt, sTextLine);
if (iLineEnd <= 0)
{
LogError ("### Abend: Invalid Text! {sTextLine} ###");
bPass = FALSE;
break;
}
else
{
if (sTextLine[1] == gt)
{
LogError ("### Abend: Invalid Text! {sTextLine} ###");
bPass = FALSE;
break;
}
}
}
else
{
iLineEnd = StrPos (lt, sTextLine) - 1;
if (iLineEnd <= 0)
{
iLineEnd = Len (sTextLine);
}
}
sToken = SubStr (sTextLine, 1, iLineEnd); //get token
sTextLine = SubStr (sTextLine, ++iLineEnd);
if (sToken[1] == lt)
{
if (SubStr (sToken, 1, 2) == decl || SubStr (sToken, 1, 2) == cmntplg) // Comments or Prolog?
{
break;
}
else
{
if (SubStr (sToken, 1, 2) == endtag) // End Tag?
{
if (SubStr (lsTokens[iListEnd], 2, Len (lsTokens[iListEnd]) - 2) // Valid end tag?
== SubStr (sToken, 3, Len (sToken) - 3))
{
ListDelete (lsTokens, iListEnd);
--iListEnd;
if (sDataLine != " ")
{
sDataLine = StrTran (sDataLine, ce_lt, lt);
sDataLine = StrTran (sDataLine, ce_gt, gt);
sDataLine = StrTran (sDataLine, ce_amp, amp);
sDataLine = StrTran (sDataLine, ce_apos, apos);
sDataLine = StrTran (sDataLine, ce_quot, quot);
ListAppend (lsData, SubStr (sDataLine, 1, Len (sDataLine) - 1));
sDataLine = " ";
}
}
else
{
LogError ("### Abend: Invalid Document! {lsTokens[iListEnd]} != {sToken} ###");
bPass = FALSE;
break;
}
}
else
{
if (bFirstLine) // if First line, then Parent Window
{
sParent = SubStr (sToken, 2, Len (sToken) - 2);
print ("RESULTS = Parent Window: {sParent}");
bFirstLine = FALSE;
}
else
{
sWinFound = SubStr (sToken, 2, Len (sToken) - 2);
ListAppend (lsControls, sWinFound);
}
ListAppend (lsTokens, sToken);
iListEnd = ListCount (lsTokens);
}
}
}
else
{
sDataLine = Stuff (sDataLine, Len (sDataLine), 0, sToken);
}
}
if (!bPass)
{
return (bPass);
}
}
FileClose (hInputFile);
return (bPass);
}
main ()
{
STRING sXMLFile = "C:\ScriptDev\XML\ShippingAddress.XML";
LIST OF STRING lsData;
LIST OF STRING lsControls;
ParseXML (sXMLFile, lsData, lsControls);
print ("RESULTS = Controls: {lsControls}");
print ("RESULTS = Data: {lsData}");
}
<!-- **************************************** --> <!-- *** File Name: ShippingAddress.XML ***** --> <!-- *** Site Name: Amazon.com ************ --> <!-- *** Author: Gerard Sczepura ************ --> <!-- **************************************** --> <Shipping_Address> <Full_Name>Gerard Sczepura</Full_Name> <Address_Line1>289 Upper Tinicum Church Road</Address_Line1> <Address_Line2></Address_Line2> <City>Upper Black Eddy</City> <State_Province_Region>PA</State_Province_Region> <ZIP_Postal_Code>18972</ZIP_Postal_Code> <Country>United States</Country> <Phone_Number>610-294-8097</Phone_Number> <IsThisAddressAlsoYourBillingAddress>No</IsThisAddressAlsoYourBillingAddress> </Shipping_Address>
Script XML.t - Passed
Machine: (local)
Started: 12:47:55PM on 25-Sep-2011
Elapsed: 0:00:00
Totals: 0 errors, 0 warnings
RESULTS = Parent Window: Shipping_Address
RESULTS = Controls: {Full_Name, Address_Line1, Address_Line2, City, State_Province_Region, ZIP_Postal_Code, Country, Phone_Number, IsThisAddressAlsoYourBillingAddress} RESULTS = Data: {Gerard Sczepura, 289 Upper Tinicum Church Road, , Upper Black Eddy, PA, 18972, United States, 610-294-8097, No, }
Script Features
- Leading spaces are trimmed from the input line
- Opening and closing tags are validated
- <, >, &, &apos, and " are translated
Script Limitations
- Leading tab characters are not trimmed from the input line
- Embedded windows are not handled
- Only one test case per XML file is assumed
Function Invocation
BOOLEAN ParseXML (STRING sXMLFile, out LIST OF STRING lsData, out LIST OF STRING lsControls) STRING sXMLFile = "C:\Temp\ShippingAddress.XML"; BOOLEAN bStatus; LIST OF STRING lsData; LIST OF STRING lsControls; bStatus = ParseXML (sXMLFile, lsData, lsControls);
VBScript Solution
I recently decided to re-implement the XML parsing function in VBScript since I’m now working primarily with HP QuickTest Pro. I took the approach of re-designing the algorithm instead of attempting a line for line translation. I also made the assumption that the tester would use a Web browser to validate the XML, so tag checking code was omitted. In addition, since I’m no longer working within the SilkTest framework mentioned previously, I was able to eliminate some function calls from the original version. Finally, where the SilkTest implementation uses local variables, the VBScript version uses mostly global variables.
I’m providing two different versions of the parsing subroutine. ParseXML_1 was my first attempt. This version parses the input string by successively reducing the size of the string after each tag or tag value is found. That is, each search always starts at the beginning of the string. ParseXML_2 was my second attempt at the subroutine. This version parses the input string by examining each character individually. Also, unlike the 4Test implementation, the code to read the input file is outside the subroutine.
'------------------------------------------------------------------------------
'Simple XML parser for retrieving test data from an .xml file.
'
' Function(s): TrimString (txtStr)
'
' Subroutine(s): InitArray (sArray, sVal)
' ParseXML_1 (txtLine)
' ParseXML_2 (txtLine)
'
' Revised: 9/25/2011
'
' Author: Gerard Sczepura
'------------------------------------------------------------------------------
Option Explicit
' -----------------------------------------------------------------------------
Sub InitArray (sArray, sVal)
Dim i
For i = LBound (sArray) to UBound (sArray)
sArray(i) = sVal
Next
End Sub
' -----------------------------------------------------------------------------
Function TrimString (txtStr)
'Remove leading and trailing spaces and tab characters
Do While InStr (1, txtStr, vbTab, 0) <> 0
txtStr = Mid (txtStr, InStr (1, txtStr, vbTab, 0) + 1)
txtStr = LTrim(txtStr)
If InStr (1, txtStr, vbTab, 0) > 1 Then
Exit Do
End If
Loop
Do While InStr (1, txtStr, vbTab, 0) <> 0
txtStr = Mid (txtStr, 1, InStr (1, txtStr, vbTab, 0) - 1)
Loop
txtStr = RTrim (txtStr)
TrimString = txtStr
End Function
' ---------------------------------------------------------------------------
Sub ParseXML_2 (txtLine)
Dim i
Dim token
Dim bLAngle, bCloseTag
token = ""
bLAngle = False
If Len (txtLine) > 0 Then ' skip over blank lines
For i = 1 to Len (txtLine)
Select Case Mid (txtLine, i, 1) ' examine the next char in the line
Case "<"
If token <> "" Then ' if not the first "<" then field value found
valueStruc (fvStrPtr) = token ' save field value
token = ""
End If
bLAngle = True
Case "/"
If bLAngle Then
bCloseTag = True ' found a closing tag
End If
Case ">"
If bCloseTag Then ' reset flag for next tag search
bCloseTag = False
Else ' save field name
fvStrPtr = fvStrPtr + 1
fieldStruc (fvStrPtr) = token
token = ""
If Mid (txtLine, i+1, 1) = "<" Then ' field value is empty
valueStruc (fvStrPtr) = ""
Exit For
End If
End If
bLAngle = False' reset flag for next tag search
Case "!", "?"
Exit For ' skip if declaration or comment line
Case Else
token = token & Mid (txtLine, i, 1) ' append the current char to the data item
End Select
Next
End If
End Sub
' ---------------------------------------------------------------------------
Sub ParseXML_1 (txtLine)
Dim linePtr
linePtr = 0
If Len (txtLine) > 0 Then ' skip over blank lines
If Mid (txtLine, 1, 2) <> "<?" And Mid (txtLine, 1, 2) <> "<!" Then ' skip over declarations and comments
Do Until Len (txtLine) <= 0
If Mid (txtLine, 1, 1) = "<" Then
If Mid (txtLine, 2, 1) = "/" Then
linePtr = InStr (1, txtLine, ">") ' closing tag processing
StkPtr = StkPtr - 1 ' pop the stack
txtLine = Mid (txtLine, linePtr + 1) ' strip characters already examined
Else
linePtr = InStr (1, txtLine, ">", 1)
StkPtr = StkPtr + 1
stack(StkPtr) = Mid (txtLine, 2, linePtr - 2) ' push the field name on the stack
fvStrPtr = fvStrPtr + 1
fieldStruc(fvStrPtr) = stack(StkPtr) ' save the field name
txtLine = Mid (txtLine, linePtr + 1, Len (txtLine) - linePtr)
End If
Else
If InStr (1, txtLine, "></") <> 0 Then ' field value is empty
valueStruc(fvStrPtr) = ""
Else
linePtr = InStr (1, txtLine, "</")
valueStruc(fvStrPtr) = Mid (txtLine, 1, linePtr - 1) ' save the field value
txtLine = Mid (txtLine, linePtr)
End IF
End If
Loop
End If
End If
End Sub
' Main _________________________________________
' ---------- Data structures for the field name/value pairs
Dim fieldStruc (19)
Dim valueStruc (19)
' ---------- Data structure for the stack
Dim stack (19)
' ---------- Stack and field/value pointers
Dim fvStrPtr, StkPtr
' ---------- Loop and line counter variables
Dim iLine, i
' ---------- File system variables
Dim filePath_in, filePath_out
Dim txtLine
Dim fsoIn, fsoOut
Dim XMLFile, ResultsFile
filePath_in = "C:\Documents and Settings\Gerry\My Documents\xml\shipping_address.xml"
filePath_out = "C:\Documents and Settings\Gerry\My Documents\xml\results.txt"
Set fsoIn = CreateObject("Scripting.FileSystemObject")
Set fsoOut = CreateObject("Scripting.FileSystemObject")
Set XMLFile = fsoIn.OpenTextFile (filePath_in, 1)
Set ResultsFile = fsoOut.OpenTextFile (filePath_out, 2, True)
iLine = 0
txtLine = ""
InitArray fieldStruc, ""
InitArray valueStruc, ""
fvStrPtr = -1
StkPtr = -1
Do While XMLFile.AtEndOfStream <> True
iLine = iLine+1
txtLine = XMLFile.ReadLine
txtLine = TrimString (txtLine)
ParseXML_1 txtLine
'ParseXML_2 txtLine
Loop
XMLFile.Close
ResultsFile.WriteLine "Results -> " & iLine & " data line(s) processed"
ResultsFile.writeLine
For i = LBound (fieldStruc) to UBound (fieldStruc)
If fieldStruc(i) = "" Then
Exit For
End If
If valueStruc(i) <> "" Or i <> LBound (fieldStruc) Then
ResultsFile.WriteLine "Results -> Field= " & fieldStruc(i) & " | Value= " & valueStruc(i)
Else
ResultsFile.WriteLine "Results -> Window= " & fieldStruc(i) ResultsFile.writeLine
End If
Next
Script Features
- Both leading and trailing tabs and spaces are trimmed from the input line
- The number of data lines processed from the input file is displayed
Script Limitations
- Embedded windows are not handled
- Only one test case per XML file is assumed
Function Invocation
Sub ParseXML_1 (txtLine)
Sub ParseXML_2 (txtLine)
Do While XMLFile.AtEndOfStream <> True
iLine = iLine+1
txtLine = XMLFile.ReadLine
txtLine = TrimString (txtLine)
ParseXML_1 txtLine
'ParseXML_2 txtLine
Loop
2013-2026 Gerard Sczepura. All rights reserved.