Reading XML file using XML UDF 1.1.1.13

[ijourneaux]

I am trying to read an XML file that looks like the following. I am particularly interested in the ParameterNames and ParameterValues

 

I was able to read a simplier XML file using

$oXML.load("DataForwardSettings.xml")

Local $oInfos = $oXML.selectnodes("//Database")   ; or //Info or //Data//Info or //Values/Info

 but have not been able to read

<?xml version="1.0"?>
<Entities>
  <Entity RecordType="TrendData">
    <Property Name="AlarmLimitsSetNumber" IsReadOnly="False" ValueType="System.Int32">8</Property>
    <Property Name="AnalysisParamaterSetNumber" IsReadOnly="False" ValueType="System.Int32">8</Property>
    <Property Name="ParameterNames" IsReadOnly="True" IsList="True" ListType="List<string>" ValueType="Array" ArrayType="System.String" Count="12">System.Collections.Generic.List`1[System.String]<Data>OVERALL|PK-PK WAVEFORM|HFD|CREST FACTOR|SYNC 1-6|1X|2X|3X-4X|FTF|BSF|BPFO|BPFI</Data></Property>
    <Property Name="ParameterValues" IsReadOnly="True" IsList="True" ListType="List<float>" ValueType="Array" ArrayType="System.Single" Count="12">System.Collections.Generic.List`1[System.Single]<Data>0.04706,0.27951,0.02640,4.85608,0.03494,0.01727,0.02256,0.01993,0.00207,0.00060,0.00178,0.00221</Data></Property>
    <Property Name="NumberOfParameters" IsReadOnly="False" ValueType="System.Int32">12</Property>
    <Property Name="ModifiedSinceLastDataDump" IsReadOnly="False" ValueType="System.Boolean">False</Property>
    <Property Name="Load" IsReadOnly="False" ValueType="System.Single">0</Property>
    <Property Name="RPM" IsReadOnly="False" ValueType="System.Single">140.962</Property>
    <Property Name="Value" IsReadOnly="False" ValueType="System.Single">-1.1E-20</Property>
    <Property Name="SampleID" IsReadOnly="False" ValueType="System.Int32">-626794</Property>
    <Property Name="Timestamp_as_String" IsReadOnly="True" ValueType="System.String">8/18/2018 2:05:33 PM</Property>
    <Property Name="Timestamp_as_UInt" IsReadOnly="False" ValueType="System.UInt32">1534619133</Property>
    <Property Name="Timestamp" IsReadOnly="False" ValueType="System.DateTime">8/18/2018 2:05:33 PM</Property>
    <Property Name="StorageFlag" IsReadOnly="False" ValueType="Enum" EnumType="Emerson.CSI.DataImport.MHM.TrendDataStorageType" EnumValue="2">RPM_And_Overall</Property>
    <Property Name="Parents" IsReadOnly="False" IsList="True" ListType="List<string>" ValueType="Array" ArrayType="System.String" Count="5">System.Collections.Generic.List`1[System.String]<Data>Database=phmhmdb4ts;C:\RBMdbsrv\CustData\4ts_online_1807.rbm;-99|Area=4TS;-494|Equipment=4THTS;-712|MeasurementPoint=D39;-780|DataCollectionSet=Normal Collection Dryer Rolls;-783</Data></Property>
  </Entity>
</Entities>

I tried switching to the XML UDF but was alittle lost in how to use it. I am particularly interested in the ParameterNames and ParameterValues.

[TheXman]

Your XML data appears to be invalid.  Run it through an XML validator and it will point out your errors.  It is much easier to help you if you start with valid XML. 

Also, your Xpath query is looking for nodes that do not exist in your XML document.

Fix your XML, fix your Xpath query, see if your script works and, if not, come back with your questions.

Here is a snippet that will get you started.  It just loads the external XML file.  If you run it against the XML you supplied, you will see that it gets an error when loading the file.  That's because your XML has errors in it.

#include <Constants.au3>
#include <MyIncludes\xml\xml.au3>  ;<== CHANGE TO YOUR UDF LOCATION

example()

Func example()
    Local $oXml = _XML_CreateDOMDocument(Default)

    _XML_load($oXML,"<path to xml file>")
    If @error Then
        ConsoleWrite(StringFormat("@error = %s", @error) & @CRLF)
        Exit -1
    EndIf
EndFunc

 

[ijourneaux]

Unfortunately, I am not able to correct the XML file. I am trying to process XML files created by an 3rd party program.  I did load the XML to a web based validator and it gave this error

6:

83

The value of attribute "ListType" associated with an element type "Property" must not contain the '<' character.

That looks like only the first of several similar errors.

Since I can't modified the XML files that are created, is the only option I have to parse the file using brute force. My needs are relatively simple. I need to get a date time, the list of parameter names and the list of parameter values. perhaps the RPM value.

Appreciate your thoughts.

[TheXman]

Assuming that you use this abbreviated and validated XML file:

<?xml version="1.0"?>
<Entities>
  <Entity RecordType="TrendData">
    <Property Name="AlarmLimitsSetNumber" IsReadOnly="False" ValueType="System.Int32">8</Property>
    <Property Name="AnalysisParamaterSetNumber" IsReadOnly="False" ValueType="System.Int32">8</Property>
    <Property Name="ParameterNames" IsReadOnly="True" IsList="True" ListType="List-string" ValueType="Array" ArrayType="System.String" Count="12">System.Collections.Generic.List`1[System.String]<Data>OVERALL|PK-PK WAVEFORM|HFD|CREST FACTOR|SYNC 1-6|1X|2X|3X-4X|FTF|BSF|BPFO|BPFI</Data></Property>
    <Property Name="ParameterValues" IsReadOnly="True" IsList="True" ListType="List-float" ValueType="Array" ArrayType="System.Single" Count="12">System.Collections.Generic.List`1[System.Single]<Data>0.04706,0.27951,0.02640,4.85608,0.03494,0.01727,0.02256,0.01993,0.00207,0.00060,0.00178,0.00221</Data></Property>
    <Property Name="NumberOfParameters" IsReadOnly="False" ValueType="System.Int32">12</Property>
  </Entity>
</Entities>

The script below is one way of displaying the all of the attributes for each of a particular node.  In this case, I chose to show all of the attributes for each of the "Property" nodes.  You will need to make sure that save the XML snippet to a file and enter the path to the XML file where noted.  Hopefully this gives you a place to start and motivates you to dig into the XML.au3 UDF to figure out the functions you need to accomplish your goal.  If you run into obstacles, just come back and post what the obstacles are, and I'm sure that you will get more help.

expandcollapse popup

#include <Array.au3>
#include <MyIncludes\xml\xml.au3>

example()

Func example()
    Local $oXmlDoc
    Local $aAttributes
    Local $iNodeCount

    ;Create XML Document object and load XML file
    $oXmlDoc = _XML_CreateDOMDocument(Default)
    _XML_Load($oXmlDoc,"C:\Path\To\XML\file.xml")  ;<== ENTER XML FILE PATH HERE
    If @error Then
        ConsoleWrite(StringFormat("_XML_load error - @error = %s", @error) & @CRLF)
        Exit -1
    EndIf

    ;If no specified nodes exist, log error and exit
    If Not _XML_NodeExists($oXmlDoc, "//Property") Then
        ConsoleWrite("No specified nodes exist" & @CRLF)
        Exit -1
    EndIf

    ;Get number of Property nodes
    _XML_SelectNodes($oXmlDoc,"//Property")
    If @error Then
        ConsoleWrite(StringFormat("_XML_SelectNodes error - @error = %s", @error) & @CRLF)
        Exit -1
    EndIf
    $iNodeCount = @extended

    ;Loop thru each Property node
    For $i = 1 To $iNodeCount
        ;Get Property node's attributes and display them
        $aAttributes = _XML_GetAllAttrib($oXmlDoc, StringFormat("//Property[%s]", $i))
        If @error Then
            ConsoleWrite(StringFormat("_XML_GetAllAttrib error - @error = %s", @error) & @CRLF)
            Exit -1
        EndIf
        _ArrayDisplay($aAttributes, StringFormat("Node Attributes %s of %s", $i, $iNodeCount))
    Next
EndFunc

 

Edited September 25, 2018 by TheXman

[TheXman]

2 minutes ago, ijourneaux said:

Unfortunately, I am not able to correct the XML file. I am trying to process XML files created by an 3rd party program.

Like you said, you could always scrub the file first.  This could be done manually or it could be done in your script.  It only appears to be 1 type of error, found several times.  You could easily search and replace to remove those errors or change them in a way that makes them valid.  I just changed "List<string>" to "List-string".

[ijourneaux]

Really appreciate the tips. Replacing List<string>  should be straight forward. Also very much appreciate the sample code. That may be more than enough for my use case.

[TheXman]

You're welcome.

"List<string>" was just one of the errors of that type.  The actual issue is that "<" and ">" are not valid within XML attribute values.  So if you remove them, you should be good to go.

 

[ijourneaux]

Ok that all worked. Unfortuantely I can't seem to figure out the syntax to read the <Data></Data> block.  from the Count attribute, I know there are 2 fields in the data but can't seem to get farther than that 

[TheXman]

Change all references from //Property to //Data.  For example:

If Not _XML_NodeExists($oXmlDoc, "//Data")

 

Edited September 25, 2018 by TheXman

[TheXman]

I just looked at the XML.  The <Data> node does not have any attributes.  Note that he logic I gave you only shows the attributes for specified nodes, It does not show the text value of the node.

[TheXman]

This snippet should display all <Data> values:

#include <Array.au3>
#include <MyIncludes\xml\xml.au3>

example()

Func example()
    Local $oXmlDoc
    Local $aData

    ;Create XML Document object and load XML file
    $oXmlDoc = _XML_CreateDOMDocument(Default)
    _XML_Load($oXmlDoc,"C:\Path\To\XML\file.xml")  ;<== ENTER XML FILE PATH HERE
    If @error Then
        ConsoleWrite(StringFormat("_XML_load error - @error = %s", @error) & @CRLF)
        Exit -1
    EndIf

    $aData = _XML_GetValue($oXmlDoc, "//Data")
    If IsArray($aData) Then _ArrayDisplay($aData)
EndFunc

 

[jdelaney]

An example of how to grab a specific Data node under a specific node/attribute pair:

$sXML = '<?xml version="1.0"?><Entities>  <Entity RecordType="TrendData">    <Property Name="AlarmLimitsSetNumber" IsReadOnly="False" ValueType="System.Int32">8</Property>    <Property Name="AnalysisParamaterSetNumber" IsReadOnly="False" ValueType="System.Int32">8</Property>    <Property Name="ParameterNames" IsReadOnly="True" IsList="True" ListType="List-string" ValueType="Array" ArrayType="System.String" Count="12">System.Collections.Generic.List`1[System.String]<Data>OVERALL|PK-PK WAVEFORM|HFD|CREST FACTOR|SYNC 1-6|1X|2X|3X-4X|FTF|BSF|BPFO|BPFI</Data></Property>    <Property Name="ParameterValues" IsReadOnly="True" IsList="True" ListType="List-float" ValueType="Array" ArrayType="System.Single" Count="12">System.Collections.Generic.List`1[System.Single]<Data>0.04706,0.27951,0.02640,4.85608,0.03494,0.01727,0.02256,0.01993,0.00207,0.00060,0.00178,0.00221</Data></Property>    <Property Name="NumberOfParameters" IsReadOnly="False" ValueType="System.Int32">12</Property>  </Entity>  </Entities>'
Local $oXML = ObjCreate("Microsoft.XMLDOM")
$oXML.LoadXML($sXML)
Local $oParementerNames = $oXML.selectSingleNode("//Property[@Name='ParameterNames']/Data")
ConsoleWrite($oParementerNames.text & @CRLF)
Local $oParementerValues = $oXML.selectSingleNode("//Property[@Name='ParameterValues']/Data")
ConsoleWrite($oParementerValues.text & @CRLF)

output:

OVERALL|PK-PK WAVEFORM|HFD|CREST FACTOR|SYNC 1-6|1X|2X|3X-4X|FTF|BSF|BPFO|BPFI
0.04706,0.27951,0.02640,4.85608,0.03494,0.01727,0.02256,0.01993,0.00207,0.00060,0.00178,0.00221

Edited September 25, 2018 by jdelaney

[ijourneaux]

That provided the details I needed. Thanks you

Local $oParementerNames = $oXmlDoc.selectSingleNode("//Property[@Name='ParameterNames']/Data")
    ConsoleWrite($oParementerNames.text & @CRLF)
    Local $oParementerValues = $oXmlDoc.selectSingleNode("//Property[@Name='ParameterValues']/Data")
    ConsoleWrite($oParementerValues.text & @CRLF)
    Local $oParementerValues = $oXmlDoc.selectSingleNode("//Property[@Name='Parents']/Data")
    ConsoleWrite($oParementerValues.text & @CRLF)

 

[ijourneaux]

Ok I am making progress on my project.  I am trying to access the following XML file to correct the UnitsString text.

I successfully get the two recordtypes and their attribute  but when I change UnitsString, only UnitString in the first entity get updated. 

<?xml version="1.0"?><Entities>
  <Entity RecordType="Emerson.CSI.DataImport.MHM.WaveFormData">
    <Property Name="ActualDate" IsReadOnly="False" ValueType="System.DateTime">7/31/2018 10:34:14 PM</Property>
    <Property Name="UnitsString" IsReadOnly="False" ValueType="System.String">G's</Property>
    <Property Name="MeasurementUnits" IsReadOnly="False" ValueType="Enum" EnumType="Emerson.CSI.DataImport.MHM.UnitsType" EnumValue="3">evAcceleration</Property>
  </Entity>
  <Entity RecordType="Emerson.CSI.DataImport.MHM.SpectraData">
    <Property Name="UnitsString" IsReadOnly="False" ValueType="System.String"></Property>
    <Property Name="MeasurementUnits" IsReadOnly="False" ValueType="Enum" EnumType="Emerson.CSI.DataImport.MHM.UnitsType" EnumValue="3">evAcceleration</Property>
    <Property Name="ActualDate" IsReadOnly="False" ValueType="System.DateTime">7/31/2018 10:34:14 PM</Property>
  </Entity>
</Entities>

expandcollapse popup

#Region ;**** Directives created by AutoIt3Wrapper_GUI ****
#AutoIt3Wrapper_UseX64=n
#EndRegion ;**** Directives created by AutoIt3Wrapper_GUI ****
#Include <File.au3>
#Include <Array.au3>
#include <Misc.au3>
#include <date.au3>
#include <StringConstants.au3>

;#include <D:\AutoIt Projects\AutoItUDF\XML_1.1.1.13\xml.au3>
#include <C:\Documents\AutoItUDF\XML_1.1.1.13\xml.au3>
;
; Set scheduled task for Delete Files to start ~5min before the start time of the transfer files task (currently at 15 past hour).
; Delete Files runs for 1435min per day, Scheduled tasks creates a new copy of DeleteFiles once per day.
;

Global $Timer = TimerInit()
Global $MaxTime = 10 * 1000 * 60 * 1435 ; 24hr x 60minutes - 5

;Const $xml_filepath = "D:\RBMnet\RBMsuite\config\DataImport\DataForwardExport"
Const $xml_filepath = "C:\Documents\AutoItProjects\Parsexmlfiles\Test Data"
CorrectXMLMissingUnitsString($xml_filepath & "\testUnitString.xml", "Other")
Exit

Func CorrectXMLMissingUnitsString($sFileXML, $sType)
    Local $oXmlDoc
    Local $aAttributes
    Local $oProperties
    LOcal $oProperty
    Local $iNodeCount
    Local $sString

    ;Create XML Document object and load XML file
    $oXmlDoc = _XML_CreateDOMDocument(Default)
    _XML_Load($oXmlDoc,$sFileXML)  ;<== ENTER XML FILE PATH HERE
    If @error Then
        ConsoleWrite(StringFormat("_XML_load error - @error = %s", @error) & @CRLF)
        ConsoleWrite("-" & $sFileXML & @CRLF)
        Exit -1
    EndIf

    ;If no specified nodes exist, log error and exit
    If Not _XML_NodeExists($oXmlDoc, "//Property") Then
        ConsoleWrite("No specified nodes exist" & @CRLF)
        Exit -1
    EndIf

    ;Get number of Property nodes
    $oEntities= _XML_SelectNodes($oXmlDoc,"//Entity")
    $iNodeCount = @extended
;   Consolewrite("Nodes: " & $inodecount & @crlf)
    For $oEntity In $oEntities
         $sString = ""
         $recordtype = _XML_GetNodeAttributeValue($oEntity, "RecordType")
         Consolewrite($RecordType & @crlf)
          $UnitsString= $oEntity.SelectSingleNode("//Property[@Name='UnitsString']")
          $MeasurementUnits= $oEntity.SelectSingleNode("//Property[@Name='MeasurementUnits']")

;       $UnitsString = _XML_SelectNodes($oEntity, "//Property[@Name='UnitsString']")
;       $MeasurementUnits =  = _XML_SelectNodes($oEntity, "//Property[@Name='MeasurementUnits']")
        Consolewrite(">" & $UnitsString.text & "<" &@crlf)
        Consolewrite(">>" & $MeasurementUnits.text & "<" &@crlf)
        $sString = StringStripWS ( $UnitsString.text, $STR_STRIPALL)
;       Consolewrite(">" & $sString & "<" &@crlf)
        if(StringLen($sString) = 0) then
            if($MeasurementUnits.text = "evAcceleration") Then
               if(stringinstr($RecordType, "WaveFormData")) then
                  $UnitsString.text = "G's"
               elseif(stringinstr($recordType, "SpectraData")) then
                  $unitsString.text = "rms G's"
               EndIf
            endif
         Else
            consolewrite( $sString & "-" & stringlen($sstring)&@crlf)
        endif
        Consolewrite(">" & $UnitsString.text  & "<" &@crlf)
        Consolewrite("----------------------" & @crlf)
     Next

    $oXmlDoc.Save($sFileXML)
    consolewrite($sFileXML &@crlf)
EndFunc

 

Edited October 11, 2018 by ijourneaux

[ijourneaux]

Ok.  I was able to get something that works but if I am using $oEntity in $oEntities, I wouldn't have thought I needed to the record type when trying to access UnitsString in the specific $oEntity.

             $UnitsString= $oEntity.SelectSingleNode("//Entity[@RecordType='" & $recordtype & "']/Property[@Name='UnitsString']")
             $MeasurementUnits= $oEntity.SelectSingleNode("//Entity[@RecordType='" & $recordtype & "']/Property[@Name='MeasurementUnits']")
 

[jdelaney]

You don't.   but you have to specify where your xpath starts.   you are using //, which tells to start from the root node.   what you want is to look at the children of the node. ..which means you need to include a period first. ..like

oEntity.selectsinglenode ("./Property....

The period signals that the xpath is relative to the node: oEntity

Sorry, mobile is frustrating.  I can provide full example in a few hours.

Edited October 11, 2018 by jdelaney

[ijourneaux]

Thanks for the help. I understand what you mean. Just need to figure out the syntax.