Beefy Boxes and Bandwidth Generously Provided by pair Networks
Perl-Sensitive Sunglasses

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

by gaggio (Friar)
on Jul 21, 2010 at 03:02 UTC ( #850544=perlquestion: print w/replies, xml ) 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".

  • 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;

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?


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
    set DBI_TRACE=15=x.log

      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.


        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.

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

    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.

      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!

        No problems and that rt should be fixed later today and hopefully I'll have time for a new development release.

Log In?

What's my password?
Create A New User
Node Status?
node history
Node Type: perlquestion [id://850544]
Approved by ww
[1nickt]: kgb not quite as simple as ... and ...; are interchangeable. There are edge cases, shown in perlsyn.
[1nickt]: Hm well actually I guess it's not the trailing semi-colon that's at issue there, it's disambiguation between the ellipsis and the binary flip-flop
[1nickt]: Although the example given does not produce an error as stated, on my perl.

How do I use this? | Other CB clients
Other Users?
Others avoiding work at the Monastery: (10)
As of 2017-11-18 19:41 GMT
Find Nodes?
    Voting Booth?
    In order to be able to say "I know Perl", you must have:

    Results (277 votes). Check out past polls.