Trim a value in an xml column in MS SQL Server
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 ( '&lt;Root&gt;
    &lt;Row&gt;
        &lt;Rowid&gt;trim this value    &lt;/Rowid&gt;
    &lt;/Row&gt;
&lt;/Root&gt;' )


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: