We were recently asked by one of our System Engineers how PI Data could be transformed into the XML format.

The request was pretty general and there are likely dozens of ways one could do this. However, Moritz and I came together and created a few examples for your reference.

 

The Developer Technologies and Developer Tools we've chosen, recommended themselves from the simplicity perspective. We decided to offer examples for the following combination of Developer Technology and Developer Tool.

 

  1. AF SDK using PowerShell
  2. OSIsoft.PowerShell module in PowerShell
  3. PI Web API in C# (Visual Studio .NET)

 

Example 1: AF SDK using PowerShell

The first example allows specifying multiple PI Points and generates one output file for each PI Point specified.

For simplicity, the data retrieval period is limited to the past four hours from now. When you plan to retrieve data for larger periods, e.g. larger than 2 weeks, please consider adding some logic to break larger periods into smaller ones to avoid executing expensive queries.

 

# Load the AF SDK assembly through reflection
[Reflection.Assembly]::LoadWithPartialName("OSIsoft.AFSDK") | Out-Null

# Connect to the default PI Data Archive
[OSIsoft.AF.PI.PIServers] $piSrvs = New-Object OSIsoft.AF.PI.PIServers
[OSIsoft.AF.PI.PIServer] $piSrv = $piSrvs.DefaultPIServer

# Define array of PI Point Names
$piPointNames = @()
$piPointNames = $piPointNames + "SINUSOID"
$piPointNames = $piPointNames + "CDT158"

# Define the time range for data retrieval
[OSIsoft.AF.Time.AFTimeRange] $timeRange = New-Object OSIsoft.AF.Time.AFTimeRange("*-4h", "*")

# Create one XML file for each PI Point
foreach ($piPointName in $piPointNames)
{
    # Load PI Point 
    [OSIsoft.AF.PI.PIPoint] $piPoint = [OSIsoft.AF.PI.PIPoint]::FindPIPoint($piSrv, $piPointName)

    # Retrieve values
    [OSIsoft.AF.Asset.AFValues] $piValues = $piPoint.RecordedValues($timeRange, [OSIsoft.AF.Data.AFBoundaryType]::Inside, $null, $true, 1000)

    # Define output file path and name
    $OutputFile = "C:\Temp\XML\" + $piPointName + ".xml"

    # Inform the user
    Write-Host $piValues.Count " values will be written to " $OutputFile

    # Convert $piValues to XML 
    $XMLValues = ConvertTo-Xml -InputObject $piValues
    
    # Save the $XMLValues object to file
    $XMLValues.Save($OutputFile)
} 

 

 

Below is a screenshot illustrating the format of the resulting XML file. While the information of each event can be considered complete, it might be too much information for some purposes and we really don't need to fill our hard disk with more information than needed.

 

 

We decided offering you a modified script as an alternative. Again, it is possible to reference multiple PI Points but the output is written to a single file which uses the PI Data Archive's host name. To be able to define the resulting XML structure, we need to maintain the structure which is indeed kind of tedious and easily doubles the line of code compared to the very first example.

 

# Load the AF SDK assembly through reflection
[Reflection.Assembly]::LoadWithPartialName("OSIsoft.AFSDK") | Out-Null

# Connect to the default PI Data Archive
[OSIsoft.AF.PI.PIServers] $piSrvs = New-Object OSIsoft.AF.PI.PIServers
[OSIsoft.AF.PI.PIServer] $piSrv = $piSrvs.DefaultPIServer

# Define array of PI Point Names
$piPointNames = @()
$piPointNames = $piPointNames + "SINUSOID"
$piPointNames = $piPointNames + "CDT158"

# Define the time range for data retrieval
[OSIsoft.AF.Time.AFTimeRange] $timeRange = New-Object OSIsoft.AF.Time.AFTimeRange("*-4h", "*")

# Create Xml Document
$XmlDoc = New-Object System.Xml.XmlDocument
$xeSrv = $XmlDoc.CreateElement("PIDataArchive")
$xeSrv.SetAttribute("Name", $piSrv.Name)
$XmlDoc.AppendChild($xeSrv) | Out-Null
$XmlDeclaration = $XmlDoc.CreateXmlDeclaration("1.0", "UTF-8", $null) 
$XmlDoc.InsertBefore($XmlDeclaration, $xeSrv) | Out-Null

foreach ($piPointName in $piPointNames)
{
    # Load PI Point 
    [OSIsoft.AF.PI.PIPoint] $piPoint = [OSIsoft.AF.PI.PIPoint]::FindPIPoint($piSrv, $piPointName)

    # Create a Xml node with the PI Point name and append it
    $xePiPoint = $XmlDoc.CreateElement("PIPoint")
    $xePiPoint.SetAttribute("Name", $piPointName)
    $xeSrv.AppendChild($xePiPoint) | Out-Null

    # Retrieve values
    [OSIsoft.AF.Asset.AFValues] $piValues = $piPoint.RecordedValues($timeRange, [OSIsoft.AF.Data.AFBoundaryType]::Inside, $null, $true, 1000)

    # Inform the user
    Write-Host "Processing PI Point '$piPointName'. "$piValues.Count" values found .. "
    
    foreach ($piValue in $piValues)
    {
        # Create and append Xml node 'RecordedValue'
        $xeRecorded = $XmlDoc.CreateElement("RecordedValue")
        $xePiPoint.AppendChild($xeRecorded) | Out-Null

        # Append the Value
        $xeValue = $XmlDoc.CreateElement("Value")
        $xeRecorded.AppendChild($xeValue) | Out-Null
        $xtValue = $XmlDoc.CreateTextNode($piValue.Value.ToString())
        $xeValue.AppendChild($xtValue) | Out-Null

        # Append the Timestamp
        $xeTimestamp = $XmlDoc.CreateElement("Timestamp")
        $xeRecorded.AppendChild($xeTimestamp) | Out-Null
        $xtTimestamp = $XmlDoc.CreateTextNode($piValue.Timestamp.ToString())
        $xeTimestamp.AppendChild($xtTimestamp) | Out-Null

        # Append the Status
        $xeIsGood = $XmlDoc.CreateElement("IsGood")
        $xeRecorded.AppendChild($xeIsGood) | Out-Null
        $xtIsGood = $XmlDoc.CreateTextNode($piValue.IsGood.ToString())
        $xeIsGood.AppendChild($xtIsGood) | Out-Null
    }
}

# Define output file path and name
$OutputFile = "C:\Temp\XML\" + $piSrv.Name + ".xml"

# Save the Xml document 
$XmlDoc.Save($OutputFile) 
You will recognize the output looks totally different this time.

 

 

[Top of Page]

 

Example 2: OSIsoft.PowerShell module in PowerShell

When retrieving time series data via OSISoft.PowerShell module, you will recognize that you cannot use PITime format, at least not out of the box. This means references like "*-1d" to refer 1 day backwards in time from now, do not work unless you implement some logic to derive a real timestamp from a PITime expression. This pure PowerShell example is however the most simple one and shows less than 20 lines of code can do great things.

 

 

# Make connection to the default PI Data Archive
$DefaultConf = Get-PIDataArchiveConnectionConfiguration -Default
$connection = Connect-PIDataArchive -PIDataArchiveConnectionConfiguration $DefaultConf

# Define PI Point name
$piPointName = "SINUSOIDU"

# Retrieve list of events for the defined PI Point
$PIValues = Get-PIValue -PointName $piPointName -Connection $connection -StartTime "10-Apr-2018 6:00:00 AM" -EndTime "10-Apr-2018 12:00:00 PM" -MaxCount 1000

# Define output file and path 
$PathFileName = "C:\Temp\XML\" + $piPointName + ".xml"

# Inform the user
Write-Host $PIValues.Count " values will be written to " $PathFileName

# Write Xml file
Export-Clixml -Path $PathFileName -InputObject $PIValues

 

The resulting output is again Xml format but again with a different format. The interesting thing to me is that you get timestamp in UTC (underlined green) and local time (underlined blue). This can be pretty useful when troubleshooting e.g. Daylight Savings Time issues.

 

 

[Top of Page]

 

Example 3: PI Web API in C# (Visual Studio .NET)

The next example connects against our public PI Web API endpoint. Please refer linked document for additional information.

 

Have you ever wondered if PI Web API supports XML? Some of you may recall that this was a feature with the first Community Technology Preview (CTP) but was than skipped out since the first PI Web API release. I don't know the exact reason but could assume the decision was taken because it is so easy to turn JSON to XML. The following example shows that using JsonReaderWriterFactory class allows doing the conversion from JSON to XML within a single line of code.

 

While with previous examples, we have retrieved time series data, the following will work with any GET request or more precisely with any request which causes PI Web API to return a JSON response. For this reason we just stick with the base-Uri.

The resulting XML file will be created in the application folder.

 

using System;
using System.Net;
using System.Net.Http;
using System.Xml;


// Requires reference to .NET Framework library System.Runtime.Serialization 
using System.Runtime.Serialization.Json;


namespace piwebapi_to_XML
{
    class Program
    {
        static void Main(string[] args)
        {
            // Application defaults. Make your changes here 
            string uri = "https://devdata.osisoft.com/piwebapi";
            string userName = "webapiuser";
            string passPhrase = "!try3.14webapi!";
            string xmlFileName = "xmlDoc.xml";
            
            // Create a client handler and set the credentials 
            HttpClientHandler handler = new HttpClientHandler();
            NetworkCredential piWebAPICredential = new NetworkCredential(userName, passPhrase);
            handler.Credentials = piWebAPICredential;
            
            // Create the client using the handler 
            HttpClient client = new HttpClient(handler);
            
            // Execute query synchronous ( .Result will wait for the call to complete) 
            HttpResponseMessage response = client.GetAsync(uri).Result;
            
            // Create new empty XML Document 
            XmlDocument xmlDoc = new XmlDocument();
            
            // Does the StatusCode indicate success? 
            if (response.IsSuccessStatusCode)
            {
                // Read the content as byte array, again synchronous 
                byte[] content = response.Content.ReadAsByteArrayAsync().Result;
                
                // Convert and load the content to our XML Document  
                xmlDoc.Load(JsonReaderWriterFactory.CreateJsonReader(content, XmlDictionaryReaderQuotas.Max));
            }
            else
            {
                // In case of a bad StatusCode, add Status information to the XML Document 
                XmlElement badStatusE = xmlDoc.CreateElement("BadStatus");
                XmlElement stCodeE = xmlDoc.CreateElement("StatusCode");
                stCodeE.SetAttribute("Key", string.Format("{0}", (int)response.StatusCode));
                badStatusE.AppendChild(stCodeE);
                XmlElement description = xmlDoc.CreateElement("Description");
                description.SetAttribute("Value", response.StatusCode.ToString());
                badStatusE.AppendChild(description);
                xmlDoc.AppendChild(badStatusE);
            }
            // Save the result of query execution to file 
            xmlDoc.Save(xmlFileName);
            
            // We are done. Let's inform the user 
            Console.Write("Done. Press any key to quit .. ");
            Console.ReadKey();
        }
    }
}

 

For completeness, I am also including a screenshot of the XML file above code produces.

 

 

[Top of Page]