Read xml datatype sql server, ODBC connector from datastage

A forum for discussing DataStage<sup>®</sup> basics. If you're not sure where your question goes, start here.

Moderators: chulett, rschirm, roy

Post Reply
ashish_moni86
Participant
Posts: 2
Joined: Fri Oct 16, 2015 8:42 am

Read xml datatype sql server, ODBC connector from datastage

Post by ashish_moni86 »

I am trying to read xml datatype in one column of Microsoft sql server using ODBC connector from datastage. Can anyone suggest me steps to do it like datatype, query, settings etc.
chulett
Charter Member
Charter Member
Posts: 43085
Joined: Tue Nov 12, 2002 4:34 pm
Location: Denver, CO

Post by chulett »

Did you check the docs to see if it was supported?
-craig

"You can never have too many knives" -- Logan Nine Fingers
ashish_moni86
Participant
Posts: 2
Joined: Fri Oct 16, 2015 8:42 am

Post by ashish_moni86 »

If it does not what is the other way to achieve this?
I want to read xml datatype column from sql server database table, parse it and load in netezza table. While reading as varchar or other similar datatypes I am getting error "ODBC_Connector_19,0: terminate called after throwing an instance of 'APT_BadAlloc'" as xml is big
chulett
Charter Member
Charter Member
Posts: 43085
Joined: Tue Nov 12, 2002 4:34 pm
Location: Denver, CO

Post by chulett »

Doesn't seem to be a supported data type.

Did you import the metadata or set it up manually? The former should give you a clue about what kind of types aren't well supported. And I'll have to leave the answers to folks who've actually done what you are attempting but perhaps somethinghere might give you some ideas for alternatives.

Oh, any chance you are on AIX?
-craig

"You can never have too many knives" -- Logan Nine Fingers
eostic
Premium Member
Premium Member
Posts: 3838
Joined: Mon Oct 17, 2005 9:34 am

Post by eostic »

Yeah....this is a difficult one. "Sometimes", the long-text types of columns will work with various connectors...sometimes not. At the very least, try making the datatype longvarchar with a long length and see what happens....and/or maybe try the JDBC Connector. ...but it might not be do-able via default.

Possible things to try.....

1. I haven't explored SQL Server and its XML datatypes, but back in the day when the database companies were all competing for xml storage features, most of them had alternate SQL syntax that could be used that would dynamically parse some of the xml as part of a SELECT. "Maybe" there is something you could do there to pass custom SQL and get back an answer set that has already parsed columns?

2. A Stored Procedure?

If you can't get it back already parsed, then the goal should be to just be able to get back the "chunk" of xml content so that you can parse it yourself.

...hopefully someone who has done it will have some ideas...

Ernie
Ernie Ostic

blogit!
<a href="https://dsrealtime.wordpress.com/2015/0 ... ere/">Open IGC is Here!</a>
qt_ky
Premium Member
Premium Member
Posts: 2895
Joined: Wed Aug 03, 2011 6:16 am
Location: USA

Post by qt_ky »

I haven't tried it using DataStage but have had to query a SQL Server column that was named XML and stored XML but I do not recall the data type. It may have been TEXT or a string rather than the XML type.

SELECT [ID], CAST([XML] AS XML) FROM ...;
Choose a job you love, and you will never have to work a day in your life. - Confucius
Post Reply