xml类型使用注意事项

xml 的数据类型在平常的开发也是很常用的，燃鹅。也是有一些地方需要留意。今天我就分享几个测试的例子。

使用 xquery.exist (有但不仅仅限于)的注意事项。通常使用来判断节点是否存在，值是否存在。常用例子是

DECLARE @xml XML = '
<Root>
    <ID>1</ID>
    <ID>2</ID>
</Root>
'
SELECT @xml.exist('Root/ID[text()=1]')
SELECT @xml.exist('Root/ID[text()=0]')

显然结果就是第一条就是1，第二条语句就是0。这是基本应用，也没有什么问题。

然后我们根据实际情况测试一下。比方说我们使用exist 来配对表里面的值，比如使用以下例子，将包含在xml节点的员工密码修改为默认123456

SET NOCOUNT ON;
DECLARE @T AS TABLE (
Staff NVARCHAR(50),
Age INT,
PSW VARCHAR(50)
)

INSERT INTO @T
        ( Staff, Age, PSW )
VALUES  ( 'Joe', 30,'JJJJJ' ),
        ( 'Mary',25,'KKK')

DECLARE @Xml XML ='
<Staffs>
    <Staff>Joe</Staff>
</Staffs>
'

SELECT * FROM @T
UPDATE a SET PSW = '123456'
    FROM @T a
    WHERE @Xml.exist('Staffs/Staff[text()=sql:column("a.Staff")]') = 1
SELECT * FROM @T




Staff                                              Age         PSW
-------------------------------------------------- ----------- --------------------------------------------------
Joe                                                30          JJJJJ
Mary                                               25          KKK

Staff                                              Age         PSW
-------------------------------------------------- ----------- --------------------------------------------------
Joe                                                30          123456
Mary                                               25          KKK

 这样看没有什么问题，因为测试用例都比较规范。但是如果变点花样就不一样了。比如说

SET NOCOUNT ON;
DECLARE @T AS TABLE (
Staff NCHAR(50),  --只是将数据类型从NVarchar(50) 调整为 NChar(50)
Age INT,
PSW VARCHAR(50)
)

INSERT INTO @T
        ( Staff, Age, PSW )
VALUES  ( 'Joe', 30,'JJJJJ' ),
        ( 'Mary',25,'KKK')

DECLARE @Xml XML ='
<Staffs>
    <Staff>Joe</Staff>
</Staffs>
'

SELECT * FROM @T
UPDATE a SET PSW = '123456'
    FROM @T a
    WHERE @Xml.exist('Staffs/Staff[text()=sql:column("a.Staff")]') = 1
SELECT * FROM @T

Staff Age PSW
-------------------------------------------------- ----------- --------------------------------------------------
Joe 30 JJJJJ
Mary 25 KKK

Staff Age PSW
-------------------------------------------------- ----------- --------------------------------------------------
Joe 30 JJJJJ
Mary 25 KKK

 

表里面的数据没有被更改，换句话来说用 xquery.exist 判断不了这种情况。原因好明确，就因为@T里面的Staff 实际上后面带了N多个空格嘛，所以和xml进行匹配就懵逼了。导致根本生成不了结果。

如果数据是这样的，不妨改一下方法，也可以实现修改的功能。下面的例子是先把xml里面的节点值先提取出来，再和数据库表变量进行一个比对，因为在默认情况下，数据库的比对会去除后面空格再进行比对。所以更新就可以成功了。

 

SET NOCOUNT ON;
DECLARE @T AS TABLE (
Staff NCHAR(50),
Age INT,
PSW VARCHAR(50)
)

INSERT INTO @T
        ( Staff, Age, PSW )
VALUES  ( 'Joe', 30,'JJJJJ' ),
        ( 'Mary',25,'KKK')

DECLARE @Xml XML ='
<Staffs>
    <Staff>Joe</Staff>
</Staffs>
'

SELECT * FROM @T
;WITH CTE AS
(
    SELECT t.c.value('.[text()]','Nvarchar(50)') Staff
        FROM @Xml.nodes('Staffs/Staff') AS t(c)
)
UPDATE a SET PSW = '123456'
    FROM @T a
    WHERE EXISTS(SELECT * FROM CTE WHERE a.Staff = CTE.Staff)
SELECT * FROM @T

 

 

这个例子也告诉我们当遇到有些语句在查询有问题出不来的时候，多一个途径，看下是不是查询的方法和和本身数据是否不符合。也多一种检查的路子。

燃鹅关键还是要多练习一下下