使用 excel vba 读取自定义 xml 输出
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/16142014/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
read custom xml output using excel vba
提问by Anand
one of our systems is providing us XML in following format.
我们的系统之一以以下格式为我们提供 XML。
Can you please help me how to parse this XML and store it into array using excel 2010 vba?
你能帮我如何解析这个 XML 并使用 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
到目前为止，在 excel vba 中，我尝试了以下代码，但无法进一步进行
Dim xmldoc As New MSXML2.DOMDocument
xmldoc.Load ("C:\DOCUME~1\xyz\LOCALS~1\Temp\zr_aud_bbsw_130422.xml")
回答by Anand
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
感谢 Philip 的帮助，我可以解析我的 XML。以下是我的最终代码，它读取 XML 并为我提取存储在属性下的数据
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
回答by tofo
Do not forget the option use xpath syntax to select nodes
不要忘记选项使用 xpath 语法来选择节点
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
使用 xpath，您可以在这样的一个或多个属性上放置条件
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.
[] 方括号可以放置在 xpath 表达式中的其他节点和多个节点上，可以使用通配符以及对父节点或子节点的后向和前向引用。
Read more about xpath here http://www.w3schools.com/xpath/xpath_syntax.asp
在此处阅读有关 xpath 的更多信息http://www.w3schools.com/xpath/xpath_syntax.asp
