Edit XML TAG Contents and Decimal Places

[Fraz]

Hi guys,

I am hoping someone can help me with automating editing XML files using AUTOIT. I have some XML for example:-

 <TotalPayments>265.52</TotalPayments>

  <CostOfMaterials>0.10</CostOfMaterials>

  <TotalDeducted>331.92</TotalDeducted>

 

 

And I need to edit all occurences of the above tags to change the pence values to '00'. So I would want this to be changed into:-

 <TotalPayments>265.00</TotalPayments>

  <CostOfMaterials>0.00</CostOfMaterials>

  <TotalDeducted>331.00</TotalDeducted>

 

Any help appreciated.

[Melba23]

Fraz,

Welcome to the AutoIt forum.

Just the thing for a RegEx:

$sString = "<TotalPayments>265.52</TotalPayments>" & @CRLF & _
    "<CostOfMaterials>0.10</CostOfMaterials>" & @CRLF & _
    "<TotalDeducted>331.92</TotalDeducted>"

$sModdedString = StringRegExpReplace($sString, "(?=.)(\d\d)(?=<)", "00")

MsgBox(0, "Modded", $sModdedString)

SRER decode:

(?=.)   - Must be preceded by .
(\d\d)  - Must be 2 digits (this is the capturing group)
(?=<)   - Must be followed by < 

00      - If all of the above are true, replace the 2 digits with 00

All good?

M23

[Fraz]

Hi M23,

Wow your fast

just a quick question though instead of loading :-

$sString = "<TotalPayments>265.52</TotalPayments>" & @CRLF & _
    "<CostOfMaterials>0.10</CostOfMaterials>" & @CRLF & _
    "<TotalDeducted>331.92</TotalDeducted>"

 

Should I not be loading my xml file path? Also their could be multiple <TotalPayments> tags would this code handle them all?

Thanks again M23.

[Melba23]

Fraz,

You will need to read the file into a variable using FileRead and put that variable into the RegEx in place of $sString.

And the code will handle all double digit instances preceded by a "." and followed by a "<" - that is one of the wonders of a RegEx.

M23

[Fraz]

Thanks M23 il give this a go

[jdelaney]

XMLDOM route:

#include <File.au3>
$oXML = ObjCreate("Microsoft.XMLDOM")

$sXML_File = @DesktopDir & "\temp.xml"
$sSampleXML = "<SomeParent><TotalPayments>265.52</TotalPayments><CostOfMaterials>0.10</CostOfMaterials><TotalDeducted>331.92</TotalDeducted></SomeParent>"
_FileCreate($sXML_File)
FileWrite($sXML_File,$sSampleXML)

$oXML.load($sXML_File)
$oTotalPayments     = $oXML.selectSingleNode("//TotalPayments")
$oCostOfMaterials   = $oXML.selectSingleNode("//CostOfMaterials")
$oTotalDeducted     = $oXML.selectSingleNode("//TotalDeducted")

$oTotalPayments.text    = StringFormat('%.2f',Floor($oTotalPayments.text))
$oCostOfMaterials.text  = StringFormat('%.2f',Floor($oCostOfMaterials.text))
$oTotalDeducted.text    = StringFormat('%.2f',Floor($oTotalDeducted.text))

; or, use regexpreplace
;~ $oTotalPayments.text = StringRegExpReplace($oTotalPayments.text,"(\.\d{2})",".00")
;~ $oCostOfMaterials.text   = StringRegExpReplace($oCostOfMaterials.text,"(\.\d{2})",".00")
;~ $oTotalDeducted.text = StringRegExpReplace($oTotalDeducted.text,"(\.\d{2})",".00")

ConsoleWrite($oXML.xml & @CRLF)
$oXML.save($sXML_File)

output:

<SomeParent><TotalPayments>265.00</TotalPayments><CostOfMaterials>0.00</CostOfMaterials><TotalDeducted>331.00</TotalDeducted></SomeParent>

another option of looping through the nodes of a parent node

#include <File.au3>
$oXML = ObjCreate("Microsoft.XMLDOM")

$sXML_File = @DesktopDir & "\temp.xml"
$sSampleXML = "<SomeParent><TotalPayments>265.52</TotalPayments><CostOfMaterials>0.10</CostOfMaterials><TotalDeducted>331.92</TotalDeducted></SomeParent>"
_FileCreate($sXML_File)
FileWrite($sXML_File,$sSampleXML)

$oXML.load($sXML_File)

