topic Re: Merge a regular DB field with a xml DB field in Talend Studio

Merge a regular DB field with a xml DB field

rgranado — Wed, 14 Aug 2019 18:09:35 GMT

Hi anyone,

In one record, it contains a regular DB field and an XML content DB field. The regular DB field contains the key and would like to merge it with the XML content DB field.

Example:

INPUT

Regular Field XML Field

999999 <?xml version="1.0" encoding="utf8"?> .....

999998 <?xml version="1.0" encoding="utf8"?> .....

OUTPUT (Merged)

XML Field

<?xml version="1.0" encoding="utf8"?><Key> 999999 </Key>

<?xml version="1.0" encoding="utf8"?><Key> 999998 </Key>

Do you have a solution for it? Thanks for your help!

Re: Merge a regular DB field with a xml DB field

tnewbie — Thu, 15 Aug 2019 14:43:50 GMT

Not sure if i am missing something here...it should be simple. The only catch in the solution i am proposing is i am writing to a file, you may have to extrapolate to an xml... i can not write to an xml because i don't know what your output xml looks like.

solution.docx

Re: Merge a regular DB field with a xml DB field

rgranado — Thu, 15 Aug 2019 16:09:26 GMT

Hi,

I am expecting to insert the DB postgres column 1 to column 2 (xml format). Example is below:

INPUT (DB Postgres)

Column 1 Column 2 (in XML format)

999999 <?xml version="1.0" encoding="utf8"?> <procedure_nbr>82.111111</procedure_nbr> ....

999998 <?xml version="1.0" encoding="utf8"?> <procedure_nbr>82.222222</procedure_nbr> ....

OUTPUT (DB Postgres)

Column 1 Column 2 (in XML format)

999999 <?xml version="1.0" encoding="utf8"?> <key>999999</key> <procedure_nbr>82.111111</procedure_nbr> ....

999998 <?xml version="1.0" encoding="utf8"?> <key>999998</key> <procedure_nbr>82.222222</procedure_nbr> ....

Re: Merge a regular DB field with a xml DB field

JR1 — Sat, 17 Aug 2019 21:10:48 GMT

tnewbie's solution is pretty close. Basically, you are smuggling in a new XML tag into a string - not pretty but can be effective. Instead of

row1.xmlfield+"<KEY> "+row.dbfield+" </key>"

you should use (exactly like this, assuming xmlField and dbField are never null)

row1.xmlField.replaceFirst(">", "><key>" + row1.dbField + "</key>")

Please accept my advice: always pay attention to case sensitivity (e.g. KEY vs. key) - it might bite you later in places or at times you did not expect. I have been there.