Parse xml data to SQLTags

I have an application where we’re logging data from a wind generator. While they’s working on adding features to their web appliance in the inverter, we can get data out of it in an xml format:

<response> <exportstate>Export 105 W </exportstate> <gridvsetting> 208.0 V</gridvsetting> </response>

Like I said, not much to it, yet. :wink:

So, while waiting for firmware updates, I wrote some code to parse the xml and write it to SQLTags. Easy enough, since there’s a total of two. Then I thouht “how cool would it be to dynamically add the tags, so when new items are added to the firmware, the tags are automatically created and populated?” Here’s the result:

[code]def xml(folderRoot,node,IP):
import system,re

folderRoot+="/"

# Check for folder exists
query="SELECT count(name) from sqlt_core WHERE name='"+node+"' and path='"+folderRoot+"' and deleted='0'"
SQLTagExists=system.db.runScalarQuery(query)

#Add folder if not exist
if SQLTagExists==0:
	query="INSERT INTO sqlt_core(name,path,drivername,tagtype,datatype,enabled,accessrights,scanclass,configchange,deleted)"+\
		"VALUES('"+node+"','"+folderRoot+"','WindGendrv','6','2','1','1','2',current_timestamp,0)"
	system.db.runUpdateQuery(query)

#Get XML Data from WindGen
path=folderRoot+node+"/"
url="http://"+IP+"/index.xml"
xmlIn=system.net.httpGet(url)

#Find all items in the xml file
patternString='.<.*?>*?</*?>'
pattern = re.compile(patternString, re.DOTALL)
match=pattern.findall(xmlIn)
if match:
	for x in match:
		#Extract the xml parameter name and value
		patternString='.<.*?>'
		pattern = re.compile(patternString, re.DOTALL)
		match1=pattern.search(x)
		nodeName=(match1.group())[1:]
		baseTagName=(match1.group())[2:-1].strip()
		tagName="[MySQL]"+path+(match1.group())[2:-1].strip()
		value=(x.replace("/","")).replace(nodeName,"")[1:].strip()
		
		#find out if a current tag exists
		query="SELECT count(name) from sqlt_core WHERE name='"+baseTagName+"' and path='"+path+"' and deleted='0'"
		SQLTagExists=system.db.runScalarQuery(query)
		
		if SQLTagExists==0:  #If no tag exists, add it to the database
			query="INSERT INTO sqlt_core(name,path,drivername,tagtype,datatype,enabled,accessrights,scanclass,configchange,deleted)"+\
				"VALUES('"+baseTagName+"','"+path+"','WindGendrv','1','7','1','1','2',current_timestamp,0)"
			system.db.runUpdateQuery(query)
		else: #If the tag exists
			#Update the value...
			query="UPDATE sqlt_core SET StringValue='"+value+"', valuechange=current_timestamp, dataintegrity='192' "+\
			"WHERE name='"+baseTagName+"' and path='"+path+"'"
			system.db.runUpdateQuery(query)
			#...then update the executon table so we don't get any stale tags
			query="UPDATE sqlt_sci SET lastexecrate=1000, lastexec=current_timestamp, nextexec=date_add(current_timestamp,interval 1 second) "+\
			"WHERE driverName='WindGendrv'"
			system.db.runUpdateQuery(query)
else: #If no xml data exists
	pass

[/code]

A note here: I create the tag first, but not populate/update at the same time, because I would get an error that I don’t quite remember. But since I’m running this once a second, the inconvenience of a null value is negligible.

The above script is not exactly efficient in terms of updating tag values (since I have a total of two, it’s not a big deal). This updated script will collate the tags into a single query.

[code]import system,re

folderRoot=“WindGen/”
node=“WindGen1”
IP=“192.168.140.68”

Check for folder exists

query=“SELECT count(name) from sqlt_core WHERE name=’”+node+"’ and path=’"+folderRoot+"’ and deleted=‘0’"
SQLTagExists=system.db.runScalarQuery(query)

#Add folder if not exist
if SQLTagExists==0:
query=“INSERT INTO sqlt_core(name,path,drivername,tagtype,datatype,enabled,accessrights,scanclass,configchange,deleted)”+
“VALUES(’”+node+"’,’"+folderRoot+"’,‘WindGendrv’,‘6’,‘2’,‘1’,‘1’,‘2’,current_timestamp,0)"
system.db.runUpdateQuery(query)

#Get XML Data from WindGen
path=folderRoot+node+"/"
url=“http://”+IP+"/index.xml"
xmlIn=system.net.httpGet(url)

#Find all items in the xml file
patternString=’.<.?>?</?>’
pattern = re.compile(patternString, re.DOTALL)
match=pattern.findall(xmlIn)
tagUpdateQuery=“UPDATE sqlt_core SET StringValue=CASE’”
tagcount=0
if match:
for x in match:
#Extract the xml parameter name and value
patternString=’.<.
?>’
pattern = re.compile(patternString, re.DOTALL)
match1=pattern.search(x)
nodeName=(match1.group())[1:]
baseTagName=(match1.group())[2:-1].strip()
tagName="[MySQL]"+path+(match1.group())[2:-1].strip()
value=(x.replace("/","")).replace(nodeName,"")[1:].strip()

	#find out if a current tag exists
	query="SELECT count(name) from sqlt_core WHERE name='"+baseTagName+"' and path='"+path+"' and deleted='0'"
	SQLTagExists=system.db.runScalarQuery(query)
	
	if SQLTagExists==0:  #If no tag exists, add it to the database
		query="INSERT INTO sqlt_core(name, path, drivername, tagtype, datatype, enabled, accessrights, scanclass, configchange, deleted)"+\
			"VALUES('"+baseTagName+"','"+path+"','WindGendrv','1','7','1','1','2',current_timestamp,0)"
		system.db.runUpdateQuery(query)
	else: #If the tag exists
		#Add a case to the query
		tagUpdateQuery+=" WHEN (name='"+baseTagName+"') THEN "+value
		tagcount +=1
	
		#...then update the executon table so we don't get any stale tags
if tagcount>0:
	#finish off the rest of the query
	tagUpdateQuery+=" ELSE StringValue END, valuechange=current_timestamp, dataintegrity='192' "+"WHERE path='"+path+"'"
	#update the tag values	
	system.db.runUpdateQuery(tagUpdateQuery)
	query="UPDATE sqlt_sci SET lastexecrate=1000, lastexec=current_timestamp, nextexec=date_add(current_timestamp,interval 1 second) "+\
	"WHERE driverName='WindGendrv'"
	system.db.runUpdateQuery(query)

else:
	pass

else:
pass
[/code]

Awesome Jordan. That’s the sort of automated time saving that I like to do. And the fact that Ignition allows us to do this sort of thing is even more awesome.

Next step would be to build the display dynamically based on available tags. :slight_smile: