topic Re: How to Import column which is stored as XML format in database? in SAS Programming

How to Import column which is stored as XML format in database?

Gangi — Fri, 08 Nov 2019 04:29:11 GMT

I have table able called ABC in database with column names as A,B,C,D,E. Out of these columns B column has data in XML format.

when I tried to import data from datsabse using PROC SQL it the throwing an error,

"ERROR: At least one of the columns in this DBMS table has a datatype that is not supported by this engine."

ChrisNZ — Fri, 08 Nov 2019 05:15:01 GMT

You have to cast the type XML to VARCHAR in a pass-through query.

Gangi — Fri, 08 Nov 2019 06:12:25 GMT

I tried using cast(B as VARCHAR), I got the error as

Teradata prepare: Syntax error, expected something like '(' between the 'VARCHAR' keyword and ')'

I also tried defining the length Varchar(10000) but still I got the error.

Thank you,

Kurt_Bremser — Fri, 08 Nov 2019 07:20:15 GMT

Did you do this

cast (b as varchar(10000))

and if it did not work, what was the Teradata response?

ChrisNZ — Sat, 09 Nov 2019 11:00:00 GMT

> Syntax error, expected something like '(' between the 'VARCHAR' keyword and ')'

means there is something missing here:

cast(B as VARCHAR HERE )

which is, at the message reads between the 'VARCHAR' keyword and ')'

and which is, at the message also reads something like '('

more specifically something like (100).

> I also tried defining the length Varchar(10000) but still I got the error.

Surprising. Post the complete log please.