none
The xml.value() function, which is used directly for fields and applied to variables, behaves differently? RRS feed

  • Question

  • Hi,
    I'm using sqlserver 2012, there is a table field type of xml, and then read in this way:

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


    The xml data in the table is as follows:

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


    That is, the value of another property of the element is queried based on the value of the xml element's property.

    The problem now is that querying directly from the field will report a type conversion error, and always report:

    Failed to convert nvarchar '你好' data to int

    But if you do this instead:

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


    There is no error at all and the value is correctly obtained.

    At present, there is no thought at all, do not know what went wrong ... Trying to change libraries, rebuilding tables, rewriting xml, none of them.
    Hope you have to teach me, thank you!

    That my real table scheme:

    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


    The data like that:

    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>');


    The "type" in the xml data is only used for validation in the page form I generate, and its value value is real and does not have decimal points, so I should have no problem turning to int.

    My query statement is like this:

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



    • Edited by aben008 Tuesday, July 7, 2020 6:22 AM
    Tuesday, July 7, 2020 3:02 AM

Answers

  • Hi aben008,

    You didn't provide desired output.

    Please try the following. It is using a CROSS APPLY clause. That's the difference between a variable and a column of the XML data type.

    SQL:

    -- DDL and sample data population, start DECLARE @ConfigInfo TABLE ( [ConfigKey] [nvarchar](50) PRIMARY KEY 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); INSERT INTO @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>'); -- DDL and sample data population, end 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);

    Tuesday, July 7, 2020 4:19 AM
  • If you remove the ‘NOT NULL’ constraint from the XmlConfig column in table definition, then the next query seems to work too:

    select XmlConfig.value('(//field[@name="rate"]/@value)[1]','int')

    from ConfigInfo

    where ConfigKey = N'MyKey'


    If it must be NOT NULL, then check this query:


    select
    cast(XmlConfig as xml).value('(//field[@name="rate"]/@value)[1]','int')

    from ConfigInfo

    where ConfigKey = N'MyKey'





    • Edited by Viorel_MVP Tuesday, July 7, 2020 8:17 AM
    • Marked as answer by aben008 Wednesday, July 8, 2020 1:38 AM
    Tuesday, July 7, 2020 8:10 AM
  • There have been several suggestions in the thread, but none seems to address the root problem is that SQL Server may apply the conversion before it is doing the filtering on the tag. SQL Server may do things in this order, because it thinks it faster, but it is not considering the risk for conversion errors. This is a recurring theme and by no means unique to XML. You can see this with regular relational queries as well.

    So while the workarounds suggested so far works for the moment, they break tomorrow. To solve this in a robust way, you need to extract the data as string, and then use try_cast/try_convert to suppress the error. See example below.

    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]
    
    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>');
    go
    select try_cast(XmlConfig.value('(//field[@name="rate"]/@value)[1]', 'nvarchar(10)') AS int)
    from   ConfigInfo
    where ConfigKey = 'MyKey'
    go
    DROP TABLE ConfigInfo


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Tuesday, July 7, 2020 9:23 AM

