Issue
How to push web service data onto an ION meter

Product Line
PME, ION Enterprise

Environment
Designer VIP module

Cause
The VIP module in ION:Enterprise includes an XML import module that allows you to read and parse XML files and push them directly to an ION meter. When this is combined with XML retrieval from a web service or database query on a scheduled task, you can push practically any data onto a meter where it can be logged, displayed, or used like any other measurement. The current worked example shows how to retrieve data from a web service, and also from an EEM database. A quick internet search for 'web services' will turn up hundreds of freely available sources of data.

Resolution

Part 1: Downloading and preparing data
Case 1: Retrieving data from a web service

There are many web services freely available for general use with publicly available information. They often provide HTTP GET access, which lets you craft a special URL that will retrieve values using a normal browser request. These URLs typically supply any needed parameters using a "?parameter1=value1&parameter2=value2" type syntax. That, coupled with the availability of the WGET command in most Windows OS's (or available as a free third party utility) lets you fire off a URL request and save the output from the command line.

For example, to retrieve the current share price for Schneider Electric, in Euros, from the Paris stock exchange, use the URL:

• http://www.webservicex.net/stockquote.asmx/GetQuote?symbol=SU.PA

Once you have WGET on your computer, you can issue

• WGET http://www.webservicex.net/stockquote.asmx/GetQuote?symbol=SU.PA -q -O=raw.XML

on the command line to write the output to the file raw.XML. Note that for URLs that contain the ampersand character '&' that this symbol is significant in the Windows command line and must be escaped with a caret, like ^& if you are testing on the command line. This shouldn't be needed if run from a scheduled task.

Additional step: massaging the downloaded XML

• The XML import module in the VIP is very sensitive to the downloaded XML, and there are a number of elements it can't deal with well. After some experimentation, I found that I had to strip out the namespace declaration (the VIP can't deal with double quotes in XML) and also that some web services escape '<' and '>' characters as html, which they shouldn't. To do this, I used a third party version of the 'sed' stream editor, an old unix utility, that lets you batch process text files. Any stream editor would do. Here is an example of a sed script that strips out the XML namespace declaration and 'un-escapes' the angle brackets around XML tags:

  s/<?.*?>//
  s/ xmlns=\"http:\/\/www.webserviceX.NET\/\"//
  s/&lt;/</g
  s/&gt;/>/g
  
  I had to run the first two lines of this against other web service XML downloads to strip out the namespace values even if the angle brackets were OK. Note that the URI (the http: part) is specific to the service you're retrieving from and would have to be modified for your case.
  
  By placing the above commands in a file 'sedcommands.txt' and using

  • sed -f "C:\MyDirectory\sedcommands.txt" "C:\MyDirectory\raw.xml" > cooked.XML
  against the downloaded "raw.XML" file
  
  <?xml version="1.0" encoding="utf-8"?>
  <string xmlns="http://www.webserviceX.NET/">&lt;StockQuotes&gt;&lt;Stock&gt;&lt;Symbol&gt;SU.PA&lt;/Symbol&gt;&lt;Last&gt;47.45&lt;/Last&gt;&lt;Date&gt;10/8/2008&lt;/Date&gt;&lt;Time&gt;11:35am&lt;/Time&gt;&lt;Change&gt;-4.125&lt;/Change&gt;&lt;Open&gt;47.46&lt;/Open&gt;&lt;High&gt;53.27&lt;/High&gt;&lt;Low&gt;45.82&lt;/Low&gt;&lt;Volume&gt;3147618&lt;/Volume&gt;&lt;MktCap&gt;N/A&lt;/MktCap&gt;&lt;PreviousClose&gt;51.575&lt;/PreviousClose&gt;&lt;PercentageChange&gt;-8.00%&lt;/PercentageChange&gt;&lt;AnnRange&gt;51.125 - 100.91&lt;/AnnRange&gt;&lt;Earns&gt;0.00&lt;/Earns&gt;&lt;P-E&gt;N/A&lt;/P-E&gt;&lt;Name&gt;SCHNEIDER ELECTRI&lt;/Name&gt;&lt;/Stock&gt;&lt;/StockQuotes&gt;</string>

you get an output "cooked.xml" that looks like:

• <string><StockQuotes><Stock><Symbol>SU.PA</Symbol><Last>47.45</Last><Date>10/8/2008</Date><Time>11:35am</Time><Change>-4.125</Change><Open>47.46</Open><High>53.27</High><Low>45.82</Low><Volume>3147618</Volume><MktCap>N/A</MktCap><PreviousClose>51.575</PreviousClose><PercentageChange>-8.00%</PercentageChange><AnnRange>51.125 - 100.91</AnnRange><Earns>0.00</Earns><P-E>N/A</P-E><Name>SCHNEIDER ELECTRI</Name></Stock></StockQuotes></string>

