how to modify xml node in sql server

Question

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,

Well duh - if your column is of type NTEXT, of course the XML functions won't work! I just assumed it was an XML column (since you said so!) First of all: don't use NTEXT anymore - it's deprecated - if at all, use nvarchar(max) and if it's a column containing XML - USE XML ... — marc_s
– marc_s, Commented Nov 17, 2011 at 21:36

Glory Raj · Accepted Answer · 2011-11-17 21:09:50Z

1

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

answered Nov 17, 2011 at 21:09

Glory Raj

17.7k28 gold badges114 silver badges215 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Ron Over a year ago

Thanks for your response, but i am getting "Cannot call methods on ntext" error message when i execute this statement.

Ron Over a year ago

@ marc_s thanks for your feedback, i am not proficient in sql - anyhow, i tried your method of declaring my variable as XML and still getting the same message - "Cannot call methods on ntext"...

marc_s · Accepted Answer · 2011-11-17 22:04:47Z

0

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).....

edited Nov 17, 2011 at 22:04

answered Nov 17, 2011 at 21:23

marc_s

760k186 gold badges1.4k silver badges1.5k bronze badges

1 Comment

marc_s Over a year ago

Well duh - if your column is of type NTEXT, of course the XML functions won't work! I just assumed it was an XML column (since you said so!) First of all: don't use NTEXT anymore - it's deprecated - if at all, use nvarchar(max) and if it's a column containing XML - USE XML ...

Collectives™ on Stack Overflow

how to modify xml node in sql server

2 Answers 2

2 Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related