asked on

XML Parsing, How to get each set of values from a node

I rarely work with XML, so it's always a bear to figure out the right method of parsing values. I have an import function I built years ago (to import batch settlement data from AuthNet), but just discovered a [big] problem. The code below loops though each "transaction" node (I thought) and then gets all the values for that transaction:

for each item in xmlDoc.getElementsByTagName("transaction")

				If Not xmlDoc.getElementsByTagName("transId").item(i) Is Nothing Then transId = xmlDoc.getElementsByTagName("transId").item(i).text else transId = ""
				If Not xmlDoc.getElementsByTagName("submitTimeLocal").item(i) Is Nothing Then submitTimeLocal = xmlDoc.getElementsByTagName("submitTimeLocal").item(i).text else submitTimeLocal = ""
				If Not xmlDoc.getElementsByTagName("transactionStatus").item(i) Is Nothing Then transactionStatus = xmlDoc.getElementsByTagName("transactionStatus").item(i).text else transactionStatus = ""
				If Not xmlDoc.getElementsByTagName("invoiceNumber").item(i) Is Nothing Then invoiceNumber = xmlDoc.getElementsByTagName("invoiceNumber").item(i).text else invoiceNumber = ""
				If Not xmlDoc.getElementsByTagName("firstName").item(i) Is Nothing Then firstName = xmlDoc.getElementsByTagName("firstName").item(i).text else firstName = ""
				If Not xmlDoc.getElementsByTagName("lastName").item(i) Is Nothing Then lastName = xmlDoc.getElementsByTagName("lastName").item(i).text else lastName = ""
				If Not xmlDoc.getElementsByTagName("accountType").item(i) Is Nothing Then accountType = xmlDoc.getElementsByTagName("accountType").item(i).text else accountType = ""
				If Not xmlDoc.getElementsByTagName("accountNumber").item(i) Is Nothing Then accountNumber = xmlDoc.getElementsByTagName("accountNumber").item(i).text else accountNumber = ""
				If Not xmlDoc.getElementsByTagName("settleAmount").item(i) Is Nothing Then settleAmount = xmlDoc.getElementsByTagName("settleAmount").item(i).text else settleAmount = 0

  ' do something with the values

     i=i+1
next

Open in new window

At least that's what I thought I was doing. I thought that by specifying the item(i) I was ensuring that the values being populated into each variable belonged to the parent transaction.

However, sometimes AuthNet doesn't have a FirstName & LastName value in the transaction, so rather than sending a blank value they simply leave it out entirely, so that what I thought was the 5th "FirstName" element is now the 4th, and thus the customer names thereafter don't match the transactions.

For example, you'll notice that of the below 3 transactions the second one has missing FIrstName and LastName rows:

<transaction>
    <transId>1234567890</transId>
    <submitTimeUTC>2017-08-12T11:54:52Z</submitTimeUTC>
    <transactionStatus>settledSuccessfully</transactionStatus>
    <invoiceNumber>19999</invoiceNumber>
   <firstName>Bob</firstName>
   <lastName>Jones</lastName>
    <accountType>MasterCard</accountType>
    <accountNumber>XXXX1234</accountNumber>
    <settleAmount>97.20</settleAmount>
    <marketType>eCommerce</marketType>
    <product>Card Not Present</product>
</transaction>
<transaction>
    <transId>1234567891</transId>
    <submitTimeUTC>2017-08-12T11:54:52Z</submitTimeUTC>
    <transactionStatus>settledSuccessfully</transactionStatus>
    <invoiceNumber>20000</invoiceNumber>
    <accountType>MasterCard</accountType>
    <accountNumber>XXXX1234</accountNumber>
    <settleAmount>97.20</settleAmount>
    <marketType>eCommerce</marketType>
    <product>Card Not Present</product>
</transaction>
<transaction>
    <transId>1234567892</transId>
    <submitTimeUTC>2017-08-12T11:54:52Z</submitTimeUTC>
    <transactionStatus>settledSuccessfully</transactionStatus>
    <invoiceNumber>20001</invoiceNumber>
   <firstName>Bob</firstName>
   <lastName>Jones</lastName>
    <accountType>MasterCard</accountType>
    <accountNumber>XXXX1234</accountNumber>
    <settleAmount>97.20</settleAmount>
    <marketType>eCommerce</marketType>
    <product>Card Not Present</product>
</transaction>

Open in new window

I know this is probably so simplistic to some of you guys, but I just can't seem to wrap my head around how to EASILY pull the right values out of an XML file.

Please help with a simple way for me to edit my existing code to accomplish the correct correlation of data from the xml file. I would rather not have to recode the whole process.

Thanks!

jetbet

The easiest way of getting values out of an XML file is using XPath. I tend to use a couple of small utility functions to make life easier like

public static string GetElementValue(XmlNode _parent, string _xPath, string _default)
        {
            try
            {
                if (_parent == null)
                {
                    return _default;
                }
                else
                {
                    XmlNode lNode = _parent.SelectSingleNode(_xPath);
                    if (lNode != null)
                    {
                        return lNode.InnerText;
                    }
                    else
                    {
                        return _default;
                    }
                }
            }
            catch (Exception ex)
            {
                return _default;
            }
        }

Open in new window

and

public static string GetAttributeValue(XmlNode _parent, string _xPath, string _attribute)
        {
            try
            {
                String result = string.Empty;
                if (_parent != null)
                {
                    XmlNode lNode = _parent.SelectSingleNode(_xPath);
                    if (lNode != null)
                    {
                        if (lNode.Attributes[_attribute] != null)
                        {
                            result = lNode.Attributes[_attribute].Value;
                        }
                    }
                }
                return result;
            }
            catch (Exception ex)
            {
                return string.Empty;
            }
        }

Open in new window

So then you just get the node list from the document and do something like

XmlDocument transaction_doc = new XmlDocument();
 transaction_doc.LoadXml(response);
XmlNodeList transactions = transaction_doc.SelectNodes("document/Transactions")
foreach (XmlNode transaction in transactions)
{
string result = GetElementValue(transaction, "elementName1", string.Empty);
if (result == string.Empty)
{
result = GetElementValue(transaction, "elementname2", string.Empty);
}


;

Open in new window

jetbet

BTW the above code is C# but the concept should be easy to duplicate in VB

worthyking1

ASKER

@jetbet Sounds like a good solution however I'm a bit lost on how to rewrite those functions properly in VBScript.

ASKER CERTIFIED SOLUTION

Ryan Chong

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

aikimark

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

aikimark

Here is an example of refactored code using a function.

Sub ProcessXML()
    Dim dicTags
    
    For Each Item In xmlDoc.getElementsByTagName("transaction")
        Set dicTags = Q_29052458(Item)
        
        ' do something with the values
        i = i + 1
        
        'Example value reference: dicTags("transId")
        
    Next

End Sub

Function Q_29052458(xmlElement)
    Dim vTags
    Dim vItem
    vTags = Array(Array("transId", ""), Array("submitTimeLocal", ""), _
            Array("transactionStatus", ""), Array("invoiceNumber", ""), _
            Array("firstName", ""), Array("lastName", ""), Array("accountType", ""), _
            Array("accountNumber", ""), Array("settleAmount", 0))
    
    Set Q_29052458 = CreateObject("scripting.dictionary")
    
    For Each vItem In vTags
        
        If Not xmlElement.SelectSingleNode(vItem(0)) Is Nothing Then
            Q_29052458(vItem(0)) = xmlElement.SelectSingleNode(vItem(0)).Text
        Else
            Q_29052458(vItem(0)) = vItem(1)
        End If
    
    Next

End Function

Open in new window

worthyking1

ASKER

Thank you guys, that got me where I needed to go!

aikimark

Post-closure comment. I actually recommend this version of the IF statement. I'm not a fan of negating a condition. My $0.02

    For Each vItem In vTags
        
        If xmlElement.SelectSingleNode(vItem(0)) Is Nothing Then
            Q_29052458(vItem(0)) = vItem(1)
        Else
            Q_29052458(vItem(0)) = xmlElement.SelectSingleNode(vItem(0)).Text
        End If
    
    Next

Open in new window