- last edited on
Please i need your kind help.
SCENERIO: INSERT CONTENT OF AN XML FILE AS A FIELD INTO A DATABASE
- I am trying to read the content of an xml file into a flowfile attribute
- I want to use the attribute as variable in sql query statement to insert the content into a DB
- I tried to use both Xpath & Xquery but no success.
DATA: The data i am trying to convert is as below
<?xml version='1.0' encoding='utf-8'?>
<database xmlns='http://www.lotus.com/dxl' version='9.0' maintenanceversion='1.0'
replicaid='8525886700702AFD' path='BStravel2.nsf' title='BSTravel_Expenses_and_Vacations'
maintainunread='false' optimizetablebitmaps='true' increasemaxfields='true'
compressdesign='true' compressdata='true' markmodifiedunread='false' logtransactions='false'
<databaseinfo dbid='85256700702AFD' odsversion='43' diskspace='261440'
<launchsettings><noteslaunch whenopened='openframeset' frameset='Launch' showaboutdocument='never'/></launchsettings></database>
Thank you so much for all your help .
You shouldn't need to extract the content as an attribute. Instead, use ReplaceText to replace the contents of the flowfile with the SQL template, like this:
INSERT INTO my_table (xml_col) VALUES ('$1')
Where $1 is a reference to the default regex capture group that captures the entire content.
In reality, it requires a bit more labour, since you have to escape the single quotes in the XML first.
View solution in original post
Thanks for the insight Mr @araujo .
I would like to tell you i have thousands of that XML files. Please is there any better way of doing it.
Same approach. Just create the processor(s) and let the files flow through it 🙂