CODEWITHSUNDEEP
c#asp.netsql-server
Ron
Ron

Reputation: 1931

how to modify xml node in sql server

I have an xml column in my table named 'Content_Html' in which the data would look like:

 <root>
      <Category>Cover Impression</Category>
      <Title>Mystery of the Wolves</Title>
      <Month>April</Month>
      ...
      ...
    </root>

I am trying to replace the element under <Category> from Cover Impression to Cover Impressions, and after the replace, my table with this category should look like

<Category>Cover Impressions</Category>

I checked this article (http://stackoverflow.com/questions/7316712/how-to-rename-xml-node-name-in-a-sql-server) for my question, but not exactly what i am looking for.

Can some one point me in the right direction ?

after suggestions down below i tried this:

declare @newValue XML
select @newValue = 'Cover Impressions'
update dbo.content 
set content_html.modify('replace value of (/root/Category/text())[1] with sql:variable("@newValue")')

but giving me "Cannot call methods on ntext" error Thanks,

Upvotes: 2

Views: 1450

Answers (2)

marc_s
marc_s

Reputation: 755321

Update: What you could try (but please - on a TEST environment first!) is to just simply change the datatype of your column to XML:

ALTER TABLE dbo.YourTable 
  ALTER COLUMN Content_Html XML

If you have only valid XML content in all your rows for that column, this command ought to work.

Once that has worked - then you can try this for your specific example :

UPDATE dbo.YourTable
SET Content_Html.modify('replace value of (/root/Category/text())[1] with "Cover Impressions"')
WHERE ...(whatever condition need here).....

Upvotes: 0

Glory Raj
Glory Raj

Reputation: 17701

you can try like this..

for this code ..

    <Sample>
  <NodeOne>Value1</NodeOne>
  <NodeTwo>Value2</NodeTwo>
  <NodeThree>OldValue</NodeThree>
</Sample>

to replace ‘OldValue’ in NodeThree with ‘NewValue’.

 DECLARE @newValue varchar(50)
 SELECT @newValue = 'NewValue'

 UPDATE [Product]
 SET ProductXml.modify('replace value of (/Sample/NodeThree/text())[1] with sql:variable("@newValue")')

pls go through this for more information

Upvotes: 1

Related Questions