Writing XML strings to Database

nsm · Post by **nsm** » Tue Nov 25, 2008 11:30 am

Hi,

My database guys wanted to write data into an XML string in the database.
For ex:

Key from Source will stay as Key in the target but all other attributes go as an XML String.

I think its doable but wanted to get opinions if anybody faced issues with it and what do I use : XML Transformer and XML Output stage?

Thanks
nsm.

chulett · Post by **chulett** » Tue Nov 25, 2008 11:40 am

Just XML Output, I would think. What database? What is the target field's datatype?

ArndW · Post by **ArndW** » Tue Nov 25, 2008 11:57 am

Oracle even has that datatype. One caveat is that if you want more than 2Kb of XML then you will have to use a CLOB or BLOB in Oracle or comparable datatypes in other databases.

chulett · Post by **chulett** » Tue Nov 25, 2008 12:00 pm

Exactly, and all of which are officially "unsupported".

ArndW · Post by **ArndW** » Tue Nov 25, 2008 12:33 pm

Yep, unsupported. We have a couple of jobs here that send over 40 VarChar2 columns containing XML (and SOAP) to Oracle, where they are combined into one BLOB for compression and storage. The same happens on the way back into DataStage and the whole thing is a complex and unwieldy system - I wish we could have used Server jobs and Hashed files for storage for this portion.

eostic · Post by **eostic** » Wed Nov 26, 2008 9:07 am

All important considerations. Here's how you can do it if you choose a supported datatype.....

If the strings are short enough to use a supported datatype, like char or longvarchar, just have a single column on the output link of your XMLOutput Stage.... call it anything, like "myXMLoutput" ...give it a longvarchar and some length, such as 99999, and then put a single slash in the Description property. All of your xml will go into that column as a single string.

Ernie