none
xml.value()函数,直接用于字段和应用于变量,为何表现不同? RRS feed

  • 问题

  • 用的是sqlserver2012,有一个表字段类型为xml,然后用这种方式读取:

    select field.value('(//tag[@name="abc"]/@value)[1]','int')
    from tb
    where ....

    表内xml数据如下:

    <root>
    <tag name="msg" value="你好" />
    <tag name="abc" value="1000" />
    <tag name="cde" value="10" />
    <tag name="efg" value="100" />
    </root>

    即,根据xml元素属性值查询该元素另一个属性的值。

    现在遇到的问题是,直接从字段进行查询,会报类型转换错误,而且始终是报:

    在将nvarchar数据"你好"转换为int时失败

    但是如果改为这样操作:

    declare @v xml;
    select @v=field from tb where ...
    select @v.value('(//tag[@name="abc"]/@value)[1]','int');

    则完全不会报错而且正确能取到值。

    目前完全没有思绪,不知道到底哪里出错……试了换库、重建表、重新写入xml,都不行。
    希望大家有以教我,谢谢!

    2020年7月6日 1:26

答案

  • 你好,

    根据你的数据和语句,经过测试,我把int改为navchar()以后就可以正常显示了:

    select XmlConfig.value('(//field[@name="preq"]/@value)[1]','nvarchar(50)') from ConfigInfo where ConfigKey=N'MyKey';
    

    你把int改为nvarchar()应该也是可以的


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    2020年7月6日 9:34

