In recent post I talked about converting XML data to Excel table. But what should You do to just overwrite data in XML file, without converting or copying it into Excel?
In some cases there is no need to extract XML data into more user friendly Excel table format. For the application purpose, where You upload the XML file, You want to stay with original format of data. But still You need to check one or few things to be sure, that everything is correct.
You can do this manually. Open file, find the tag and its value.
However, You will not open manually 100 XML files or more and check one by one, when there is possibility of automation.
It’s OK, totally understandable. I got You buddy!
I brought here XML code, the same as I prepared recently.
<CATALOG> <PRODUCT name="A"> <FEATURE1>A1</FEATURE1> <FEATURE2>A2</FEATURE2> <FEATURE3>A3</FEATURE3> </PRODUCT> <PRODUCT name="B"> <FEATURE1>B1</FEATURE1> <FEATURE2>B2</FEATURE2> <FEATURE3>B3</FEATURE3> </PRODUCT> <PRODUCT name="C"> <FEATURE1>C1</FEATURE1> <FEATURE2>C2</FEATURE2> <FEATURE3>C3</FEATURE3> </PRODUCT> </CATALOG>
According to the XML code, imagine situation: You want to change every ‘2’ into ‘4’, in every feature, in every product. Remember that this is just the example.
Let’s do this!
First of all You need to have file path, then create XML object and load this file into object.
xmlfilename = "...\test.xml" Set xmlfile = CreateObject("Microsoft.XMLDOM") xmlfile.Load (xmlfilename)
Secondly You have to select node – if You know the structure of the file start with basic level names.
Set ProductNodes = xmlfile.SelectNodes("/CATALOG/PRODUCT")
If You don’t really know how it is leveled or You want to use ChildNodes from the beginning, just leave slash.
Set ProductNodes = xmlfile.SelectNodes("/")
After that the only thing left is to loop through elements. In this case You need 2 loops – firstly loop through products and secondly loop through its features. Features are Products ChildNodes, just take a look at the XML structure above.
For i = 0 To ProductNodes.Length - 1 For j = 0 To ProductNodes.Item(i).ChildNodes.Length - 1
Remember to start every loop from 0 to the last element minus one. Most of my errors in looping through ChildNodes were caused by forgetting to subtract 1 element (cause of the start from 0!!).
To check the value of the product child node – feature – use .Text property.
To replace every ‘2’ value in features I used Replace function.
Replace(ProductNodes.Item(i).ChildNodes(j).Text, "2", "4")
Key to success
The last thing, but probably most important in this case, is how to make all the changes permanent into the file. The thing is, You just need to save all the changes.
If You want to overwrite the file just put the same file path from the beginning. If not, change it.
Option Explicit Sub main() Dim xmlfile As Object Dim xmlfilename As String, excelfilename As String Dim prod_type As String Dim ProductNodes As Object Dim i As Long, j As Long xmlfilename = "...\test.xml" Set xmlfile = CreateObject("Microsoft.XMLDOM") xmlfile.Load (xmlfilename) Set ProductNodes = xmlfile.SelectNodes("/CATALOG/PRODUCT") For i = 0 To ProductNodes.Length - 1 For j = 0 To ProductNodes.Item(i).ChildNodes.Length - 1 If InStr(1, ProductNodes.Item(i).ChildNodes(j).Text, _ "2") Then ProductNodes.Item(i).ChildNodes(j).Text = _ Replace(ProductNodes.Item(i).ChildNodes(j).Text, "2", "4") End If Next Next Call xmlfile.Save(xmlfilename) Msgbox "Done" End Sub
This was simple code, but worth to describe, I think. Now I hope You know how to overwrite data in XML file without copying, converting or even opening any single XML file. Just remember those few tips I mentioned above and You will manage to do it by yourself.
9 thoughts on “How to overwrite data in XML file?”
Thanks for the great article, it’s really helpful.
Would like to check if any way I can locate the specific attribute value then amend it’s child text?
Below is the part of the data I have in xml, it has many xCoord & yCoord (more than 1K), and I already have idea which X & Y coordinate I want to change their defect, not sure if any quick way other than run from line 1 to end?
Thanks a lot for the help.
I would go from first to the last, but maybe there is faster approach to get specified tag based on its property 🙂
Thank you for making this article.
I have multiple large sized .XML files in a folder that requires rows (or nodes) being deleted if a specific string is found in a specific column (if the XML is viewed in a dataframe perspective).
Hope you can assist.
So what have You done already?
Do You have any issue yet? 🙂
How can I adapt for this code instead of txt to be xml and in utf-8:
Set fso = CreateObject(“Scripting.FileSystemObject”)
Set Fileout = fso.CreateTextFile(“G:\Mi unidad\PC\Documentos\” & nombre_txt, True)
Have You tried to include the file name & xml extension inside the CreateTextFile function?
if name_txt=name.xml but in the output I don’t know if I need to add that it has utf-8 encoding
Probably this is what You need to include inside your xml