|P is for Practical|
[Windows / SQL Server / ODBC / DBI / DBD] How to access XML data type columnsby gaggio (Friar)
|on Jul 21, 2010 at 03:02 UTC||Need Help??|
gaggio has asked for the
wisdom of the Perl Monks concerning the following question:
Dear brothers in Perl, I have now tried to tackle this problem for quite a while by myself without any success - time to request some of your wisdom!
I am on a Windows box, trying to access through ODBC / Perl DBI/DBD a database that has a table containing a column having the "XML" data type, and I just can't figure out a way to access this data in a bare select query: as soon as my query includes the XML-typed column, the query does not return any row.
The XML-typed column's name is "XmlResults".
The funny thing is 2) returns the expected rows perfectly well when I run the query through my database querying IDE (SqlDbx), connecting to SQL Server the exact same way (using the same ODBC DSN).
Note that no specific error is thrown - it's just that I get 0 row returned.
I have tried using other accessors than the CONVERT statement, XQuery for example, likeSELECT XmlResults.value('/whatever','varchar(50)'), etc.
but I get the same behaviour: works from the IDE, but not from my Perl code :(
My code is quite standard:
I have tried using $sth->fetchall_arrayref rather than $sth->fetchrow_array but it made no difference.
I have the latest CPAN modules installed.
What would you do to resolve my issue? Any hint/advice on how to analyse the issue further?