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

• LutzM

  SSC Guru

  Points: 107049

  More actions

  January 2, 2012 at 3:07 am

  #1427351

  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

  January 2, 2012 at 6:05 am

  #1427410

  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

  January 2, 2012 at 6:18 am

  #1427413

  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

  January 2, 2012 at 9:27 pm

  #1427543

  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