question Re: Read xml file content into an Attribute: How to? in Support Questions

Read xml file content into an Attribute: How to?

rafy — Thu, 08 Sep 2022 04:46:51 GMT

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 .

Re: Read xml file content into an Attribute: How to?

araujo — Tue, 06 Sep 2022 21:23:15 GMT

@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é

Re: Read xml file content into an Attribute: How to?

rafy — Tue, 06 Sep 2022 21:44:38 GMT

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.

Re: Read xml file content into an Attribute: How to?

araujo — Tue, 06 Sep 2022 22:24:04 GMT

@rafy ,

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

Cheers,

André