# .xml or .txt file automatically dumping to SQL

**URL:** <https://forum.inductiveautomation.com/t/xml-or-txt-file-automatically-dumping-to-sql/28200>\
**Category:** General Discussion\
**Created:** [September 12, 2019, 11:42am UTC](https://forum.inductiveautomation.com/t/xml-or-txt-file-automatically-dumping-to-sql/28200 "2019-09-12T11:42:10Z")\
**Posts on this page:** 4\
**Page:** 1

<div class="post-metadata">

**Author:** ![Micky.Franks](https://avatars.discourse-cdn.com/v4/letter/m/c57346/32.png) [@Micky.Franks](https://forum.inductiveautomation.com/u/Micky.Franks)\
**Post date:** [September 12, 2019, 11:42am UTC](https://forum.inductiveautomation.com/t/xml-or-txt-file-automatically-dumping-to-sql/28200/1 "2019-09-12T11:42:11Z")

</div>

I have an existing gate and RFID badge installation that I would like to integrate into Ignition. My options for what I have now is the gate software will continuously update a TXT ot XML file. Are there any options available to where I can get the data from ether of these files into a MySQL database to be used in Ignition other then having to manually importing it?

---

<div class="post-metadata">

**Author:** ![pturmel](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pturmel/32/3295_2.png) [@pturmel](https://forum.inductiveautomation.com/u/pturmel)\
**Post date:** [September 12, 2019, 12:07pm UTC](https://forum.inductiveautomation.com/t/xml-or-txt-file-automatically-dumping-to-sql/28200/2 "2019-09-12T12:07:32Z")

</div>

You could monitor the file for modification in a gateway timer script, and then parse it to update your DB. Java has pretty good built-in XML handling (usable from jython). What that script would look like would depend on the XML structure.

---

<div class="post-metadata">

**Author:** ![Micky.Franks](https://avatars.discourse-cdn.com/v4/letter/m/c57346/32.png) [@Micky.Franks](https://forum.inductiveautomation.com/u/Micky.Franks)\
**Post date:** [September 17, 2019, 6:48pm UTC](https://forum.inductiveautomation.com/t/xml-or-txt-file-automatically-dumping-to-sql/28200/3 "2019-09-17T18:48:40Z")

</div>

It looks like the attached file.

[Stream.xml](https://us1.discourse-cdn.com/inductiveautomation/original/2X/1/14a9abf0f6a0761c49e8132678e4cf4e6fcb033c.xml) (1.2 KB)

---

<div class="post-metadata">

**Author:** ![JordanCClark](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/jordancclark/32/83362_2.png) [@JordanCClark](https://forum.inductiveautomation.com/u/JordanCClark)\
**Post date:** [September 18, 2019, 1:20pm UTC](https://forum.inductiveautomation.com/t/xml-or-txt-file-automatically-dumping-to-sql/28200/4 "2019-09-18T13:20:45Z")

</div>

IIRC, MySQL has INSERT IGNORE, that you may be able to use to avoid duplicate inserts. I don’t have it around to test.

The XML is, thankfully, pretty straightforward and can use ElementTree easily enough.

```python
import xml.etree.ElementTree as ET

filepath = 'C:/Test/stream/Stream.xml'

# Load the element tree
tree = ET.parse(filepath)

# Grab the tree root. It's the outermost tag of the XML
# I like naming the root varible the same as root tag, as it helps
# remind me of the XML structure if I have to go back later. 
transactionFile = tree.getroot()

# Generate all lists to make datasets, or flat list for runPrepUpdate
# We're looping through everthing anyway, so we may as well do it once.
# 'data' will be a list of lists, 'flatList' will be a single list of all the values.
headers = [] # column names for either dataset or query
data = [] # to help create dataset
flatList = []	# for use in runPrepUpdate
for transaction in transactionFile:
	newRow = []
	for child in transaction:
		# add to headers if it doesn't already exist
		if child.tag not in headers:
			headers.append(child.tag)
		newRow.append(child.text)
		flatList.append(child.text)
	data.append(newRow)

# A dataset is not necessary for this script, but you never know. 
datasetOut = system.dataset.toDataSet(headers, data)
	
# Generate db query	
tableCols = '(' + ', '.join(headers) + ')'
valueString = ', '.join(['(' + ','.join('?' * len(headers)) + ')'] * len(data))

# INSERT IGNORE is a uniquely MySQL thing. YMMV.
query = 'INSERT IGNORE INTO table '+ tableCols + ' VALUES ' + valueString

# Run the query
system.db.runPrepUpdate(query, flatList)

```