Modify XML attribute value based on matching data

  • Hardy21

    SSCrazy Eights

    Points: 9708

    Hello,

    I am using SQL Server 2008 R2 and facing an issue to update XML attribute value. I have a requirement to update XML attribute value as per the maching data.

    For example my sample XML is as below:

    declare @p1 xml

    set @p1=convert(xml,

    '

    <Data>

    <Datas>

    <Item Section="1" primaryKeyId="11" name="HD1">

    <ExtraDetails>

    <Column surname="dd" salary="10000" />

    </ExtraDetails>

    </Item>

    <Item Section="2" primaryKeyId="12" name="HD2">

    <ExtraDetails>

    <Column surname="dd" salary="8000" />

    </ExtraDetails>

    </Item>

    <Item Section="2" primaryKeyId="14" name="HD3">

    <ExtraDetails>

    <Column surname="dd" salary="8000" />

    </ExtraDetails>

    </Item>

    </Datas>

    </Data>

    ')

    select @p1

    I need to modify salary attribute value of primaryKeyId=12 & primaryKeyId=14 from 8000 to 12000. I know I need to use Modify method and use [replace value of ] syntax for the same. But don't know how to use it.

    Can you please help me to resolve the issue or guide me for the same?

    Thanks

  • seth delconte

    SSCertifiable

    Points: 6388

    set @p1.modify('replace value of (/Data/Datas/Item[@primaryKeyId = ("12")]/ExtraDetails/Column/@salary)[1] with 1200')

    set @p1.modify('replace value of (/Data/Datas/Item[@primaryKeyId = ("14")]/ExtraDetails/Column/@salary)[1] with 1200')

    _________________________________
    seth delconte
    http://sqlkeys.com

  • Hardy21

    SSCrazy Eights

    Points: 9708

    seth delconte (6/4/2012)


    set @p1.modify('replace value of (/Data/Datas/Item[@primaryKeyId = ("12")]/ExtraDetails/Column/@salary)[1] with 1200')

    set @p1.modify('replace value of (/Data/Datas/Item[@primaryKeyId = ("14")]/ExtraDetails/Column/@salary)[1] with 1200')

    Thanks

    Thanks

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

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