how to trim values of xml while reading from sqlserver

rajesh yadav 171 Reputation points
2020-12-09T06:54:21.45+00:00

hi,

I have XML which I am reading from SQL server.  

SELECT
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
XPath.

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
Transact-SQL
Transact-SQL
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
    2020-12-09T12:54:44.563+00:00

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

    SQL

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

    Output

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

1 additional answer

Sort by: Most helpful
  1. Stefan Hoffmann 621 Reputation points
    2020-12-09T10:13:59.477+00:00

    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>
    </root>';
    
    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