none
Replace/Add XMl node in XML file RRS feed

  • Question

  • Hi,

    I am not a developer but I need to resolve the following problem :-

    I have a xml file that I have created using VBA in Excel and saved on my c drive :-

    <?xml version="1.0"?>
    -<statistics>-<presentValue><statisticName>Present Value|20120402</statisticName>-<statOutputResultChoice><outputResultValue/></statOutputResultChoice>-<drilldownNameList><drilldownName>Agg1</drilldownName></drilldownNameList>-<baseBenchmarkPairNameList><baseBenchmarkPairName>DD 20120402</baseBenchmarkPairName></baseBenchmarkPairNameList>-<valuationSpecNameList><valuationSpecName>VD 20120402_</valuationSpecName></valuationSpecNameList></presentValue>-<deltaStressPVUserDefined><statisticName>eS1_5D_StC|20120402</statisticName>-<statOutputResultChoice><outputResultValue/></statOutputResultChoice><stressTestName>eS1_5D_StC</stressTestName>-<drilldownNameList><drilldownName>Agg1</drilldownName></drilldownNameList>-<baseBenchmarkPairNameList><baseBenchmarkPairName>DD 20120402</baseBenchmarkPairName></baseBenchmarkPairNameList>-<valuationSpecNameList><valuationSpecName>VD 20120402_5D</valuationSpecName></valuationSpecNameList></deltaStressPVUserDefined>-<deltaStressPVUserDefined><statisticName>eS2_5D_StC|20120402</statisticName>-<statOutputResultChoice><outputResultValue/></statOutputResultChoice><stressTestName>eS2_5D_StC</stressTestName>-<drilldownNameList><drilldownName>Agg1</drilldownName></drilldownNameList>-<baseBenchmarkPairNameList><baseBenchmarkPairName>DD 20120402</baseBenchmarkPairName></baseBenchmarkPairNameList>-<valuationSpecNameList><valuationSpecName>VD 20120402_5D</valuationSpecName></valuationSpecNameList></deltaStressPVUserDefined></statistics>

    I want to add this data into a child node of <statistics> within the following xml file that I am constructing, which is of the following format :-

    <?xml version="1.0"?>
    -<rmlQuery>-<timeSeriesData><hedgeFundDataList/></timeSeriesData>-<runAnalysis><numberofSimulations>1000</numberofSimulations>-<binningAlgorithm><noBinning/></binningAlgorithm><baseBenchMarkPairList/><valuationSpecList/><factorModelSpecList/><monteCarloSpecList/><drilldownList/>-<statistics><temp/></statistics><positionGroupList/><dividerGroupList/><horizonGroupList/><issuerMappingRulesList/></runAnalysis></rmlQuery>

    the code I have been using is :-

    Sub Overall_XML()
      
       Dim aa1, aa2, aa3, aa4, aa5, aa6, file_out As String
       Dim objDom As DOMDocument
       Dim objRootElem As IXMLDOMElement
       Dim objMemberElem As IXMLDOMElement
       Dim objMemberRel As IXMLDOMAttribute
       Dim objMemberName As IXMLDOMElement
       Dim objMemberName1 As IXMLDOMElement
       Dim objMemberName2 As IXMLDOMNode
       Dim temp As IXMLDOMNode
       Dim temp1 As IXMLDOMNode
       Dim doc1 As MSXML2.DOMDocument60
       Dim doc2 As MSXML2.DOMDocument60
       Dim doc3 As MSXML2.DOMDocument60
       Dim doc4 As MSXML2.DOMDocument60
       Dim doc5 As MSXML2.DOMDocument60
       Dim doc7 As IXMLDOMDocumentFragment
       Dim doc8 As CustomXMLNode
      
        Call control_details
       
       Set doc1 = New MSXML2.DOMDocument60
       Set doc2 = New MSXML2.DOMDocument60
       Set doc3 = New MSXML2.DOMDocument60
       Set doc4 = New MSXML2.DOMDocument60
       Set doc5 = New MSXML2.DOMDocument60
       
        doc4.async = False
        doc5.async = False
        doc4.Load (root_dir & stat_file)
        doc5.Load (root_dir & stat_file)
        Set doc7 = doc4.createDocumentFragment
       
        doc7.appendChild doc4.DocumentElement.CloneNode(True)
        Set temp = doc7

       Set objDom = New DOMDocument
      
       ' Creates root element
       Set objRootElem = objDom.createElement("rmlQuery")
       objDom.appendChild objRootElem
         
          ' Creates Member element
       Set objMemberElem = objDom.createElement("timeSeriesData")
       objRootElem.appendChild objMemberElem
      
         Set objMemberName = objDom.createElement("hedgeFundDataList")
         objMemberElem.appendChild objMemberName
      
       Set objMemberElem = objDom.createElement("runAnalysis")
       objRootElem.appendChild objMemberElem
        
         Set objMemberName = objDom.createElement("numberofSimulations")
         objMemberElem.appendChild objMemberName
         objMemberName.Text = noSim 'a(2)
      
         Set objMemberName = objDom.createElement("binningAlgorithm")
         objMemberElem.appendChild objMemberName
      
           Set objMemberName1 = objDom.createElement("noBinning")
           objMemberName.appendChild objMemberName1
      
         Set objMemberName = objDom.createElement("baseBenchMarkPairList")
         objMemberElem.appendChild objMemberName
        
         Set objMemberName = objDom.createElement("valuationSpecList")
         objMemberElem.appendChild objMemberName
        
         Set objMemberName = objDom.createElement("factorModelSpecList")
         objMemberElem.appendChild objMemberName
     
         Set objMemberName = objDom.createElement("monteCarloSpecList")
         objMemberElem.appendChild objMemberName
     
         Set objMemberName = objDom.createElement("drilldownList")
         objMemberElem.appendChild objMemberName
        
        ' place holder for Statistics
         Set objMemberName = objDom.createElement("statistics")
         objMemberElem.appendChild objMemberName
            Set temp1 = objDom.SelectSingleNode("//rmlQuery/runAnalysis/statistics/temp")
    '      Set objMemberName2 = objDom.createElement(temp)
          objMemberName.InsertBefore temp, temp1.LastChild 'objMemberName2

        ' place holder for Stress tests
    '     objMemberElem.appendChild (doc5)
       
         Set objMemberName = objDom.createElement("positionGroupList")
         objMemberElem.appendChild objMemberName
       
        ' place holder for positionsList
       
         Set objMemberName = objDom.createElement("dividerGroupList")
         objMemberElem.appendChild objMemberName

         Set objMemberName = objDom.createElement("horizonGroupList")
         objMemberElem.appendChild objMemberName

         Set objMemberName = objDom.createElement("issuerMappingRulesList")
         objMemberElem.appendChild objMemberName
     
       ' Saves XML data to disk.
       file_out = root_dir & All_file
       objDom.Save (file_out)

    End Sub

    Sub Stats_XML()
      
       Dim aa, aa0, aa1, aa2, aa3, aa4, file_out As String
       Dim ba1, va1, sa1 As String
       Dim objDom As DOMDocument
       Dim objRootElem As IXMLDOMElement
       Dim objMemberElem As IXMLDOMElement
       Dim objMemberRel As IXMLDOMAttribute
       Dim objMemberName As IXMLDOMElement
       Dim objMemberName1 As IXMLDOMElement
       Dim objMemberName2 As IXMLDOMElement
       Dim objMemberName3 As IXMLDOMElement
       Dim objMemberName4 As IXMLDOMElement
      
       aa1 = "presentValue"
       aa2 = "statisticName"
       aa3 = "deltaStressPVUserDefined"
       aa4 = "Present Value"
      
       Call control_details
       
       Call Stress_dataload
             
       Set objDom = New DOMDocument
      
       ' Creates root element
       Set objRootElem = objDom.createElement("statistics")
       objDom.appendChild objRootElem
      
      For i = 3 To 5 's_rows
     
       aa0 = stress_data(i, 2) & "|" & Format(run_d, "YYYYMMDD")
       aa = aa3
       If i = 3 Then
         aa0 = aa4 & "|" & Format(run_d, "YYYYMMDD")
         aa = aa1
       End If
      
       da1 = "DD " & Format(run_d, "YYYYMMDD")
       va1 = "VD " & Format(run_d, "YYYYMMDD") & "_" & stress_data(i, 1)
      
      
       ' Creates Member element
       Set objMemberElem = objDom.createElement(aa)
       objRootElem.appendChild objMemberElem
      
         Set objMemberName = objDom.createElement(aa2)
         objMemberElem.appendChild objMemberName
         objMemberName.Text = aa0 'a(2)
           
         Set objMemberName = objDom.createElement("statOutputResultChoice")
         objMemberElem.appendChild objMemberName
         
          Set objMemberName1 = objDom.createElement("outputResultValue")
          objMemberName.appendChild objMemberName1
       
        If i > 3 Then
         Set objMemberName = objDom.createElement("stressTestName")
         objMemberElem.appendChild objMemberName
         objMemberName.Text = stress_data(i, 2) 'a(2)
        End If
             
         Set objMemberName = objDom.createElement("drilldownNameList")
         objMemberElem.appendChild objMemberName
        
           Set objMemberName1 = objDom.createElement("drilldownName")
           objMemberName.appendChild objMemberName1
           objMemberName1.Text = "Agg1" 'a(2)
              
         Set objMemberName = objDom.createElement("baseBenchmarkPairNameList")
         objMemberElem.appendChild objMemberName
        
           Set objMemberName1 = objDom.createElement("baseBenchmarkPairName")
           objMemberName.appendChild objMemberName1
           objMemberName1.Text = da1 'a(2)
              
         Set objMemberName = objDom.createElement("valuationSpecNameList")
         objMemberElem.appendChild objMemberName
        
           Set objMemberName1 = objDom.createElement("valuationSpecName")
           objMemberName.appendChild objMemberName1
           objMemberName1.Text = va1 'a(2)
                         
      Next i
     
       ' Saves XML data to disk.
       file_out = root_dir & stat_file
       objDom.Save (file_out)

    End Sub

    I have tried many things, but its failing at the point where I try and replace the existing "temp" childnode below "statistics" with the temp node I have created from the doc7 documentfragment.

    Any help is really appreciated, as I have been struggling with this for several days now.

    Monday, December 3, 2012 3:37 AM

