Skip to main content
rafy
rafy
Gold ⭐️
September 6, 2022
Solved

Read xml file content into an Attribute: How to?

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 .

araujo
araujoAnswer
Platinum ⭐️⭐️⭐️
September 6, 2022

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

 

-- Was your question answered? Please take some time to click on "Accept as Solution" below this post. If you find a reply useful, say thanks by clicking on the thumbs up button.
View in thread

3 replies

araujo
Platinum ⭐️⭐️⭐️
araujoAnswer
Platinum ⭐️⭐️⭐️
September 6, 2022

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

 

Translate
-- Was your question answered? Please take some time to click on "Accept as Solution" below this post. If you find a reply useful, say thanks by clicking on the thumbs up button.
rafy
rafyAuthor
Gold ⭐️
September 6, 2022

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.

Translate
araujo
Platinum ⭐️⭐️⭐️
araujo
Platinum ⭐️⭐️⭐️
September 6, 2022

@rafy ,

 

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

 

Cheers,

André

 

Translate
-- Was your question answered? Please take some time to click on "Accept as Solution" below this post. If you find a reply useful, say thanks by clicking on the thumbs up button.
Terms & ConditionsAccessibility statement