title: using VBscript to extract data from XML from nodes and children
body: <p>first time poster here and somewhat newbie to VBscript. I could really use some help from you guys who know this like second nature.
  I've tried to include some pertinent information and hopefully not too much.</p>
  
  <p>I've been trying to get this to work and am finally reaching out after a few days time of attempts and a dozen iterations of code. I haven't found examples of extracting data from multiple levels (noes and chidlren) within the XML document.</p>
  
  <p>I've been tasked with extracting data from an XML file using VBScript. 
  the specific items are: Year, Account Number, Current Amount Due, Has Delinquent? (true/false) and Formatted Warrant Number.</p>
  
  <p>The format of the XML file is as below, with anywhere from 1,000 to 10,000+  nodes filled with this data along with plenty of 'misc' nodes in there as well.</p>
  
  <pre><code>  &lt;BillData&gt;
      &lt;BillHeader&gt;
        &lt;Year&gt;2010&lt;/Year&gt;
        &lt;misc&gt;&lt;/misc&gt;
        &lt;misc2&gt;&lt;/misc2&gt;
        &lt;misc3&gt;&lt;/misc3&gt;
        &lt;AcctNumber&gt;0002566129&lt;/AcctNumber&gt;
        &lt;misc4&gt;&lt;/misc4&gt;
        &lt;PayAmounts&gt;
           &lt;CurrentAmountDue&gt;133.06&lt;/CurrentAmountDue&gt;
           &lt;misc5&gt;&lt;/misc5&gt;
        &lt;/PayAmounts&gt;
        &lt;misc6&gt;&lt;/misc6&gt;
        &lt;HasDelinquents&gt;true&lt;/HasDelinquents&gt;
        &lt;WarrantInfo&gt;
           &lt;FormattedWarrantNumber&gt;201115447&lt;/FormattedWarrantNumber&gt;
        &lt;/WarrantInfo&gt;
       &lt;/BillHeader&gt;
     &lt;/BillData&gt;
  </code></pre>
  
  <p>CurrentAmountDue and FormattedWarrantNumber may not always be present. by this I dont mean they are blank, but the entire entry of CurrentAmountDue may be missing, as shown below.</p>
  
  <pre><code>&lt;PayAmounts&gt;
     &lt;misc5&gt;&lt;/misc5&gt;
  &lt;/PayAmounts&gt;
  </code></pre>
  
  <p>I need the extract this data to a comma separated text file. If the data is not present then I just need to insert the comman, so when the output is eventually imported to Excel it can be noted to be blank.</p>
  
  <p>The challenge for me is to get into the different child nodes and extract the data correctly. I can't seem to select the different nodes correctly.</p>
  
  <p>These are some links I've used as reference, but can't seem to get it working.</p>
  
  <p><a href="http://technet.microsoft.com/en-us/magazine/2007.02.heyscriptingguy.aspx" rel="nofollow">http://technet.microsoft.com/en-us/magazine/2007.02.heyscriptingguy.aspx</a>
  this seemed to be the direction to go in, but I get an error "Node Test Expected Here": </p>
  
  <pre><code>  Set colNodes=xmlDoc.SelectNodes("/BillData/BillHeader/*" (Year | Account | CurrentAmountDue)")
  </code></pre>
  
  <p>I found a post on Stack which suggested using this technique below, but it doesn't work for me once I get past two values, whereas I have more. I'm guessing this is due to the CurrentAmountDue and FormattedWarrantNumber are deeper levels into the XML so to speak.</p>
  
  <pre><code>  strQuery = "/BillData/BillHeader/ " &amp; _
    "[name()='Year' or name()='AccountNumber' or name()='HasDelinquents' or name()='CurrentAmountDue' or name()='FormattedWarrantNumber']"
  </code></pre>
  
  <p>To my surprise, I am able to get this to return some values but not all on the same loop so my output is off (first line will only display year, last line is missing) and is just a comma.</p>
  
  <pre><code>   strQuery = "/BillData/BillHeader/*"
     Set colNodes=xmlDoc.selectNodes(strQuery)
     For Each objNode in colNodes 
  
     ' some lame if then statements that get the values, but this can't be the correct approach!
     ' these three items (Year, Account and HasDelinquents are under each BillHeader as far as I can tell, but this doesn't seem to be the most effective method.
       if objNode.nodeName = "Year" then strYear = objNode.text  
       if objNode.nodeName = "Account" then strAccount = objNode.text 
       if objNode.nodeName = "HasDelinquents" then strHasDelq = objNode.text 
  
            for each CurrentAmt in objNode.SelectNodes("./CurrentAmountDue")
                  strCurrAmt = CurrentAmt.text
                  ' i finally got a value here when I use msgbox to view it.'
            next
  
            for each WarrantNum in objNode.SelectNodes("./FormattedWarrantNumber")
                  strWarNum = WarrantNum.text   
                  ' getting this value also when I use msgbox to view it.
            next
     next
  </code></pre>
  
  <p>So you can see my attempts are futile.</p>
  
  <p>I also tried insert this line below. I put it just before the last NEXT, but it didn't work as intended. I also attempted to insert some IF-Then statements to check for values in Year and Account before writing to the file and then clearing out the values after writing to the file. That almost worked, but my first line and last lines are not producing correct data.</p>
  
  <pre><code>     objFileToWrite.WriteLine(strYear &amp; "," &amp; strAccount &amp; "," &amp; strCurrAmt &amp; "," &amp; strHasDelq &amp; "," &amp; strWarNum)
  </code></pre>
  
  <p>ok now that you've had a giggle with my prehistoric attempt at coding this, can you lend me a hand? :)
  let me know if anything else is needed.
  thanks for any time invested. I know some of you can likely kick this out with ease.</p>