TeraData XML datatype

bicap · Post by **bicap** » Thu Dec 18, 2014 3:14 pm

I am using Db2 connector as source, copy stage, Teradata connector as Target stage.

In Teradata connector stage:
Load type : Immediate
Enabled LOB reference.
XML column is defined as LongNVarchar(2097088000) both in source & target stage.
When I load data the xml content is loading as character set.
< is replaced by &lt , > is replaced by &gt.
Do we have any steps need to take care in order to load xml content properly. ?

major · Post by **major** » Thu Dec 18, 2014 4:35 pm

Before loading the xml into Teradata, try to write into a file and see.

If everything is ok, change the data element in the meta data as 'XML'.

Thanks
major

qt_ky · Post by **qt_ky** » Thu Dec 18, 2014 6:23 pm

In case there was a question on certain symbols getting replaced, there are 5 invalid XML characters that must be escaped when in the data, in order to have proper XML content. Examples:

Code: Select all

>   >
<   <
&   &
'   &apos;
"   "

Hopefully the source XML column has already been validated and you're good to go.

chulett · Post by **chulett** » Thu Dec 18, 2014 7:57 pm

And then as noted, setting the Data Element to XML tells it that it is already XML and to leave it alone as it passed through.

bicap · Post by **bicap** » Fri Dec 19, 2014 8:45 am

The column is defined as XML in both source & target database, but in datastage meta, if we import, it was coming as UNKNOWN -370; So we changed to LongNVarchar(2097088000).

chulett · Post by **chulett** » Fri Dec 19, 2014 9:14 am

Note that we're not discussing the data type here but rather a property called data element. It's in the documentation and has been discussed in the forums here, for example in this post.

bicap · Post by **bicap** » Fri Dec 19, 2014 4:07 pm

When I choose the Data Element as XML , then it was throwing error as "the data element selected is not compatible with the SQL data type".

So, now the issue is when I edit the xml file in note pad , every thing looks good , but the spaces are stripped , making the xml content as long string.