how to trim values of xml while reading from sqlserver

rajesh yadav 171 Reputation points


I have XML which I am reading from SQL server.  

T.C.value('@DeezNutz ','nvarchar(max)') ColumnName

			 FROM @kXML.nodes('Column[fn:lower-case(@isok) = "true"]') AS T(C)   

As I have used fn:lower-case to handle case-sensitivity, I want to put trim also, pls tell me how to put the trim in

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,850 questions
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,559 questions
{count} votes

Accepted answer
  1. Yitzhak Khabinsky 25,201 Reputation points

    Here is how to do it correctly.
    You can apply the same technique in both XQuery methods: .value() and .nodes()


    DECLARE @Data XML = N'<root>
         <column id="1" isok="true">OK</column>
         <column id="2" isok="false">NOT OK</column>
         <column id="3" isok="   TRUE   ">OK</column>
         <column id="4" isok=" false">NOT OK</column>
     SELECT c.value('@id', 'INT') AS [id]
     , c.value('@isok', 'VARCHAR(10)') AS [isok]
     , c.value('(./text())[1]', 'VARCHAR(10)') AS [column]
     FROM @Data.nodes('/root/column[(fn:lower-case(@isok) cast as xs:token?) = "true"]') AS t(c);


    | id |    isok    | column |
    |  1 | true       | OK     |
    |  3 |    TRUE    | OK     |

1 additional answer

Sort by: Most helpful
  1. Stefan Hoffmann 621 Reputation points

    First of all: xs:boolean allows only "true", "false", "1", "0". So those tests are not necessary. Invalid XML should no be processed.

    Then: look at how fn:boolean() works. This should give you a hint, why you should NOT process such data.

    And last but not least:
    The closest function is fn:contains, but it's better to cast the value. So you may choose between

    DECLARE @Data XML = N'<root>
        <column id="1" isok="True">OK</column>
        <column id="2" isok="False">OK</column>
        <column id="3" isok="1">OK</column>
        <column id="4" isok="0">OK</column>
        <column id="5" isok="true">OK</column>
        <column id="6" isok="false">OK</column>
        <column id="7" isok=" true">OK</column>
        <column id="8" isok=" false">OK</column>
    SELECT T.C.query('.')
    FROM   @Data.nodes('/root/column[fn:lower-case(@isok) = "true"]') T(C);
    SELECT T.C.query('.')
    FROM   @Data.nodes('/root/column[fn:contains(fn:lower-case(@isok),"true")]') T(C);
    SELECT T.C.query('.')
    FROM   @Data.nodes('/root/column[xs:boolean(@isok)]') T(C);
    SELECT T.C.query('.')
    FROM   @Data.nodes('/root/column[xs:boolean(fn:lower-case(@isok))]') T(C);
    0 comments No comments