Read xml file content into an Attribute: How to?

[#1]

Hi, 

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

ISSUES:

- 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'?>
<!DOCTYPE database>
<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'
 uselz1='true'>
<databaseinfo dbid='85256700702AFD' odsversion='43' diskspace='261440'
 percentused='96.2421875' numberofdocuments='13'><datamodified><datetime
 dst='true'>20220813253,61-04</datetime></datamodified><designmodified
><datetime dst='true'>2022062936,80-04</datetime></designmodified></databaseinfo>
<launchsettings><noteslaunch whenopened='openframeset' frameset='Launch' showaboutdocument='never'/></launchsettings></database>

Thank you so much for all your help .

[#2 · Accepted solution]

@rafy 

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.

Cheers,

André

[#3]

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.

Thank you.

[#4]

@rafy ,

Same approach. Just create the processor(s) and let the files flow through it 🙂

Cheers,

André