全部回复

  • 你好,

    首先请确认你的变量[@name="abc"]还是[@name="msg"]?  通常,使用value()从 xml 类型列、参数或变量内存储的 XML 实例中提取值 。根据xml.value的语法:value (XQuery, SQLType)  ,SQLType是你XML文本中的格式,而你要查找的是int,但是“你好”不是int格式,所以报错。

    另外你的两种方法,直接查询列字段和先指定变量再查询,如果是[@name="abc"],都是可以执行成功的,如果改成[@name="msg"],都是会报如上错误,我做了一下测试如下:

    1. select A.value('(//tag[@name="abc"]/@value)[1]','int') from test

    能返回有效值int 1000

    2.  declare @v xml;
    select @v=A from TEST
    select @v.value('(//tag[@name="abc"]/@value)[1]','int');

    能返回有效值int 1000

    3. declare @v xml;
    select @v=A from TEST
    select @v.value('(//tag[@name="msg"]/@value)[1]','int');

    把@name改为msg, 由于msg对应的‘你好’不是int,所以返回如下报错,和你的报错是一样的

    4. select A.value('(//tag[@name="msg"]/@value)[1]','nvarchar') from test

    把@name改为msg, 输出类型改为nvarchar,就能返回‘你好’,由于我用的不是UTF-8字符集,所以返回的是?,实际上这个语句是没有问题的

    所以你这个报错的问题出在‘你好’字符类型和你想要查询的字符类型不符,请在检查一下,应该和你直接查询字段或变量关系不大

    希望对你有用



    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.





    2020年7月6日 7:07
  • 你好,

    首先请确认你的变量[@name="abc"]还是[@name="msg"]?  





    首先谢谢您的回复,然而我百分之百的肯定,我的xpath内的特性值没有写错,所以才会如此困惑。

    您建立测试表测试的方法,我也试过,这也是让我抓狂的地方,建立的测试表,查询完全没有问题,但改从正式表内查询则会报错。为此我试过重新建库、重新建表后手动插入数据(原先的正式表是由asp.net程序写入xml数据的)等办法,都是这样的错误,所以我已经完全没有头绪了……

    会不会跟where条件、建表选项、建库选项等有关呢?我的正式表里,是用一个nvarchar字段做为主键的,查询的条件也是这样写:

    ...

    where MyKey=N'...'

    这个key虽然都会用全英文做为key,但字段是定义成了nvarchar(50),所以写条件时用了N''的写法。

    但其实我试过去掉字母N,并没有什么改变。

    如果有可能与建表、建库选项有关,请告诉我与哪些参数有关,我去检查一下。

    再次感谢您的回复


    • 已编辑 aben008 2020年7月6日 8:09
    2020年7月6日 8:03
  • 在确保数据安全的情况下,能否分享一下你的XML表格和具体的查询语句? 如果你把@name改成msg, int改为nvarchar,是否还有这个报错?

    另外,一般情况都是要先declare,再select,这样是最符合逻辑的,而且你经过declare再select也不报错。我觉得你可以不用纠结直接用字段应用于变量这种方法的报错,而且这个用法也比较奇怪


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.




    2020年7月6日 8:08
  • 在确保数据安全的情况下,能否分享一下你的XML表格和具体的查询语句? 如果你把@name改成msg, int改为nvarchar,是否还有这个报错?

    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    表结构如下:

    CREATE TABLE [dbo].[ConfigInfo](
    [ConfigKey] [nvarchar](50) NOT NULL,
    [Title] [nvarchar](50) NOT NULL,
    [Description] [nvarchar](400) NOT NULL,
    [SortID] [int] NOT NULL,
    [JsonConfig] [varchar](max) NOT NULL,
    [XmlConfig] [xml] NOT NULL,
     CONSTRAINT [PK_ConfigInfo] PRIMARY KEY CLUSTERED 
    (
    [ConfigKey] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
    ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
    GO

    真实数据(去掉了业务相关文字)如下:

    INSERT [dbo].[ConfigInfo] ([ConfigKey], [Title], [Description], [SortID], [JsonConfig], [XmlConfig]) 
    VALUES (N'MyKey', N'标题', N'说明', 25, 
    N'', 
    N'<root>
    <field type="checkbox" name="switch" placeholder="">
    <checkItems text="开启" checked="1" />
    </field>
    <field type="txt" name="closeMsg" placeholder="说明" value="功能被关闭,请留意公告" />
    <field type="float" name="max" placeholder="说明" value="100" />
    <field type="float" name="rate" placeholder="说明" value="10000" />
    <field type="float" name="preq" placeholder="说明" value="10" />
    <field type="float" name="retention" placeholder="说明" value="1000" />
    </root>');

    查询语句如下:

    select XmlConfig.value('(//field[@name="preq"]/@value)[1]','int') from MyDataBase.dbo.ConfigInfo where ConfigKey=N'MyKey';

    xml数据中的那个type仅在我生成页面表单做验证使用,其value值是真实、没有带小数点的,因此我转为int理应没有问题。

    事实上,我真实的业务逻辑会同时将所有field的value查出来赋值给变量,但无论是查preq还是max还是rate...都会出现题目描述的错误,好像无论查什么都会读取到name=closeMsg的那个field去了。

    2020年7月6日 8:20

  • 另外,一般情况都是要先declare,再select,这样是最符合逻辑的,而且你经过declare再select也不报错。我觉得你可以不用纠结直接用字段应用于变量这种方法的报错,而且这个用法也比较奇怪





    这个……因为这种用法,之前是从来没有遇到过这样报错的,所以大规模用在了很多地方,如果全部改成先取到变量内再查询赋值的话……工作量实在有点太大了……

    2020年7月6日 8:27
  • 你好,

    根据你的数据和语句,经过测试,我把int改为navchar()以后就可以正常显示了:

    select XmlConfig.value('(//field[@name="preq"]/@value)[1]','nvarchar(50)') from ConfigInfo where ConfigKey=N'MyKey';
    

    你把int改为nvarchar()应该也是可以的


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    2020年7月6日 9:34
  • 这样是可以,但并没有解决我的问题,因为我想要的数据类型不是nvarchar

    取出来的值是要参与计算和判断的,为每个读取的地方增加额外的类型转换,也是一个工作量超大的事情……

    你用我的建表脚本和数据建立出来的表,读取也会出现数据类型转换错误的问题吗?究竟是怎么回事呢?

    2020年7月6日 9:50
  • 你好,

    我也是同样的数据类型转换报错。

    经过把<field type="txt" name="closeMsg" placeholder="说明" value="功能被关闭,请留意公告" />删除,然后直接select就能成功返回@value:10。 我觉得如果取出的值还要计算,必须要int的话,如果可能的话,把<field type="txt" name="closeMsg" placeholder="说明" value="功能被关闭,请留意公告" />删除,如果不行,那就只能赋值变量再select了。


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.


    2020年7月7日 1:39
  • 你好,

    我也是同样的数据类型转换报错。

    经过把<field type="txt" name="closeMsg" placeholder="说明" value="功能被关闭,请留意公告" />删除,然后直接select就能成功返回@value:10。 所以我个人理解是如果直接select field.value(),第一步是按照路径检查所有变量@value的数据类型并转换成int,第二步是返回符合@name=preq的@value,在你这个案例里由于有nvarchar无法转换成int,所以报无法转换的错误,就无法成功执行返回对应的@value.

    我觉得如果取出的值还要计算,必须要int的话,如果可能的话,把<field type="txt" name="closeMsg" placeholder="说明" value="功能被关闭,请留意公告" />删除,如果不行,那就只能赋值变量再select了。


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    呃……这个解释……那这个xpath的行为很令人……感动啊……

    不过这解释不了为什么在表结构不同的测试表里,相同的xml结构和xpath,却不会报错的问题啊

    2020年7月7日 2:14
  • 个人理解。那你尝试把主键类型改一下试试,实在不行,你可以买个微软的ticket,他们更加专业,应该可以告诉你底层逻辑。希望对你有用



    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.


    2020年7月7日 2:22
  • 谢谢,跑到英文论坛去提问去了,不过英文论坛里找不到对应的tags,随便加了一个azure-sql-database的tag- -
    2020年7月7日 2:57
  • https://social.msdn.microsoft.com/Forums/sqlserver/en-US/home?forum=transactsql你可以到这问问

    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    2020年7月7日 2:59
  • 好的,多谢
    2020年7月7日 3:02
  • 其实还没有解决我的问题,不过看起来只能去修改读取方式了。从英文社区得到一种修改的方案,对于代码的修改略为简单些:

    SELECT ConfigKey, c.value('(field[@name="rate"]/@value)[1]','INT') AS rate	, c.value('(field[@name="preq"]/@value)[1]','INT') AS preq
    FROM ConfigInfo AS tbl
    CROSS APPLY XmlConfig.nodes('/root') AS t(c)
    where ConfigKey=N'MyKey'

    但对于这个问题的原因仍然没有一点头绪,还在等待看英文社区里有没有人能给解释一下,感兴趣的可以去看看:

    https://social.msdn.microsoft.com/Forums/sqlserver/en-US/e0a9ab70-688a-437d-8daf-919ba0601ce7/the-xmlvalue-function-which-is-used-directly-for-fields-and-applied-to-variables-behaves?forum=transactsql

    2020年7月7日 6:59