How to push web service data onto an ION meter
PME, ION Enterprise
Designer VIP module
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.
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¶meter2=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:
Once you have WGET on your computer, you can issue
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
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
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
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
Some tips on setting up the XML import:
- It's a good idea to set up a Vista diagram with a control object on the XML import module's 'ReadNow' input.
- 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
- 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.
- 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.