XML Integration

[alex878]

Hi guys,

I’m trying to do something quite simple and join two xml files together.

Basically I have an XML file called Test.xml and another called Test1.xml. I would like copy the xml text from Text.xml (all child nodes within population) and paste it into Test1.xml right before the end tag </data>.

All I have managed to achieve so far is being able to copy all the xml text from Test.xml to Range(“A2”) (I cant seem to figure out if this xml text can be stored in a variable so I copied it to a cell).

From there I am tring to insert the contents in Range (“A2”) to Test1.xml right before the </data> tag but I am having no luck with using the insertbefor method.

Could someone please help or provide direction? Thanks.



Test.xml

<population>
<details>
<person>Joe</person>
<age>90</age>
</details>
</population>




Test1.xml

<data>
<hello>Joe</hello>
</data>




[vba]
Sub Test()

Dim XMLDoc As New DOMDocument
Dim oXmlNode As IXMLDOMNode
Dim oXmlNodes As IXMLDOMNodeList

XMLDoc.Load ("c:\documents and settings\ncsald\desktop\Test.xml")

Set oXmlNodes = XMLDoc.SelectNodes("//details")

For Each oXmlNode In oXmlNodes
Worksheets("Sheet1").Range("A2") = oXmlNode.XML
Next

XMLDoc.Load ("c:\documents and settings\ncsald\desktop\Test1.xml")

Range("A2").InsertBefore "</data>"

XMLDoc.Save ("c:\documents and settings\ncsald\desktop\Test1.xml")

End Sub
[/vba]

[Bob Phillips]

What do you want the merged XML to look like?

[alex878]

Hi xld. Essentially like below:

Test1.xml

<data>
<hello>Joe</hello>

<details>
<person>Joe</person>
<age>90</age>
</details>

</data>

[alex878]

HI xml or others. Just wondering if anyone can please help on this???

[snb]

[vba]
sub snb()
with createobject("scripting.filesystemobject")
.createtextfile("C:\text3.xml").write replace(.opentextfile("C:\text1.xml").readall,"</data>", .opentextfile("C:\text.xml").readall & "</data>"
end with
end sub
[/vba]

[alex878]

Thanks snb.

But I actually need all child nodes within population to be selected in Test.xml and for this to be added before </data> in Test1.xml.

The reason why i cant read and insert all of Test.xml into Test1.xml (like in your example) is there will be other nodes in the future other and population added to Test.xml which I don't want to copy over into Test.xml.

Do you have any other suggestions?

[snb]

It look as if your requirements have been changed.
Please reconsider the phrasing of your requirements.
At the moment they are not clear to me.

[alex878]

Sorry if I'm not clear but ill start again.

Basically I want to copy all the child nodes within the tag, details (excuse my terminology if it's incorrect) from the XML file Test.xml and insert this into Test1.xml before </data> tag then save Test1.xml.

Test.xml

<population>
<details>
<person>Joe</person>
<age>90</age>
</details>
<status>
<income>10000</income>
<single>Yes</single>
</status>
</population>

Test1.xml

<data>
<hello>Joe</hello>
</data>

I want Test1.xml to now look like the below with the insertion as described above.

Test1.xml

<data>
<hello>Joe</hello>
<details>
<person>Joe</person>
<age>90</age>
</details>
</data>

My code so far below. Which copies child nodes within details tag in Test.xml and pastes it in a cell in excel (i can get this working) then copying this cell into Test1.xml before </data> (this I can't get working - this is my problem)

[VBA]Sub Test_merge()

Dim XMLDoc As New DOMDocument
Dim oXmlNode As IXMLDOMNode
Dim oXmlNodes As IXMLDOMNodeList

XMLDoc.Load ("c:\Test.xml")

Set oXmlNodes = XMLDoc.SelectNodes("//details")

For Each oXmlNode In oXmlNodes
Worksheets("Sheet1").Range("A2") = oXmlNode.XML
Next

'I can't get the below code to work the above code works well
XMLDoc.Load ("c:\Test1.xml")

Range("A2").InsertBefore "</data>"

XMLDoc.Save ("c:\Test1.xml")

End Sub[/VBA]

Please let me know if the above does not make sense. Could you please help if what I would like to do is actually possible?

[snb]

[VBA]Sub snb()
With createobject("scripting.filesystemobject")
.createtextfile("C:\text3.xml").write replace(.opentextfile("C:\text1.xml").readall,"</data>", "<details>" & split(.opentextfile("C:\text.xml").readall,"</details>")(0),"<details>")(1) & "</details>" & "</data>"
End With
End Sub [/VBA]