Answers

  • Hi,

    Thanks for the quick response, I was working on the problems last night and managed to resolve it a different way based on the simple example I have provided below, which is probably easier to undertand compared to my initial example above.

    the xml file I am trying to place in a node is provided below :-

    <?xml version="1.0" encoding="UTF-8"?>
    <Inventory>
    <name>abc</name>
    <emp>emp1</emp>
    <id>25</id>
    <Computer>
    <ComputerName>atl-fs-01</ComputerName>
    <brand>dell</brand>
    </Computer>
    </Inventory>

    I am generating XMl code in the routine below and wanted to add the nodes within the Computer node level into the XML below :-

    Sub test_xml()

    Dim objXML As MSXML2.DOMDocument
    Set objXML = New MSXML2.DOMDocument
    Dim objXML1 As MSXML2.DOMDocument
    Set objXML1 = New MSXML2.DOMDocument
    Dim nnode As MSXML2.IXMLDOMNode
    Dim nnode1 As MSXML2.IXMLDOMNode
    Dim inode As MSXML2.IXMLDOMNode
    Dim inode1 As MSXML2.IXMLDOMNode
    Dim inode1a As MSXML2.IXMLDOMAttribute
    Dim inode2 As MSXML2.IXMLDOMNode
    Dim inode2a As MSXML2.IXMLDOMAttribute
    objXML.Load ("C:\Users\xml\test1.xml")

    Set inode = objXML1.createElement("level1")
    objXML1.appendChild inode

     Set inode1 = objXML1.createElement("level21")
     inode.appendChild inode1

      Set inode1a = objXML1.createAttribute("level21a")
      inode1a.NodeValue = "level121a"
     
     Set inode1 = objXML1.createElement("level22")
     inode.appendChild inode1

     Set inode2 = objXML1.createElement("level31")
     inode1.appendChild inode2

    Set nnode = objXML1.SelectSingleNode("//level21")
    Set nnode1 = objXML.SelectSingleNode("//Computer")

    nnode.appendChild nnode1

    objXML1.Save ("C:\Users\xml\test2.xml")

    End Sub

    the resulting output is as required:-

    <?xml version="1.0"?>
    -<level1>

      -<level21>

        -<Computer>

           <ComputerName>atl-fs-01</ComputerName>

           <brand>dell</brand>

        </Computer>

       </level21>

      -<level22>

        <level31/>

       </level22>

     </level1>

    I have simply used the logic above to generate the final solution I required. Thanks for the response above.

    • Marked as answer by bharatpopat Tuesday, December 4, 2012 11:21 AM
    Tuesday, December 4, 2012 11:21 AM

