问题描述
在解析其他节点的数据时,如何为可能并不总是属于其父节点的节点创建循环?
How do you create a loop for a node that might not always be part of its parent - while parsing data for other nodes?
假设您有一个包含其中几个项目的非常大的文件,但为简单起见,让我们使用这个 XML(注意第一个书 id"没有我们想要的节点,所以我们的循环已经失败):
Assume you have a very large file with several of these items, but for simplicity let's use this XML (notice the first 'book id' does not have the node we want, so our loop already fails):
<?xml version="1.0"?>
<catalog>
<book id="Adventure" ISBN="00113" version="13">
<author>Ralls, Kim</author>
<title>XML Developer's Guide</title>
<price>44.95</price>
<misc>
<editor id="9B">
<editorBrand>Partial Edit</editorBrand>
<editorEmphasis>Minimal</editorEmphasis>
</editor>
</misc>
</book>
<book id="Adventure" ISBN="00114" version="14">
<author>Ralls, Kim</author>
<title>Midnight Rain</title>
<price>5.95</price>
<misc>
<Publisher id="5691">
<PublisherLocation>Los Angeles</PublisherLocation>
</Publisher>
<PublishedAuthor id="Ralls">
<StoreLocation>Store A/8</StoreLocation>
<seriesTitle>AAA</seriesTitle>
<store id="8">
<copies>26</copies>
</store>
</misc>
</book>
<book id="Adventure" ISBN="00115" version="14">
<author>Ralls, Kim</author>
<title>Mist</title>
<price>15.95</price>
<misc>
<Publisher id="8101">
<PublisherLocation>New Mexico</PublisherLocation>
</Publisher>
<PublishedAuthor id="Ralls">
<StoreLocation>Market C/13</StoreLocation>
<seriesTitle>BBB</seriesTitle>
<store id="9">
<copies>150</copies>
</store>
<store id="13">
<copies>60</copies>
</store>
</PublishedAuthor>
</misc>
</book>
<book id="Mystery" ISBN="00116" version="13">
<author>Bill, Simmons</author>
<title>NBA Insider</title>
<price>16.99</price>
<misc>
<editor id="11N">
<editorBrand>Full Edit</editorBrand>
<editorEmphasis>Full</editorEmphasis>
</editor>
</misc>
</book>
</catalog>
我们的 VBA 代码:
Our VBA Code:
Sub mySub()
Dim XMLFile As Variant
Dim seriesTitle As Variant
Dim series As String, Author As String, Title As String, StoreLocation As String
Dim ISBN As String, copies As String, storelc As String
Dim seriesArray() As String, AuthorArray() As String, BookTypeArray() As String, TitleArray() As String
Dim StoreLocationArray() As String, ISBNArray() As String, copiesArray() As String
Dim i As Long, x As Long, j As Long, pn As Object, loc As Object, arr, ln As String, loc2 As Object
Dim mainWorkBook As Workbook
Dim n As IXMLDOMNode
Set mainWorkBook = ActiveWorkbook
Set XMLFile = CreateObject("Microsoft.XMLDOM")
XMLFile.Load ("C:Books.xml")
XMLFile.setProperty "SelectionLanguage", "XPath"
x = 1
j = 0
Set seriesTitle = XMLFile.SelectNodes("/catalog/book/misc/PublishedAuthor/seriesTitle")
For i = 0 To (seriesTitle.Length - 1)
series = seriesTitle(i).Text
storelc = seriesTitle(i).SelectSingleNode("store/copies").Text
If series = "AAA" Or series = "BBB" Then
Set pn = seriesTitle(i).ParentNode
StoreLocation = pn.getElementsByTagName("StoreLocation").Item(0).nodeTypedValue
Author = pn.ParentNode.ParentNode.getElementsByTagName("author").Item(0).nodeTypedValue
Title = pn.ParentNode.ParentNode.getElementsByTagName("title").Item(0).nodeTypedValue
ISBN = pn.ParentNode.ParentNode.getAttribute("ISBN")
Set loc = pn.SelectSingleNode("seriesTitle/store[@id='" & storelc & "']/copies")
If loc Is Nothing Then
arr = Split(storelc, "/")
ln = Trim(arr(UBound(arr)))
Set loc = pn.SelectSingleNode("seriesTitle/store[@id='" & ln & "']/copies")
End If
If Not loc Is Nothing Then
copies = loc.Text
Else
copies = "?"
End If
AddValue seriesArray, series
AddValue AuthorArray, Author
AddValue TitleArray, Title
AddValue StoreLocationArray, StoreLocation
AddValue ISBNArray, ISBN
AddValue copiesArray, copies
j = j + 1
x = x + 1
End If
Next
Range("A3").Resize(j, 1).Value = WorksheetFunction.Transpose(AuthorArray)
Range("B3").Resize(j, 1).Value = WorksheetFunction.Transpose(TitleArray)
Range("C3").Resize(j, 1).Value = WorksheetFunction.Transpose(ISBNArray)
Range("D3").Resize(j, 1).Value = WorksheetFunction.Transpose(seriesArray)
Range("E3").Resize(j, 1).Value = WorksheetFunction.Transpose(StoreLocationArray)
Range("F3").Resize(j, 1).Value = WorksheetFunction.Transpose(copiesArray)
End Sub
'Utility method - resize an array as needed, and add a new value
Sub AddValue(arr, v)
Dim i As Long
i = -1
On Error Resume Next
i = UBound(arr) + 1
On Error GoTo 0
If i = -1 Then i = 0
ReDim Preserve arr(0 To i)
arr(i) = v
End Sub
我的目标是搜索seriesTitle".因此,我将专门创建一个 For 循环来搜索找到的项目的长度,然后解析seriesTitle"以及 ISBN、StoreLocation、作者、书名和副本.
My goal is to search for "seriesTitle". So I will specifically create a For loop that searches for the length of items found and then parse the "seriesTitle" along with ISBN, StoreLocation, Author, Book Title, and copies.
- 如果 seriesTitle 存在 - 它是第 14 版 - 我想打印 seriesTitle、ISBN、StoreLocation、作者、书名和副本.
- 如果 seriesTitle 不存在 - 那是版本 13 - 我只想打印 ISBN、作者和书名.
但问题在于,对于每个存在的书 id",不一定有一个seriesTitle"——我们可以得出的唯一关系是,当version=13"时,没有 seriesTitle.
But the issue is that for each 'book id' that exists, there isn't necessarily a "seriesTitle" - the only relationship we can draw is that when the 'version=13' there is no seriesTitle.
- 如果您没有对象来创建 For 循环搜索,您将如何遍历整个文档?当seriesTitle"不存在时,您将如何继续向 ISBN、作者和书名数组添加项目?
感谢您对我提出任何有用的意见和建议!
Thank you for teaching me with any helpful comments and suggestions!
推荐答案
根据我的评论,看起来你最好只循环所有 <book>
元素并阅读他们的子节点获取所需的值,而不是在 DOM 树中上下导航.
As per my comment, it seems like you would be better off just looping over all of the <book>
elements and reading their child nodes for the required values, rather than navigating up and down the DOM tree quite so much.
Sub Tester()
Dim d As New MSXML2.DOMDocument
Dim bks As MSXML2.IXMLDOMNodeList
Dim bk As Object
Dim cat As Object, sertitle
Dim isbn, storeLoc, auth, seriesTitle, vsn, copies, title
d.setProperty "SelectionLanguage", "XPath"
d.LoadXML Sheet1.Range("A1").Value
Set bks = d.SelectNodes("/catalog/book")
For Each bk In bks
vsn = bk.getAttribute("version")
isbn = bk.getAttribute("ISBN")
title = GetTextSafely(bk, "title")
storeLoc = GetTextSafely(bk, "misc/PublishedAuthor/StoreLocation")
seriesTitle = GetTextSafely(bk, "misc/PublishedAuthor/seriesTitle")
auth = GetTextSafely(bk, "author")
copies = "??" ' I'm unclear exactly what you're doing here....
Debug.Print vsn, isbn, storeLoc, seriesTitle, auth, title, copies
Next bk
End Sub
'utility function: get a node's value if it exists
Function GetTextSafely(el As Object, path As String)
Dim nd, rv
Set nd = el.SelectSingleNode(path)
If Not nd Is Nothing Then rv = nd.nodeTypedValue
GetTextSafely = rv
End Function
这篇关于VBA XML DOM 搜索可能并不总是存在的项目的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!