非中文排序规则下对XML数据类型转换的一个问题

     随着SQL Server 2005/2008对XML数据类型的支持，越来越多的应用程序开始使用这一新功能。也因为这样，我们可能会遭遇许多在处理XML数据类型时的问题。比较典型的一个是，我们不能在SQL Server 2005的XQuery中，使用XML数据类型的变量，但很幸运的是，这一问题，在2008中得到了支持，可能有些朋友不明白文字描述的问题，那么下面我使用一个例子来说明这个问题（这个问题不是本文讨论的重点内容，但比较典型，所以顺便提出来）。

 

假设有这样2个XML数据如下：

 

--------------------------------------------------------------------------------

declare @x1 as xml

declare @x2 as xml

 

select

   @x1 = N'

<root>

   <node id="2">value2</node>

   <node id="3">value3</node>

</root>

',

   @x2 = N'

<node id="1">value1</node>

'

  -----------------------------------------------------------------------------------

 

我们需要把@x2的值插入到@x1里，得到下面的结果：

--------------------------------------------------------------------------------
<root>

      <node id="1">value1</node>

      <node id="2">value2</node>

      <node id="3">value3</node>

</root>
--------------------------------------------------------------------------------

那么我们可以使用如下的两种方法：

--------------------------------------------------------------------------------

--2005/2008

declare

      @x1 xml,

      @x2 xml

 

select

      @x1 = N'

<root>

      <node id="2">value2</node>

      <node id="3">value3</node>

</root>',

      @x2 = N'

      <node id="1">value1</node>'

     

     

--处理
if object_id('tempdb.dbo.##t') is not null

      drop table ##t

 

create table ##t

(

      col1 xml,

      col2 xml

)

 

insert ##t select @x1,@x2

 

declare @s varchar(200)

 

select

      @s = 'update ##t set col1.modify(''insert '

                  + convert(varchar(max),col2)

                  + ' as first into (/root)[1]'')'

from ##t

 

exec(@s)

 

select @x1 = col1 from ##t 

drop table ##t

 

select @x1
go

 

--2008

declare

      @x1 xml,

      @x2 xml

 

select

      @x1 = N'

<root>

      <node id="2">value2</node>

      <node id="3">value3</node>

</root>',

      @x2 = N'

      <node id="1">value1</node>'

 

--处理

set @x1.modify('insert sql:variable("@x2") as first into (/root)[1]')

   

select @x1
--------------------------------------------------------------------------------

从上面的脚本可以看到，SQL SERVER 2008里，我们可以直接对XML类型进行XQuery操作，而在2005里
我们无法使用这个功能，必须变通处理，如果把第2种处理方式放到2005版本下运行，你可以看到下面的错误：

Msg 6739, Level 16, State 1, Line 16

XQuery: SQL type 'xml' is not supported in XQuery.

 

 

上面的例子已经阐明了一个典型问题，但实际上在我们实际处理中，还有另外一个比较隐蔽的问题，值得注意，
那就是非中文排序规则下对XML数据类型的转换问题。

假设SQL SERVER安装时，没有使用中文排序规则，而是使用了非中文排序规则，常见的是
SQL_Latin1_General_CP1_CI_AS（关于如何查询数据库的排序规则，可以使用
select databasepropertyex('model','collation')来进行查询），而我们又要求能在数据库里显示中文字符，大家都知道，必须把表的数据类型定义成为NCHAR/NVARCHAR类型，然后在插入数据时指定“N”字符，如果表的数据类型是XML数据类型，要能显示中文字符，同样也是要在插入数据时，指定
“N”字符，如果没有指定，则会像下面一样显示问号：

--------------------------------------------------------------------------------
use master

go

 

--创建非中文排序规则的数据库

create database endb

collate SQL_Latin1_General_CP1_CI_AS

go

 

use endb

go

 

create table t

(

      col xml

)

go

 

insert t select N'<root>测试</root>'

union all select '<root>测试</root>'  --没有使用"N"

go

 

select * from t

go

 

drop table t

go

 

 

use master

go

 

drop database endb

go

--结果

<root>测试</root>

<root>??2</root>
--------------------------------------------------------------------------------

OK，我们解决了非中文环境下使用带有中文的问题，那在对其处理时有什么需要注意的么？在这里，我们借用最前面提到的场景，只是我们需要特别指出的是，我们的数据需要包含中文字符，让我们稍微修改下代码，再次运行一下看看:

--------------------------------------------------------------------------------

use master

go

 

--创建非中文排序规则的数据库

create database endb

collate SQL_Latin1_General_CP1_CI_AS

go

 

use endb

go

 

 

--2005/2008

declare

      @x1 xml,

      @x2 xml

 

select

      @x1 = N'

<root>

      <node id="2">value2</node>

      <node id="3">value3</node>

</root>',

      @x2 = N'

      <node id="1">节点值</node>'   --这里包含中文字符

     

     

--处理
if object_id('tempdb.dbo.##t') is not null

      drop table ##t

create table ##t

(

      col1 xml,

      col2 xml

)

 

insert ##t select @x1,@x2

 

declare @s varchar(200)

 

select

      @s = 'update ##t set col1.modify(''insert '

                  + convert(varchar(max),col2)

                  + ' as first into (/root)[1]'')'

from ##t

 

exec(@s)

 

select @x1 = col1 from ##t 

drop table ##t

 

select @x1

go

 

 

use master

go

 

drop database endb

go

 

 

--------------------------------------------------------------------------------

这次没有那么幸运，当你运行完以后，你会看到如下的错误：

(1 row(s) affected)

Msg 6355, Level 16, State 1, Line 29

Conversion of one or more characters from XML to target collation impossible

问题出在哪呢？这就是我要提醒大家的地方，在非中文排序规则下，我们要想支持中文字符显示，大家都记得
会在插入数据时指定”N”，但当你需要把数据拿出来做临时处理的时候，你是否会记得对于XML类型的数据，
你需要转换为nvarchar,而不是varchar呢？所以说，出现问题的地方也就是下面这个地方：

--------------------------------------------------------------------------------

select

      @s = 'update ##t set col1.modify(''insert '

                  + convert(varchar(max),col2)  --这里要转换为nvarchar

                  + ' as first into (/root)[1]'')'

--------------------------------------------------------------------------------

修改后再运行一下，是不是没有错误了！大家肯定现在想问个问题，错误是没有了，为什么中文字符又变成问号了？大家要多买点脑白金（忽悠一下，不是做广告），拿出来是记得要用nvarchar，但插入进去，怎么又给忘了加“N”呢？所以，正确的代码修改应该是下面这样：

--------------------------------------------------------------------------------
select

      @s = N'update ##t set col1.modify(N''insert ' --这里要加"N"

                  + convert(nvarchar(max),col2)   --这里要转换为nvarchar

                  + ' as first into (/root)[1]'')'

from ##t
--------------------------------------------------------------------------------

 

再运行一下，我希望是你看到的结果！

    耽误各位那么长时间阅读这篇文章，希望你能在这篇文章中找到你想要的，也谢谢各位对本人的继续支持！