DBD::OCBC support for XML datatype in SQL Server 2005

[Michael Ludwig]

I'm facing encoding issues in trying to make use of the XML datatype in
SQL Server 2005, which I'm accessing using DBD::ODBC 1.23 and Perl 5.12.

  CREATE TABLE T2 (a VARCHAR(99), u NVARCHAR(99), x XML);

Three columns here, a for single-byte characters, u for Unicode, and
x for XML.

The following statements works correctly in SSMS (SS Management Studio):

  INSERT INTO T2 VALUES ('Käse', N'Käse', CAST( '<d>Käse</d>' AS XML));

  Käse    Käse    <d>Käse</d>

Now German wasn't too difficult, so let's try some Russian.

  INSERT INTO T2 VALUES
  ('Москва', N'Москва', CAST('<r>Москва</r>' AS XML));
  INSERT INTO T2 VALUES
  ('Москва', N'Москва', CAST(N'<r>Москва</r>' AS XML));

  ??????  Москва  <r>??????</r>
  ??????  Москва  <r>Москва</r>

We need the N introducer for Unicode literals and a column type capable
of receiving Unicode data. Failing any of those two, we're getting just
a series of substitution characters (?).

How can we handle this situation from Perl? Here's a script.

          \,,,/
          (o o)
------oOOo-(_)-oOOo------
use strict;
use warnings; no warnings 'uninitialized';
use utf8;
use DBI;

my $txt_de = 'Käse';
my $txt_ru = 'Москва';

binmode STDOUT, ':utf8';

my @dsn = qw/DBI:ODBC:MY_DB my_username my_password/;
my %opt = (PrintError => 0, RaiseError => 1, AutoCommit => 1);
my $dbh = DBI->connect( @dsn, \%opt );
$dbh->{LongReadLen} = 4000;
$dbh->{LongTruncOk} = 1; # Dies muß, ob logisch oder nicht.

my $sth_ins = $dbh->prepare(
  'INSERT INTO T2 (a, u, x) VALUES (?, ?, CAST( ? AS XML) )' );
$sth_ins->execute( $txt_de, $txt_de, "<d>$txt_de</d>" );
$sth_ins->execute( $txt_ru, $txt_ru, "<r>$txt_ru</r>" );

my $sth_sel = $dbh->prepare( 'SELECT u, x FROM T2' );
$sth_sel->execute;
$sth_sel->bind_columns( \my( $txt, $xml ) );
my $i = 0;
while ( $sth_sel->fetch ) {
  printf "%3u %3u [%s] [%s]\n", ++$i, length($txt), $txt, $xml;
}
$dbh->disconnect;
-------------------------

The problem is the INSERT statement, more specifically, in the XML part.
I can't seem to get it to accept my Unicode strings as Unicode for that
column. Instead, they're treated as octets, resulting in garbage.

  Käse   Käse    <d>Käse</d>
  Москва Москва  <r>Москва</r>

I've tried to use an N introducer with the XML column, but that leads to
errors.

  CAST( N? AS XML)      - Invalid column name 'n...@p3'. (SQL-42S22)
  CAST( N ? AS XML)     - Incorrect syntax near '@P3'. (SQL-42000)

I'd appreciate your advice.
-- 
Michael Ludwig