2

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

8

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: Results after update

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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