with the angle brackets restored and the namespace descriptor stripped out. It is now ready for import by the XML import module in the VIP. Case 2: Retrieving data from a local database
It is also possible, on SQL server systems, to retrieve data from any database using the SQLCMD command line utility (formerly known as OSQL). SQL queries can be formatted for XML output right in the SQL (using the FOR XML clause) and command line options on SQLCMD can direct the output to a file. See the SQLCMD and SQL documentation for details, but here's an example of extracting weather data from an EEM system which is importing it using its own external data adapter:

The SQL query:

• Select EEMData.TimeStampUTC, EEMData.[value] as CurrentTempCelsius from EEM_Datalog EEMData where EEMData.datalogID =
  (
  SELECT     max(eem_datalog.DataLogID) as MaxDatalLog
  FROM         SMP_SourceMeasurement INNER JOIN
  SRC_Source ON SMP_SourceMeasurement.SourceID = SRC_Source.SourceID INNER JOIN
  RPT_Measurement ON SMP_SourceMeasurement.MeasurementID = RPT_Measurement.MeasurementID INNER JOIN
  EEM_DataLog ON SMP_SourceMeasurement.SourceID = EEM_DataLog.SourceID AND
  SMP_SourceMeasurement.MeasurementID = EEM_DataLog.MeasurementID
  WHERE     (SMP_SourceMeasurement.SourceID NOT IN
  (SELECT     SourceID
  FROM          SRC_Source AS SRC_Source_1
  WHERE      (Hidden = 1))) AND (SMP_SourceMeasurement.ImportEnabled = 1) AND (SRC_Source.ImportEnabled = 1) AND
  (RPT_Measurement.DisplayNameShort LIKE 'Wthr Temp (C)')
  )
  FOR XML auto
  :XML ON

The SQLCMD command line that invokes it (assuming it is in a file called XMLWeather.SQL:

• SQLCMD -S (local) -U IONEEM -P YourPassword -d ION_EEMData -h -1 -W -i "C:\MyDirectory\XMLWeather.sql" > "C:\MyDirectory\CurrentTemp.XML"

Part 2: XML Import to the VIP
Caution: If a VIP XML Import module is going to be loading a very large file, it is best to create a dedicated VIP for this purpose. Because all of the VIP's modules run in the same thread, a long file load will stop all other VIP modules from running until the file load is complete.

The XML import module in the VIP requires the name and path of the source file, and one or more XPATH style query descriptors to tell the module what part of the file to extract. There is a clear, brief introduction to XPATH at

• https://www.w3schools.com/xml/xpath_intro.asp

Some tips on setting up the XML import:

1. It's a good idea to set up a Vista diagram with a control object on the XML import module's 'ReadNow' input.
2. The XML read failure messages aren't very descriptive - you get the same error for a file not found that you get for bad path syntax in a found file
3. To see the status of the latest read attempt, look at the output register values (shift+click on the output triangle) then scroll to the bottom of the list to find the "Load Complete" and "XIM Event" fields. Load Complete is a counter of the successful loads.
4. Once you have the XML import working OK, send the output you're interested in to a 'Distributed Numeric' module, which connects to a register value on a meter on the network. This is where the 'push' is done. The Distributed Numeric module needs an 'activate' boolean, I just used an isolated convert module to provide 'true' all the time. The values are only pushed to the meter when they change in the VIP.

In the XML import, set the XIM URL to the path of the file containing the 'cleaned' XML, for example

• C:\PROGRAM FILES\SCHNEIDER ELECTRIC\STRUXUREWARE SOLUTIONS\EUR-CAD.XML

Leave the namespace blank
Set the xPath Query 1 to the path string that picks the value you want from the downloaded file. For the share price example above, the query is /string/StockQuotes/Stock/Last



Once all the pieces are working, create a scheduled task that runs the WGET and stream editor as needed to get the XML ready. I wouldn't do it much more frequently than every couple of minutes, but that's up to your application. In the VIP, use a Periodic Timer module to trigger the reads at the same frequency. The read values will propagate automatically down to the meter (you may want to create some external numeric modules to be the recipients) where they can be displayed, logged, included in arithmetic calculations, or whatever else you want.
To trigger the read, I used a periodic timer and an external pulse module (hooked to my Vista diagram for triggering manual reads for testing) and merged them with a pulse merge module.