how to trim values of xml while reading from sqlserver

Question

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.

Answer 1

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     |
+----+------------+--------+

Answer 2

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