All replies

  • Hi aben008,

    You didn't provide desired output.

    Please try the following. It is using a CROSS APPLY clause. That's the difference between a variable and a column of the XML data type.

    SQL:

    -- DDL and sample data population, start DECLARE @ConfigInfo TABLE ( [ConfigKey] [nvarchar](50) PRIMARY KEY 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); INSERT INTO @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>'); -- DDL and sample data population, end 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);

    Tuesday, July 7, 2020 4:19 AM
  • Hi aben008,

    You didn't provide desired output.

    Please try the following. It is using a CROSS APPLY clause. That's the difference between a variable and a column of the XML data type.

    SQL:

    -- DDL and sample data population, start DECLARE @ConfigInfo TABLE ( [ConfigKey] [nvarchar](50) PRIMARY KEY 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); INSERT INTO @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>'); -- DDL and sample data population, end 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);

    Sorry about my question style and I've added it, and thanks you answer. 

    Now that I can get the right results, but I still don't understand why querying through column will be wrong,CROSS APPLY seems to be no different from variables, can you explain or give a link to study?

    Will it be related to my DDL? I still want to be able to query through the column.

    • Edited by aben008 Tuesday, July 7, 2020 6:42 AM
    Tuesday, July 7, 2020 6:28 AM
  • Hope this link will help: Why is CROSS APPLY needed when using XPath queries?

    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.

    Tuesday, July 7, 2020 7:40 AM
  • If you remove the ‘NOT NULL’ constraint from the XmlConfig column in table definition, then the next query seems to work too:

    select XmlConfig.value('(//field[@name="rate"]/@value)[1]','int')

    from ConfigInfo

    where ConfigKey = N'MyKey'


    If it must be NOT NULL, then check this query:


    select
    cast(XmlConfig as xml).value('(//field[@name="rate"]/@value)[1]','int')

    from ConfigInfo

    where ConfigKey = N'MyKey'





    • Edited by Viorel_MVP Tuesday, July 7, 2020 8:17 AM
    • Marked as answer by aben008 Wednesday, July 8, 2020 1:38 AM
    Tuesday, July 7, 2020 8:10 AM
  • Hope this link will help: Why is CROSS APPLY needed when using XPath queries?

    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.

    多谢,我慢慢看看

    Tuesday, July 7, 2020 8:22 AM
  • If you remove the ‘NOT NULL’ constraint from the XmlConfig column in table definition, then the next query seems to work too:

    select XmlConfig.value('(//field[@name="rate"]/@value)[1]','int')

    from ConfigInfo

    where ConfigKey = N'MyKey'


    If it must be NOT NULL, then check this query:


    select
    cast(XmlConfig as xml).value('(//field[@name="rate"]/@value)[1]','int')

    from ConfigInfo

    where ConfigKey = N'MyKey'





    This modification is very convenient, thank you very much.

    Tuesday, July 7, 2020 8:22 AM
  • There have been several suggestions in the thread, but none seems to address the root problem is that SQL Server may apply the conversion before it is doing the filtering on the tag. SQL Server may do things in this order, because it thinks it faster, but it is not considering the risk for conversion errors. This is a recurring theme and by no means unique to XML. You can see this with regular relational queries as well.

    So while the workarounds suggested so far works for the moment, they break tomorrow. To solve this in a robust way, you need to extract the data as string, and then use try_cast/try_convert to suppress the error. See example below.

    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]
    
    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>');
    go
    select try_cast(XmlConfig.value('(//field[@name="rate"]/@value)[1]', 'nvarchar(10)') AS int)
    from   ConfigInfo
    where ConfigKey = 'MyKey'
    go
    DROP TABLE ConfigInfo


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Tuesday, July 7, 2020 9:23 AM
  • thank you! I learned more...
    Tuesday, July 7, 2020 9:45 AM
  • Very good point, Erland! Although I like Yitzhak's query syntax - to me it's more readable.

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    Tuesday, July 7, 2020 1:14 PM
    Moderator
  • Hi Naomi,

    It is beyond readability.

    Like DBs have schemas, data types, etc., the same is with XML. It has XML Schema (XSDs) to enforce structure, data types (49 of them in XSD 1.1), cardinality, optional vs. mandatory, business rules, etc.


    Tuesday, July 7, 2020 1:20 PM
  • Hi aben008 and everybody else,

    Trying to promote XML technologies, I recently asked Microsoft to beef up NoSQL functionality in SQL Server. Please check it out, add your comments, and vote for it: https://feedback.azure.com/forums/908035-sql-server/suggestions/38142115-sql-server-vnext-post-2019-and-nosql-functionali

    It became one of the most popular requests for MS SQL Server.

    The voting tally is 234 now. 

    Tuesday, July 7, 2020 1:27 PM
  • Hello friend,
    Have you solved the question?
    If you have resolved your issue, could you please mark the useful reply as answer? This can be beneficial to other community members finding and reading the thread easily. 
    In addition, if you have another questions, please feel free to ask.
    Thanks for your contribution.

    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.

    Wednesday, July 8, 2020 1:03 AM
  • All three way can solve my problem, but for the time being, Viorel_'s solution let me to solve the problem quickly. Of course, I'll never write xml query like that question.
    Thank for all!
    Wednesday, July 8, 2020 1:43 AM
  • Hi aben008,

    Good to hear that this thread provided you some answers.

    Please connect with me on LinkedIn.

    Wednesday, July 8, 2020 3:57 AM