read custom xml output using excel vba

Question

one of our systems is providing us XML in following format.

Can you please help me how to parse this XML and store it into array using excel 2010 vba?

I would like to read contents in tag.

Thanks in advance for help.

<report_output>
<xml xmlns:s='uuid:BDC6E3F0-6DA3-11d1-A2A3-'
     xmlns:dt='uuid:C2F41010-65B3-11d1-A29F-'
     xmlns:rs='urn:schemas-microsoft-com:rowset'
     xmlns:z='#RowsetSchema'
     content="Report"
     title="CURVE REPORT"
     resultHeading="DUMPCURVE"
     resultName="CURVE REPORT"
     runat="04/22/13"
     user="xxx"
     database="xxx"
     version="xxx"
     applicationdate="04/22/13"
     >
  <s:Schema id='RowsetSchema'>
    <s:ElementType name='row' content='eltOnly'>
<s:attribute type='GeneratedPK' />      <s:attribute type='Ccy'/>
      <s:attribute type='dmIndex'/>
      <s:attribute type='CurveID'/>
      <s:attribute type='CurveDate'/>
      <s:attribute type='Days'/>
      <s:attribute type='Rate'/>
      <s:extends type='rs:rowbase'/>
    </s:ElementType>
<s:AttributeType name='GeneratedPK' rs:number='1' rs:maybenull='false' 
rs:keycolumn='true' rs:autoincrement='true' rs:writeunknown='true'>
<s:datatype dt:type='int' />
</s:AttributeType>
    <s:AttributeType name='Ccy' rs:number='2' rs:maybenull='true' rs:writeunknown='true'>
      <s:datatype dt:type='string' dt:maxLength='4' origDataType='STRING'/>
    </s:AttributeType>
    <s:AttributeType name='dmIndex' rs:number='3' rs:maybenull='true' rs:writeunknown='true'>
      <s:datatype dt:type='string' dt:maxLength='6' origDataType='STRING'/>
    </s:AttributeType>
    <s:AttributeType name='CurveID' rs:number='4' rs:maybenull='true' rs:writeunknown='true'>
      <s:datatype dt:type='string' dt:maxLength='9' origDataType='STRING'/>
    </s:AttributeType>
    <s:AttributeType name='CurveDate' rs:number='5' rs:maybenull='true' rs:writeunknown='true'>
      <s:datatype dt:type='string' dt:maxLength='10' origDataType='DATE  '/>
    </s:AttributeType>
    <s:AttributeType name='Days' rs:number='6' rs:maybenull='true' rs:writeunknown='true'>
      <s:datatype dt:type='string' dt:maxLength='35' origDataType='STRING'/>
    </s:AttributeType>
    <s:AttributeType name='Rate' rs:number='7' rs:maybenull='true' rs:writeunknown='true'>
      <s:datatype dt:type='number' dt:maxLength='50' origDataType='RATES '/>
    </s:AttributeType>
  </s:Schema>
  <rs:data>
  <z:row
 GeneratedPK='1'
    Ccy='xxx'
    dmIndex='xxx'
    CurveID='xxxx'
    CurveDate='2013-04-23T00:00:00'
    Days='1'
    Rate='0000'
  />
  <z:row
 GeneratedPK='2'
    Ccy='xxx'
    dmIndex='xxxxx'
    CurveID='xxxxx'
    CurveDate='2013-05-24T00:00:00'
    Days='32'
    Rate='00000000'
  />
  <z:row
 GeneratedPK='3'
    Ccy='xxx'
    dmIndex='xxx'
    CurveID='xxxx'
    CurveDate='2013-04-23T00:00:00'
    Days='1'
    Rate='0000'
  />
  <z:row
 GeneratedPK='4'
    Ccy='xxx'
    dmIndex='xxxxx'
    CurveID='xxxxx'
    CurveDate='2013-05-24T00:00:00'
    Days='32'
    Rate='00000000'
  />
<z:row
 GeneratedPK='5'
    Ccy='xxx'
    dmIndex='xxx'
    CurveID='xxxx'
    CurveDate='2013-04-23T00:00:00'
    Days='1'
    Rate='0000'
  />
  <z:row
 GeneratedPK='6'
    Ccy='xxx'
    dmIndex='xxxxx'
    CurveID='xxxxx'
    CurveDate='2013-05-24T00:00:00'
    Days='32'
    Rate='00000000'
  />
</rs:data>
</xml>




</report_output>

So far in excel vba i have tried following code, but could not proceed further

Dim xmldoc As New MSXML2.DOMDocument
xmldoc.Load ("C:\DOCUME~1\xyz\LOCALS~1\Temp\zr_aud_bbsw_130422.xml")

Answer 1

Do not forget the option use xpath syntax to select nodes

This will return a nodelist of all row nodes

   Dim nodelist As MSXML2.IXMLDOMNodeList
   Set nodelist = xDoc.SelectNodes("//rs:data/z:row")

Using xpath you can place conditions on one or more attributes like this

   Dim nodelist As MSXML2.IXMLDOMNodeList
   Set nodelist = xDoc.SelectNodes("//rs:data/z:row[@CurveDate='2013-04-23T00:00:00']")

The [] brackets could be placed on other and multiple nodes in the xpath expression and wildcard characters can be used and back and forward reference to parent or child nodes.

Read more about xpath here http://www.w3schools.com/xpath/xpath_syntax.asp

Answer 2

Thanks to Philip's help, i could parse my XML. Following is my final code which reads the XML and extracts data for me which are stored under attributes

Option Explicit
Public Sub LoadDocument()
Dim xDoc As MSXML.DOMDocument
Set xDoc = New MSXML.DOMDocument
xDoc.validateOnParse = False
If xDoc.Load("C:\LOG\zr_aud_bbsw_130422.xml") Then
   ' The document loaded successfully.
   ' Now do something intersting.
   DisplayNode xDoc.ChildNodes, 0
Else
   ' The document failed to load.
   ' See the previous listing for error information.
End If
End Sub

Public Sub DisplayNode(ByRef Nodes As MSXML.IXMLDOMNodeList, _
   ByVal Indent As Integer)

   Dim xNode As MSXML.IXMLDOMNode
   Dim xAttribute As MSXML.IXMLDOMAttribute
   Indent = Indent + 2

   For Each xNode In Nodes
      If xNode.NodeType = NODE_ELEMENT And Trim(xNode.ParentNode.nodeName) = "rs:data" Then
        For Each xAttribute In xNode.Attributes
            Debug.Print Space$(Indent) & xAttribute.BaseName & _
            ":" & xAttribute.NodeValue
        Next xAttribute

      End If

      If xNode.HasChildNodes Then
         DisplayNode xNode.ChildNodes, Indent
      End If
   Next xNode
End Sub