Update XML attribute value using another XML attribute value

Question

Update XML attribute value using another XML attribute value

Hardy21

SSCrazy Eights

Points: 9708
More actions
January 1, 2012 at 11:00 pm

#391229

Hello,
I am using SQL Server 2008 R2. I have a requirement where I need to update one XML attribute value using another XML's same attribute's value.
XML structure is like:
<Data>
<Items StatusId="10">
<Item Id="3FF6AD1F-D6F0-43E4-8294-59D678DF478A" Name="HD 1" >
<ExtraColumns>
<Column RecordType="Sub" IsRecordExist="1" />
</ExtraColumns>
</Item>
<Item Id="0F88289C-CCD4-47AB-AFC7-54A7F83A1019" Name="HD 2" >
<ExtraColumns>
<Column RecordType="Sub" IsRecordExist="1" />
</ExtraColumns>
</Item>
</Items>
</Data>
I would like to change the "IsRecordExist" attribute value using another XML (which is generated by other process) - same structure. We can use "Id" column as Primary Key. Is there any way to do the same?
Thanks

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

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

LutzM SSC Guru Points: 107049 More actions · Answer 1

Here's a solution based on Jacobs XML Lab.

It need to be noticeed that this will modify only one attribute. If you need to modify more/all attributes and the xml structure is rather large it might be more efficient to shred the xml into a relational table, modifiy the data using standard SQL and recreate the xml structure using FOR XML.

DECLARE @xml XML

SET @xml='<Data>

<Items StatusId="10">

<Item Id="3FF6AD1F-D6F0-43E4-8294-59D678DF478A" Name="HD 1" >

<ExtraColumns>

<Column RecordType="Sub" IsRecordExist="1" />

</ExtraColumns>

</Item>

<Item Id="0F88289C-CCD4-47AB-AFC7-54A7F83A1019" Name="HD 2" >

<ExtraColumns>

<Column RecordType="Sub" IsRecordExist="1" />

</ExtraColumns>

</Item>

</Items>

</Data>'

DECLARE @var VARCHAR(36)

DECLARE @val VARCHAR(20)

SELECT @var = '3FF6AD1F-D6F0-43E4-8294-59D678DF478A'

SELECT @val = '6'

SET @xml.modify('

replace value of (

/Data/Items/Item[@Id=sql:variable("@var")]/ExtraColumns/Column/@IsRecordExist

)[1]

with sql:variable("@val")

')

SELECT @xml

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

Hardy21 SSCrazy Eights Points: 9708 More actions · Answer 2

LutzM (1/2/2012)
Here's a solution based on Jacobs XML Lab.
It need to be noticeed that this will modify only one attribute. If you need to modify more/all attributes and the xml structure is rather large it might be more efficient to shred the xml into a relational table, modifiy the data using standard SQL and recreate the xml structure using FOR XML.

Thanks for your reply.

I need to modify only one attribute value but I need to use another XML from which I get the value. Is there any way to do the same?

Thanks

LutzM SSC Guru Points: 107049 More actions · Answer 3

What does the other xml file look like and what value/attribute do you need to extract from it?

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

Hardy21 SSCrazy Eights Points: 9708 More actions · Answer 4

LutzM (1/2/2012)
What does the other xml file look like and what value/attribute do you need to extract from it?

I would like to change the "IsRecordExist" attribute value using another XML (which is generated by other process) - same structure. We can use "Id" column as Primary Key. Is there any way to do the same?

Thanks