Trim a value in an xml column in MS SQL Server

Question

I have a database with an XML column. I am trying to update (trim) an xml value of the xml column. I am running on Windows, SQL Server 2008 Express.

I did not find any way to trim directly in Xquery so I tried by selecting the value. Here is the SQL

select RTRIM(LTRIM(col2.value('(/Root/Row/Rowid/text())[1]','varchar(20)')))
from   dbo.T1

But now I am not getting how to update this xml value.

Is there any way to Trim the xml value directly? If not how can I Trim the value?

I am not trying to update the single row in the table, I am trying to trim the xml value of all the rows in the table

Answer 1

You could do that using the sql:column function and a Common Table Expression (CTE), something like this:

CREATE TABLE #tmp ( col2 XML )

INSERT INTO #tmp ( col2 )
VALUES ( '<Root>
    <Row>
        <Rowid>trim this value    </Rowid>
    </Row>
</Root>' )


SELECT 'before' s, *
FROM #tmp

;WITH cte AS
(
SELECT col2, RTRIM( LTRIM( col2.value('(/Root/Row/Rowid/text())[1]','VARCHAR(20)' )))  trimmedColumn
FROM #tmp
)
UPDATE cte
SET col2.modify('replace value of (Root/Row/Rowid/text())[1] with sql:column("trimmedColumn")')


SELECT 'after' s, *
FROM #tmp

-- Cleanup
DROP TABLE #tmp

My results: