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


1 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>
        <Rowid>trim this value    </Rowid>
</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
SET col2.modify('replace value of (Root/Row/Rowid/text())[1] with sql:column("trimmedColumn")')

SELECT 'after' s, *
FROM #tmp

-- Cleanup

My results: Results after update

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.