1

I've found some items that seem to dance around what I'm looking to do. I'm not a full-fledged programmer, but have been creating my own macros in Excel. I'm trying to create one now that will help me create the structure of an XML file.

For this context I have a column of Xpaths and one of associated values. I want to go down the list of XPaths, and for each one create any parents in the path as needed as well as the child and its value. I'll be adding another aspect to filter it to the fields I want, but the part I'm struggling with is the logic to check if each parent node exists, create it if needed, then add the child and its value.

Bonus would be if the output file were formatted with each node on a new line, and more so if it child nodes were indented from parent, but just creating it is my primary concern. Any help would be much appreciated, this has to potential to save me a lot of time.

Update: I've made progress on this, and was able to create a small sample file. I'm still not sure if there's a way to format the file with line returns and/or tab indents, but I think the main part of creating the file is well underway. I still need to look at how to handle iterative nodes. Here is what I have so far, if anyone has any critiques, it's welcome:

Sub main()
    Dim xmldoc As New DOMDocument60
    Dim xpath As String
    Dim outPath As String
    outPath = "c:\temp\test.xml"

    'just a small sample for now, xpaths in column 2 on spreadsheet
    For i = 2 To 5
        'xpaths I have leave off root element, so I add it here
        xpath = "session/" & Cells(i, 2).Value
        Call makeXPath(xmldoc, xpath)
    Next
    xmldoc.Save outPath
End Sub
Private Sub makeXPath(xmldoc As DOMDocument60, xpath As String)
    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
            Set objMemberElem = xmldoc.createElement(partsOfPath(i))
            objParent.appendChild objMemberElem
            '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

Update2 - Here is a sample of xpaths and example output that has a 2nd iteration of address:

data/account/AcctNumber
data/account/Phone
data/account/address/Address1
data/account/address/City
data/account/address/State
data/account/address/ZIP
data/account/address/AddressType

<data>
    <account>
        <AcctNumber>
        <Phone>
        <address>
            <Address1 />
            <City />
            <State />
            <ZIP />
            <AddressType />
        </address>
        <address>
            <Address1 />
            <City />
            <State />
            <ZIP />
            <AddressType />
        </address>
    </account>
</data>
Community
  • 1
  • 1
Greg R
  • 11
  • 1
  • 3
  • What have you got so far? It would be useful to update your question with some examples of your xpaths and values, and a small example of required output. – Tim Williams Aug 27 '12 at 23:46
  • Possible answer to your formatting question: http://stackoverflow.com/questions/1118576/how-can-i-pretty-print-xml-source-using-vb6-and-msxml – Tim Williams Aug 28 '12 at 00:08
  • @TimWilliams, thanks for the link, I'll try it out in a little bit. I'm not sure if the update I provided earlier gave you an idea of what I'm dealing with and trying to accomplish. – Greg R Aug 28 '12 at 00:46
  • I saw your update, but you didn't mention whether you still had a problem or what the issues with your code were... – Tim Williams Aug 28 '12 at 01:41
  • Seems like the way you're approaching this will make it difficult to have more than one element of the same name, since your xpath could conceivably match multiple nodes and you wouldn't know where to attach the next "level". Whether thats a problem will depend on the exact DTD for your document. – Tim Williams Aug 28 '12 at 04:05
  • I agree, and that's something I'm going to try to figure out when I pick it back up tomorrow. I can try to add some additional background, xpaths and desired output tomorrow as well. – Greg R Aug 28 '12 at 04:38
  • I've gotten past the initial hurdle I was seeking help with by figuring out an xpath parser and how to create the child nodes, especially when the formatting answer is thrown in. I'm inclined to consider this answered and post a new question if needed after I spend some time on the multiple iteration issue. I really appreciate the help. – Greg R Aug 28 '12 at 18:35

0 Answers0