创建带编号的XML节点,在创建XML节点时设置属性。

huangapple go评论65阅读模式
英文:

Create numbered XML nodes, set attributes when creating XML node

问题

我有一个Excel宏,从一个文件中读取XML值,并在第二个文件中复制它们。我不能只是复制文件,因为第二个文件有更多和不同的内容,用户输入确定了映射的内容。例如,以电影数据库的示例来说,用户选择要从源到目标复制哪些流派。我需要映射特定的值;源和目标的XPath在一个电子表格中。新的XML文件中并不是所有的标签都存在,所以它们需要在它们的值可以被填充之前由我的代码创建。

我从Greg-R的工作中有了一个很好的开始,链接是https://stackoverflow.com/questions/12149941/create-xml-file-based-on-xpath-from-excel-with-vba;但他的代码不能处理带编号的节点。我从目标标签中去掉了编号,但无法弄清楚如何在节点上添加属性。例如,XPath可以是 //Movies/Title[@number=1]/Actor[@number=5].Name

Sub makeXPath(xmldoc As Object, xpath As String)
'来自:https://stackoverflow.com/questions/12149941/create-xml-file-based-on-xpath-from-excel-with-vba 的原始代码
Dim partsOfPath() As String
Dim oNodeList As IXMLDOMNodeList
Dim strXPathQuery As String
Dim sParent As String
Dim objRootElem As IXMLDOMElement
Dim objMemberElem As IXMLDOMElement
Dim objMemberName As IXMLDOMElement
Dim objParent As Object

Set objParent = xmldoc

partsOfPath = Split(xpath, "/")

For i = LBound(partsOfPath) To UBound(partsOfPath)
    If strXPathQuery > "" Then strXPathQuery = strXPathQuery & "/"
    strXPathQuery = strXPathQuery & partsOfPath(i)

    Set oNodeList = xmldoc.SelectNodes(strXPathQuery)

    If oNodeList.Length = 0 Then
        '如果我没有这个节点,就创建它
        Debug.Print "partsOfPath(" & i & ") = " & partsOfPath(i)

        NumberPos = InStr(partsOfPath(i), "[@number=")
        If NumberPos > 0 Then
            '带编号的节点,提取编号
            ElementName = Left(partsOfPath(i), NumberPos - 1)
            'Len("[@number=") = 9。通过不每次计算它来加速代码。每一点都有帮助!
            NodeNumber = Mid(partsOfPath(i), NumberPos + 9, Len(partsOfPath(i)) - NumberPos - 9)
        Else
            ElementName = partsOfPath(i)
            NodeNumber = ""
        End If

        Set objMemberElem = xmldoc.createElement(ElementName)
        objParent.appendChild objMemberElem

        If Not NodeNumber = "" Then
            objMemberElem.createAttribute ("number")         '<<<------ 这部分引发错误 :(
            .createAttribute ("number")
            
            objParent.Attributes.setNamedItem(objAttr).Text = NodeNumber
        End If
        '为路径的下一个元素设置父节点
        Set objParent = objMemberElem
    Else
        '将父节点设置为第一次迭代,直到我以后做出其他调整
        Set objParent = oNodeList.Item(0)
    End If

Next

End Sub

我研究了这个问题(Chrome能处理多少个标签页?)并尝试了各种方法。我应该使用哪些方法?

英文:

I've got an Excel macro reading XML values from one file and replicating them in a second. I can't just copy files, as the second file has more and different stuff, and user input determines what is mapped.
E.g. to use the example of a movie database, the user picks which genres to copy from source to target. I need to map specific values; the XPaths of the source and target are in a spreadsheet. Not all of the tags exist in the new XML file, so they need to be created by my code before their values can be populated.

I've a great start from Greg-R's work at https://stackoverflow.com/questions/12149941/create-xml-file-based-on-xpath-from-excel-with-vba; but his code doesn't handle numbered nodes. I stripped the number from the target tag, but can't figure out the methods for adding the attribute to the node.
E.g. the XPath could be //Movies/Title[@number=1]/Actor[@number=5].Name

Sub makeXPath(xmldoc As Object, xpath As String)
'Original code from: https://stackoverflow.com/questions/12149941/create-xml-file-based-on-xpath-from-excel-with-vba
Dim partsOfPath() As String
Dim oNodeList As IXMLDOMNodeList
Dim strXPathQuery As String
Dim sParent As String
Dim objRootElem As IXMLDOMElement
Dim objMemberElem As IXMLDOMElement
Dim objMemberName As IXMLDOMElement
Dim objParent As Object

Set objParent = xmldoc

partsOfPath = Split(xpath, "/")

For i = LBound(partsOfPath) To UBound(partsOfPath)
    If strXPathQuery > "" Then strXPathQuery = strXPathQuery & "/"
    strXPathQuery = strXPathQuery & partsOfPath(i)

    Set oNodeList = xmldoc.SelectNodes(strXPathQuery)

    If oNodeList.Length = 0 Then
        'if I don't have the node, create it
        Debug.Print "partsOfPath(" & i & ") = " & partsOfPath(i)

        NumberPos = InStr(partsOfPath(i), "[@number=")
        If NumberPos > 0 Then
            'Numbered node, extract the number
            ElementName = Left(partsOfPath(i), NumberPos - 1)
            'Len("[@number=") = 9. Speed the code up by not calculating it each time. Every little bit helps!
            NodeNumber = Mid(partsOfPath(i), NumberPos + 9, Len(partsOfPath(i)) - NumberPos - 9)
        Else
            ElementName = partsOfPath(i)
            NodeNumber = ""
        End If

        Set objMemberElem = xmldoc.createElement(ElementName)
        objParent.appendChild objMemberElem

        If Not NodeNumber = "" Then
            objMemberElem.createAttribute ("number")         '<<<------ This bit is throwing errors :(
            .createAttribute ("number")
            
            objParent.Attributes.setNamedItem(objAttr).Text = NodeNumber
        End If
        'setting the parent for the next element of the path
        Set objParent = objMemberElem
    Else
        'setting parent to first iteration, until I make adjustment otherwise later
        Set objParent = oNodeList.Item(0)
    End If

Next

End Sub

I researched this (How many tabs can Chrome handle?) and tried various methods. What are the methods I should be using?

答案1

得分: 1

createElement类似,createAttribute是XML文档的方法,而不是节点,比如objMemberElem

这应该有效:

If Not NodeNumber = "" Then
    Set objAttr = xmldoc.createAttribute("number")  
    objAttr.Value = NodeNumber
    objMemberElem.Attributes.setNamedItem objAttr
End If
英文:

Just like createElement, createAttribute is a method of the xml document, not of a node like objMemberElem.

This should work:

If Not NodeNumber = "" Then
    Set objAttr = xmldoc.createAttribute("number")  
    objAttr.Value = NodeNumber
    objMemberElem.Attributes.setNamedItem objAttr
End If

huangapple
  • 本文由 发表于 2023年2月19日 05:22:57
  • 转载请务必保留本文链接:https://go.coder-hub.com/75496479.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定