$oNodes = $oXML.selectNodes("/SomeParent/*")
For $oNode In $oNodes
    $oNode.text = StringFormat('%.2f',Floor($oNode.text))
Next
ConsoleWrite($oXML.xml & @CRLF)
$oXML.save($sXML_File)

Edited April 4, 2014 by jdelaney

[Fraz]

Hi JDelaney,

Thanks for the solution, I have tried your solution but seem to be going wrong somewhere, I have changed some code so that :-

2.xml is the output XML file

1.xml is my original XML file

I added in a FileRead but couldnt get it to work, but have since taken it out of the below code.

But the program doesnt create the new xml file successfully

My altered code:-

#include <File.au3>
#include <_XMLDomWrapper.au3>
$oXML = ObjCreate("Microsoft.XMLDOM")

$sXML_File = "c:\Fraz\SAP\AutoIT\2.xml"
$sSampleXML = "c:\Fraz\SAP\AutoIT\1.xml"
_FileCreate($sXML_File)
FileWrite($sXML_File,$sSampleXML)

$oXML.load($sXML_File)
$oTotalPayments     = $oXML.selectSingleNode("//TotalPayments")
$oCostOfMaterials   = $oXML.selectSingleNode("//CostOfMaterials")
$oTotalDeducted     = $oXML.selectSingleNode("//TotalDeducted")

$oTotalPayments.text    = StringFormat('%.2f',Floor($oTotalPayments.text))
$oCostOfMaterials.text  = StringFormat('%.2f',Floor($oCostOfMaterials.text))
$oTotalDeducted.text    = StringFormat('%.2f',Floor($oTotalDeducted.text))

; or, use regexpreplace
;~ $oTotalPayments.text = StringRegExpReplace($oTotalPayments.text,"(\.\d{2})",".00")
;~ $oCostOfMaterials.text   = StringRegExpReplace($oCostOfMaterials.text,"(\.\d{2})",".00")
;~ $oTotalDeducted.text = StringRegExpReplace($oTotalDeducted.text,"(\.\d{2})",".00")

ConsoleWrite($oXML.xml & @CRLF)
$oXML.save($sXML_File)

Edited April 7, 2014 by Fraz

[Fraz]

Hi guys,

Can anyone tell me where im going wrong?

Thanks,

Fraz

[mikell]

Melba,

Typo ... positive look-behind (?<=.)

Obviously depending on the rest of the text, this should work

$sModdedString = StringRegExpReplace($sString, "\.\d+", ".00")

Edit

Being very selective would need

$sModdedString = StringRegExpReplace($sString, "(TotalPayments|CostOfMaterials|TotalDeducted)(>\d+)\.\d+", "$1$2.00")

Edited April 11, 2014 by mikell

[Fraz]

Hi Mikell,

I think the solutions kindly provided by M23 and JDelaney work, but i just cant get the script to work with my existing xml file which needs to be changed.

So i need to find out how to change JDelaney's script so that I can get the script to change the existing xml file.

Thanks,

Fraz

[jdelaney]

There is no need for the file write.  The data in the file is not changed until you do the xml.save...so, you can load in the second file, and save to the first file.  Without the xml, I can't help you debug...does this output anything?  If not, it's not valid xml:

ConsoleWrite($oXML.xml & @CRLF)

[Fraz]

Hi jdelaney,
 
After changing the sample xml to $sSampleXML = "c:FrazSAPAutoIT1.xml"
 
The output error is :- C:FrazSAPAutoITXML EDIT.au3 (15) : ==> The requested action with this object has failed.:
$oTotalPayments.text    = StringFormat('%.2f',Floor($oTotalPayments.text))
$oTotalPayments.text    = StringFormat('%.2f',Floor($oTotalPayments.text^ ERROR
>Exit code: 1    Time: 0.236

The new XML File is created (2.xml) but its completely empty.
 
The XML file that i am trying to edit is attached.
 
Thanks,
 
Fraz

1.XML

Edited April 14, 2014 by Fraz

[mikell]

I really don't understand why you want to complicate, as these 3 lines work nice on the xml you posted

$sString = FileRead("1.xml")
$sModdedString = StringRegExpReplace($sString, "(TotalPayments|CostOfMaterials|TotalDeducted)(>\d+)\.\d+", "$1$2.00")
FileWrite("2.xml", $sModdedString)

[Fraz]

Thanks Mikell,

Works a treat, sorry I didnt realise how to get your suggested code to update my xml until your latest post.

Thank you all for your help