how to update a xml field table in sql?

Question

I have a XML column in a table that looks like this:

<word A="al"   B="h"   C="Ps" />
<word A="has"  B="es"  C="Pf" /> 
<word A="mom"  B="es"  C="Ph" />

I want to update this field like this:

<word A="al"   B="B1"   C="C1" /> 
<word A="has"  B="B2"  C="C1" /> 
<word A="mom"  B="B2"  C="C2" />

I want to do by a function in SQL Server.

Thanks!

I kind of understand the B1/B2 assignment, but the C1/C2 assignments make no sense to me. Can you please elaborate and explain where the coefficients for B and C come from? — Aaron Bertrand
– Aaron Bertrand, Commented Apr 24, 2012 at 13:08

weenoid · Accepted Answer · 2012-04-24 08:13:08Z

4

As Mikael states you need to describe the logic behind the update. But for the expected output above, the following should work:

DECLARE @Words xml
SELECT @Words = '
<word A="al"   B="h"   C="Ps" />
<word A="has"  B="es"  C="Pf" />
<word A="mom"  B="es"  C="Ph" />'

SET @Words.modify('replace value of(/word[@A = "al"]/@B)[1] with "B1"')
SET @Words.modify('replace value of(/word[@A = "al"]/@C)[1] with "C1"')
SET @Words.modify('replace value of(/word[@A = "has"]/@B)[1] with "B2"')
SET @Words.modify('replace value of(/word[@A = "has"]/@C)[1] with "C1"')
SET @Words.modify('replace value of(/word[@A = "mom"]/@B)[1] with "B2"')
SET @Words.modify('replace value of(/word[@A = "mom"]/@C)[1] with "C2"')

SELECT @Words

answered Apr 24, 2012 at 8:13

weenoid

1,1962 gold badges11 silver badges24 bronze badges

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

Comments

Chotip · Accepted Answer · 2012-04-24 08:08:40Z

1

Please try the "replace value of" in your XQuery.
Ref: http://msdn.microsoft.com/en-us/library/ms190675.aspx

answered Apr 24, 2012 at 8:08

Chotip

541 bronze badge

Collectives™ on Stack Overflow

how to update a xml field table in sql?

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related