Merge a regular DB field with a xml DB field

[rgranado]

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!

[tnewbie]

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

[rgranado]

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> ....

[JR1]

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.