Page 1 of 1
Read xml datatype sql server, ODBC connector from datastage
Posted: Mon Jun 18, 2018 10:21 am
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.
Posted: Mon Jun 18, 2018 2:02 pm
by chulett
Did you check the docs to see if it was supported?
Posted: Mon Jun 18, 2018 2:20 pm
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
Posted: Mon Jun 18, 2018 6:27 pm
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 something
here might give you some ideas for alternatives.
Oh, any chance you are on
AIX?
Posted: Tue Jun 19, 2018 5:25 am
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
Posted: Tue Jun 19, 2018 5:42 am
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 ...;