All replies

  • Basically you need to find the “Statistics” element  and add another statistics element for which source is another xml file.

    So,in simple term

    1.First load the source xml file

    2.Find the Statistsics tag in current xml document

    3.Get the parent of statistic tag

    4.Insert by InsertBefore method.

     

    In case you need some other tag or need to replace instead of insert just change the method or change the parent.

    Here a trimmed down code which shows only the relevant portion which you need…

    Sub zUpdatedXML()
       
        Dim ThisDom As New DOMDocument50
        Dim SrcDom  As New DOMDocument50
        
        Dim objRoot As IXMLDOMNode
        Dim objMemberElem As IXMLDOMNode
        Dim objMemberName As IXMLDOMNode
        
        Dim TargetNode As IXMLDOMNode
        
        'Load the source doc in srcdom
        SrcDom.async = False
        SrcDom.Load ThisWorkbook.Path & "\1.xml"
    ‘1.xml is generated from your another macro.
        
        ' Creates root element
        Set objRoot = ThisDom.createElement("rmlQuery")
        ThisDom.appendChild objRoot
            
        ' Creates one child element
        Set objMemberElem = ThisDom.createElement("runAnalysis")
        objRoot.appendChild objMemberElem
        
        ' place holder for Statistics
        Set objMemberName = ThisDom.createElement("statistics")
        objMemberElem.appendChild objMemberName
        
        'Find the Statistics node.
        Set TargetNode = ThisDom.SelectSingleNode("//rmlQuery/runAnalysis/statistics")
        'In this case you do not need Xpath to select
        'statistics beacause you just craeted it
        'and have a reference to it by objmembername
        'But i thought you may need later.
            
        
        'Add to parent of Statistics
        'so that srcdom becomes sibling
        objMemberElem.InsertBefore SrcDom.DocumentElement, TargetNode
        
        ThisDom.Save ThisWorkbook.Path & "\2.xml"
    
    End Sub 
    


    Best Regards,
    Asadulla Javed, Kolkata
    ---------------------------------------------------------------------------------------------
    Please do not forget to click “Vote as Helpful” if any post helps you and "Mark as Answer”if it solves the issue.

    Tuesday, December 4, 2012 6:44 AM
    Answerer
  • Hi,

    Thanks for the quick response, I was working on the problems last night and managed to resolve it a different way based on the simple example I have provided below, which is probably easier to undertand compared to my initial example above.

    the xml file I am trying to place in a node is provided below :-

    <?xml version="1.0" encoding="UTF-8"?>
    <Inventory>
    <name>abc</name>
    <emp>emp1</emp>
    <id>25</id>
    <Computer>
    <ComputerName>atl-fs-01</ComputerName>
    <brand>dell</brand>
    </Computer>
    </Inventory>

    I am generating XMl code in the routine below and wanted to add the nodes within the Computer node level into the XML below :-

    Sub test_xml()

    Dim objXML As MSXML2.DOMDocument
    Set objXML = New MSXML2.DOMDocument
    Dim objXML1 As MSXML2.DOMDocument
    Set objXML1 = New MSXML2.DOMDocument
    Dim nnode As MSXML2.IXMLDOMNode
    Dim nnode1 As MSXML2.IXMLDOMNode
    Dim inode As MSXML2.IXMLDOMNode
    Dim inode1 As MSXML2.IXMLDOMNode
    Dim inode1a As MSXML2.IXMLDOMAttribute
    Dim inode2 As MSXML2.IXMLDOMNode
    Dim inode2a As MSXML2.IXMLDOMAttribute
    objXML.Load ("C:\Users\xml\test1.xml")

    Set inode = objXML1.createElement("level1")
    objXML1.appendChild inode

     Set inode1 = objXML1.createElement("level21")
     inode.appendChild inode1

      Set inode1a = objXML1.createAttribute("level21a")
      inode1a.NodeValue = "level121a"
     
     Set inode1 = objXML1.createElement("level22")
     inode.appendChild inode1

     Set inode2 = objXML1.createElement("level31")
     inode1.appendChild inode2

    Set nnode = objXML1.SelectSingleNode("//level21")
    Set nnode1 = objXML.SelectSingleNode("//Computer")

    nnode.appendChild nnode1

    objXML1.Save ("C:\Users\xml\test2.xml")

    End Sub

    the resulting output is as required:-

    <?xml version="1.0"?>
    -<level1>

      -<level21>

        -<Computer>

           <ComputerName>atl-fs-01</ComputerName>

           <brand>dell</brand>

        </Computer>

       </level21>

      -<level22>

        <level31/>

       </level22>

     </level1>

    I have simply used the logic above to generate the final solution I required. Thanks for the response above.

    • Marked as answer by bharatpopat Tuesday, December 4, 2012 11:21 AM
    Tuesday, December 4, 2012 11:21 AM