[Windows / SQL Server / ODBC / DBI / DBD] How to access XML data type columns

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".

1) This works: SELECT SerialNumber, ProductCode, WorkOrder FROM ResultsTbl
2) This does not: SELECT CONVERT(VARCHAR(MAX),XmlResults) AS XmlResults, SerialNumber, ProductCode, WorkOrder FROM ResultsTbl

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, like

SELECT XmlResults.value('/whatever[1]','varchar(50)'), etc.

but I get the same behaviour: works from the IDE, but not from my Perl code :(

My code is quite standard:

my $sth = $dbh->prepare($sql);
my @row = $sth->fetchrow_array;
[download]

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?

Thanks!

Comment on [Windows / SQL Server / ODBC / DBI / DBD] How to access XML data type columns Select or Download Code

Replies are listed 'Best First'.
Re: [Windows / SQL Server / ODBC / DBI / DBD] How to access XML data type columns by mje (Curate) on Jul 21, 2010 at 08:08 UTC
Reduce code to a simple connect and select. Enable DBI tracing by setting and exporting the DBI_TRACE environment variable to something like 15=x.log. Run your code and post the log file output. `export DBI_TRACE=15=x.log` [download] or `set DBI_TRACE=15=x.log` [download]	[reply] [d/l] [select]
Re^2: [Windows / SQL Server / ODBC / DBI / DBD] How to access XML data type columns by gaggio (Friar) on Jul 22, 2010 at 00:45 UTC
Thank you for the DBI_TRACE tip. After having enabled tracing, I easily found what the problem was: the default LongReadLen of 80 was not sufficient to grab the full XML strings, resulting in the abortion of the query. Solved!!!	[reply]
Re^3: [Windows / SQL Server / ODBC / DBI / DBD] How to access XML data type columns by mje (Curate) on Jul 22, 2010 at 06:47 UTC
Glad you are working although I might have pointed at LongReadLen myself if you hadn't given the impression you were getting no data back at all for that column.	[reply]
Re^4: [Windows / SQL Server / ODBC / DBI / DBD] How to access XML data type columns by Jenda (Abbot) on Jul 22, 2010 at 10:33 UTC
Re^5: [Windows / SQL Server / ODBC / DBI / DBD] How to access XML data type columns by mje (Curate) on Jul 22, 2010 at 10:46 UTC
Re^5: [Windows / SQL Server / ODBC / DBI / DBD] How to access XML data type columns by gaggio (Friar) on Jul 22, 2010 at 11:00 UTC
Some notes below your chosen depth have not been shown here
Re: [Windows / SQL Server / ODBC / DBI / DBD] How to access XML data type columns by mje (Curate) on Jul 22, 2010 at 14:52 UTC
I looked into this and I cannot reproduce on a simple xml column with this: `create table mje (a int primary key, b xml); my $data = '<xx>' . ('z' x 500) . '</xx>'; insert into mje values (1, $data); select * from mje` [download] I always get data truncation errors whether I use the MS ODBC driver or the MS native client driver. However, DBD::ODBC is not doing the right thing if the driver reports the column type as -152 (XML) so I've rt'ed it at Does not handle MS SQL Server xml and hope to fix it in the next few days. I doubt the change will make any difference to you since I cannot reproduce your problem. If you get a chance to confirm the things I asked elsewhere in this node I'll look into it further.	[reply] [d/l]
Re^2: [Windows / SQL Server / ODBC / DBI / DBD] How to access XML data type columns by gaggio (Friar) on Jul 22, 2010 at 23:43 UTC
OK, I have to admit that I wasn't exhaustive in my explanations, and that there was indeed a data truncation error being thrown, except I did not see them. The reason is I was developing my code in a CGI script that wasn't capturing stderr... After I ran the script on the command line I could see the truncation error. Thanks mje for following up with the bug report on CPAN!	[reply]
Re^3: [Windows / SQL Server / ODBC / DBI / DBD] How to access XML data type columns by mje (Curate) on Jul 23, 2010 at 09:56 UTC
No problems and that rt should be fixed later today and hopefully I'll have time for a new development release.	[reply]


Perl: the Markov chain saw
	PerlMonks