Update XML attribute value using another XML attribute value

  • Hardy21

    SSCrazy Eights

    Points: 9708

    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

    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

    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

    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

    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

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

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