title: VBA - Read XML file
body: <p>I need to read a XML file generated by an application to further do some automation. I am new to Excel VBA and have searched the net in vain! </p>
  
  <p>Sample XML file -</p>
  
  <pre><code>&lt;?xml version="1.0" encoding="ISO-9970-7"?&gt;
  &lt;!DOCTYPE MAPPING SYSTEM "mapper.pmp"&gt;
  &lt;MAPPER CREATION_DATE="01/01/2006 10:47:36" REPOSITORY_VERSION="XXX.YY"&gt;
  &lt;REPOSITORY NAME="development" VERSION="100" CODEPAGE="Latin1" DATABASETYPE="Oracle"&gt;
  &lt;FOLDER NAME="ETL" GROUP="" OWNER="Administrator" SHARED="NOTSHARED" DESCRIPTION="This is the test folder for checking xml read" PERMISSIONS="rwx------" UUID="ab16147d-15e7-5fg1-h9i1-jk2548001234"&gt;
      &lt;SOURCE BUSINESSNAME ="" DATABASETYPE ="Oracle" DBDNAME ="SAMPLE_DB" DESCRIPTION ="SOURCE DEFINITION FOR SRC_TEST TABLE." NAME ="SRC_TEST" OBJECTVERSION ="1" OWNERNAME ="SAMPLE_DB" VERSIONNUMBER ="1"&gt;
          &lt;SOURCEFIELD BUSINESSNAME ="" DATATYPE ="number" DESCRIPTION ="" FIELDNUMBER ="1" FIELDPROPERTY ="0" FIELDTYPE ="ELEMITEM" HIDDEN ="NO" KEYTYPE ="NOT A KEY" LENGTH ="24" LEVEL ="0" NAME ="SRC_TEST_KEY" NULLABLE ="NOTNULL" OCCURS ="0" OFFSET ="0" PHYSICALLENGTH ="15" PHYSICALOFFSET ="0" PICTURETEXT ="" PRECISION ="15" SCALE ="0" USAGE_FLAGS =""/&gt;
          &lt;SOURCEFIELD BUSINESSNAME ="" DATATYPE ="varchar2" DESCRIPTION ="" FIELDNUMBER ="2" FIELDPROPERTY ="0" FIELDTYPE ="ELEMITEM" HIDDEN ="NO" KEYTYPE ="NOT A KEY" LENGTH ="24" LEVEL ="0" NAME ="EMP_NAME" NULLABLE ="NOTNULL" OCCURS ="0" OFFSET ="24" PHYSICALLENGTH ="15" PHYSICALOFFSET ="15" PICTURETEXT ="" PRECISION ="15" SCALE ="0" USAGE_FLAGS =""/&gt;
          &lt;SOURCEFIELD BUSINESSNAME ="" DATATYPE ="varchar2" DESCRIPTION ="" FIELDNUMBER ="3" FIELDPROPERTY ="0" FIELDTYPE ="ELEMITEM" HIDDEN ="NO" KEYTYPE ="NOT A KEY" LENGTH ="24" LEVEL ="0" NAME ="EMP_DEPT" NULLABLE ="NULL" OCCURS ="0" OFFSET ="48" PHYSICALLENGTH ="15" PHYSICALOFFSET ="30" PICTURETEXT ="" PRECISION ="15" SCALE ="0" USAGE_FLAGS =""/&gt;
          &lt;SOURCEFIELD BUSINESSNAME ="" DATATYPE ="number" DESCRIPTION ="" FIELDNUMBER ="4" FIELDPROPERTY ="0" FIELDTYPE ="ELEMITEM" HIDDEN ="NO" KEYTYPE ="NOT A KEY" LENGTH ="24" LEVEL ="0" NAME ="EMP_SAL" NULLABLE ="NOTNULL" OCCURS ="0" OFFSET ="72" PHYSICALLENGTH ="15" PHYSICALOFFSET ="45" PICTURETEXT ="" PRECISION ="100" SCALE ="0" USAGE_FLAGS =""/&gt;
      &lt;/SOURCE&gt;
      &lt;TARGET BUSINESSNAME ="" CONSTRAINT ="" DATABASETYPE ="Flat File" DESCRIPTION ="Tagret definition for Flat file 1." NAME ="FLAT_FILE" OBJECTVERSION ="1" TABLEOPTIONS ="" VERSIONNUMBER ="1"&gt;
          &lt;FLATFILE CODEPAGE ="MS1252" CONSECDELIMITERSASONE ="NO" DELIMITED ="YES" DELIMITERS ="," ESCAPE_CHARACTER ="" KEEPESCAPECHAR ="NO" LINESEQUENTIAL ="NO" MULTIDELIMITERSASAND ="NO" NULLCHARTYPE ="ASCII" NULL_CHARACTER ="*" PADBYTES ="1" QUOTE_CHARACTER ="NONE" REPEATABLE ="NO" ROWDELIMITER ="0" SKIPROWS ="0" STRIPTRAILINGBLANKS ="NO"/&gt;
          &lt;TARGETFIELD BUSINESSNAME ="" DATATYPE ="string" DESCRIPTION ="" FIELDNUMBER ="1" KEYTYPE ="NOT A KEY" NAME ="EMP_NAME" NULLABLE ="NULL" PICTURETEXT ="" PRECISION ="256" SCALE ="0"/&gt;
          &lt;TABLEATTRIBUTE NAME ="Datetime Format" VALUE ="A  19 mm/dd/yyyy hh24:mi:ss"/&gt;
          &lt;TABLEATTRIBUTE NAME ="Thousand Separator" VALUE ="None"/&gt;
          &lt;TABLEATTRIBUTE NAME ="Decimal Separator" VALUE ="."/&gt;
          &lt;TABLEATTRIBUTE NAME ="Line Endings" VALUE ="System default"/&gt;
      &lt;/TARGET&gt;
      &lt;TARGET BUSINESSNAME ="" CONSTRAINT ="" DATABASETYPE ="Flat File" DESCRIPTION ="Tagret definition for Flat file 2." NAME ="FLAT_FILE_LIST" OBJECTVERSION ="1" TABLEOPTIONS ="" VERSIONNUMBER ="1"&gt;
          &lt;FLATFILE CODEPAGE ="MS1252" CONSECDELIMITERSASONE ="NO" DELIMITED ="YES" DELIMITERS =";" ESCAPE_CHARACTER ="" KEEPESCAPECHAR ="NO" LINESEQUENTIAL ="NO" MULTIDELIMITERSASAND ="NO" NULLCHARTYPE ="ASCII" NULL_CHARACTER ="*" PADBYTES ="1" QUOTE_CHARACTER ="NONE" REPEATABLE ="NO" ROWDELIMITER ="0" SKIPROWS ="0" STRIPTRAILINGBLANKS ="NO"/&gt;
          &lt;TARGETFIELD BUSINESSNAME ="" DATATYPE ="string" DESCRIPTION ="" FIELDNUMBER ="1" KEYTYPE ="NOT A KEY" NAME ="EMP_DEPT" NULLABLE ="NULL" PICTURETEXT ="" PRECISION ="10" SCALE ="0"/&gt;
          &lt;TARGETFIELD BUSINESSNAME ="" DATATYPE ="string" DESCRIPTION ="" FIELDNUMBER ="2" KEYTYPE ="NOT A KEY" NAME ="EMP_NAME" NULLABLE ="NULL" PICTURETEXT ="" PRECISION ="256" SCALE ="0"/&gt;
          &lt;TARGETFIELD BUSINESSNAME ="" DATATYPE ="number" DESCRIPTION ="" FIELDNUMBER ="3" KEYTYPE ="NOT A KEY" NAME ="EMP_SAL" NULLABLE ="NULL" PICTURETEXT ="" PRECISION ="10" SCALE ="0"/&gt;
          &lt;TABLEATTRIBUTE NAME ="Datetime Format" VALUE ="A  19 mm/dd/yyyy hh24:mi:ss"/&gt;
          &lt;TABLEATTRIBUTE NAME ="Thousand Separator" VALUE ="None"/&gt;
          &lt;TABLEATTRIBUTE NAME ="Decimal Separator" VALUE ="."/&gt;
          &lt;TABLEATTRIBUTE NAME ="Line Endings" VALUE ="System default"/&gt;
      &lt;/TARGET&gt;   
  &lt;/FOLDER&gt;
  &lt;/REPOSITORY&gt;
  &lt;/MAPPER&gt;
  </code></pre>
  
  <p>I want to read 
  1) the NAME in SOURCEFIELD, for e.g. SRC_TEST_KEY, EMP_NAME, EMP_DEPT and EMP_SAL.
  2) Their PHYSICALLENGTH 
  and so on.</p>
  
  <p>Attempted VBA code (I've searched on net)-</p>
  
  <pre><code>Sub read_xml()
      Dim Init, i As Integer
      Dim xmlDoc As MSXML2.DOMDocument
      Dim elements As Object
      Dim el As Variant
      Dim Prop As String
      Dim NumberOfElements As Integer
      Dim n As IXMLDOMNode
      Init = 5
  
      Set xmlDoc = CreateObject("MSXML2.DOMDocument")
      xmlDoc.Load ("C:\path\to\Sample_xml.xml")
      Set TitleNodes = xmlDoc.SelectNodes("/MAPPER/REPOSITORY/FOLDER/SOURCE/")
      lengthTitleNodes = Len(TitleNodes)
  
      For i = 0 To lengthTitleNodes
          Title = TitleNodes(i).NodeValue
      Next i
  End Sub
  </code></pre>
  
  <p>But obviously this is not working, It doesn't recognize any Tags, nodes, etc. I'm doing something majorly wrong here but haven't figured it out!! Can someone please help me in pointing to the right direction?</p>
  
  <p>Thanks in advance!</p>