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")

show us the code you have written and we'll help you improve it, or fix problems — Our Man in Bananas
– Our Man in Bananas, Commented Apr 22, 2013 at 8:47
your xml looks like it has been output by an ADODB method. Have you considered opening the recordset directly using VBA and dumping the data into Excel using Copyfromrecordset? — Our Man in Bananas
– Our Man in Bananas, Commented Apr 22, 2013 at 9:03
@Philip i have just tried following code. When i looked into watch window to see the values in xmldoc (declared variable), i couldn't find the way to reach tag <rs:data Dim xmldoc As New MSXML2.DOMDocument xmldoc.Load ("C:\DOCUME~1\xyz\LOCALS~1\Temp\zr_aud_bbsw_130422.xml") — Anand
– Anand, Commented Apr 22, 2013 at 10:39
@Philip This file is output of one of our main systems. so i cannot access it via recordset. Hence i am trying to parse the XML file — Anand
– Anand, Commented Apr 22, 2013 at 10:44

Anand · Accepted Answer · 2013-04-23 03:29:09Z

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

answered Apr 23, 2013 at 3:29

Anand

611 silver badge7 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Our Man in Bananas Over a year ago

+1 well done - it's always better to learn how to fish than be given a fish :)

tofo · Accepted Answer · 2013-11-25 12:31:28Z

0

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

answered Nov 25, 2013 at 12:31

tofo

3983 silver badges9 bronze badges

Collectives™ on Stack Overflow

read custom xml output using excel vba

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related