XQuery

Question

XQuery

liya.tansky

SSC Enthusiast

Points: 100
More actions
November 24, 2010 at 12:24 am

#229048

Hi,
I have table with XML column.
Providing one of items in some node have a given value, I need to change several items within the same node.
example:
I look for id=2, if found I need to leave id as it is BUT to change value of NAME to "C"
<root>
<lang>....</lang>
<testRoot>
<test>
<id>1</id>
<name>A</name>
</test>
<test>
<id>2</id>
<name>B</name>
</test>
</testRoot>
</root>
Thanks in advance

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Sachin Nandanwar SSCertifiable Points: 7699 More actions · Answer 1

But what is the basis for your update?You want to update name to "C" wherever id=2 exists?

--------------------------------------------------------------------------------------------------
I am just an another naive wannabe DBA trying to learn SQL Server

liya.tansky SSC Enthusiast Points: 100 More actions · Answer 2

yes

if parameter @id = 2 then the result should be

<root>

<test>

</test>

<test>

</test>

</testRoot>

</root>

Eralper SSCarpal Tunnel Points: 4438 More actions · Answer 3

Hello

The below query reads XML into row data then updates and converts back to XML

declare @xml XML = '

<root>

<lang>....</lang>

<testRoot>

<test>

<id>1</id>

<name>A</name>

</test>

<test>

<id>2</id>

<name>B</name>

</test>

</testRoot>

</root>'

declare @id int = 2

declare @name varchar(10) = 'C'

;with cte as (

SELECT

[Test].value('id[1]/.','varchar(10)') AS id,

[Test].value('name[1]/.','varchar(10)') AS name

FROM @xml.nodes('/root/testRoot/test') Tests([Test])

)

select *

from (

select

id,

name = case when id = @id then @name else name end

from cte

) test for xml auto, root('testRoot'), elements

Eralper
SQL Server and T-SQL Tutorials and Articles
Microsoft Certification and Certification Exams

liya.tansky SSC Enthusiast Points: 100 More actions · Answer 4

Thanks, I've found something very eleganst.

Thought to share it:

create table #t (x xml)

set @x = '<root>

<test>

</test>

<test>

</test>

</testRoot>

</root>'

DECLARE @id int, @name varchar(50)

SET @id = 2

set @city = 'C'

update #t1 set x.modify('replace value of (/root/testRoot[id=sql:variable("@id")]/name/text())[1] with sql:variable("@city")')

Works like magic 🙂