VBA XML DOM 搜索可能并不总是存在的项目 [英] VBA XML DOM Search for an item that may not always exist

问题描述

在解析其他节点的数据时，如何为可能并不总是属于其父节点的节点创建循环?

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.

1. 如果 seriesTitle 存在 - 它是第 14 版 - 我想打印 seriesTitle、ISBN、StoreLocation、作者、书名和副本.
2. 如果 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 搜索可能并不总是存在的项目的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！