November 24, 2010 at 12:24 am
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
November 24, 2010 at 1:46 am
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
November 24, 2010 at 1:49 am
yes
if parameter @id = 2 then the result should be
<root>
<lang>....</lang>
<testRoot>
<test>
<id>1</id>
<name>A</name>
</test>
<test>
<id>2</id>
<name>C</name>
</test>
</testRoot>
</root>
November 24, 2010 at 4:47 am
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
November 24, 2010 at 5:09 am
Thanks, I've found something very eleganst.
Thought to share it:
create table #t (x xml)
set @x = '<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, @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 🙂
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply