CODEWITHSUNDEEP
sqlsql-servert-sql
Spooks
Spooks

Reputation: 7177

Update, find/replace for xml field

I have a Page table in my database. Lets say for simplicity it has two columns; title and xmlData It has Title something like "my example " and I have a xml field that looks like:

<MA>
  <A>
    <URL>my-example-</URL>
    <id>5</id>
    </A>
</MA>

  1. I am trying to do a find replace of any url that has "-" at the end, and remove the only the last trailing "-" if it exists

  2. and remove the trailing space(if it has one, to title)

I am able to grab the rows that need to be changed by doing

select * from Pages
where title like '% '

(there is some joins and stuff, but that is basically it)

Upvotes: 3

Views: 199

Answers (2)

Mikael Eriksson
Mikael Eriksson

Reputation: 138960

This will replace one occurrence of the URL for each row. By the look of your sample XML you have only one URL per row.

with C1 as
(
  select xmlData,
         xmlData.value('(/MA/A/URL/text())[1]', 'nvarchar(500)') as URL
  from Pages
),
C2 as
(
  select xmlData,
         URL,
         left(URL, len(URL) - 1) as URL2
  from C1
  where right(URL, 1) = '-'
)
update C2 
set xmlData.modify('replace value of (/MA/A/URL/text())[1] 
                    with sql:column("C2.URL2")')

Extract the URL value in CTE C1.
Remove the last '-' from the URL and put that in URL2 in CTE C2. Also remove the rows that does not need to be updated.
Update the XML using modify() Method (xml Data Type)

And here is another version that does the job in the XML part of the query instead.

update Pages
set xmlData.modify('replace value of (/MA/A/URL/text())[1] 
                    with fn:substring((/MA/A/URL/text())[1], 1, fn:string-length((/MA/A/URL/text())[1])-1)')
where xmlData.exist('/MA/A/URL[fn:substring(text()[1], fn:string-length(text()[1]), 1) = "-"]') = 1

It is only possible to update one node at a time so if you have multiple URL's in in one row you have to put the code above in a loop and do the updates as long as there is something to update. You could use @@ROWCOUNT to check if the update did update anything and redo the update until @@ROWCOUNT = 0.

Upvotes: 1

Joe Taras
Joe Taras

Reputation: 15379

you can use CHARINDEX function to check if exists a character '-' and so on you can use SUBSTIRNG function to grab only part to replace of your text.

Example:

declare @a varchar(100)
set @a = 'HI, TODAY IS A - BEAUTIFUL DAY'

select 
    case
        when charindex('-',@a) >= 0 then
        substring(@a, 0, charindex('-',@a)-1) + 
        substring(@a, charindex('-', @a) + 1, 100)
        else @a
    end

Upvotes: 0

Related Questions