Monday, June 4, 2012

Modify XML Attribute value in SQL Server


     DECLARE @xmlData xml
    SET @xmlData=convert(xml,
    ' <Data>
      <Employee>
        <Item Section="1" primaryKeyId="11" name="HD1">
          <Details>
            <Column surname="Khirsaria" firstname="Hiren" salary="10000" />
          </Details>
        </Item>
        <Item Section="2" primaryKeyId="12" name="HD2">
          <Details>
            <Column surname="AAA" firstname="aaa"  salary="8000" />
          </Details>
        </Item>
        <Item Section="2" primaryKeyId="14" name="HD3">
          <Details>
            <Column surname="BBB" firstname="bbb"  salary="8000" />
          </Details>
        </Item>
      </Employee>
    </Data>
    ')

SELECT @xmlData

SET @xmlData.MODIFY('
  replace value of (/Data/Employee/Item[1]/Details/Column/@salary)[1]
  with     "20000" ')


set @xmlData.MODIFY(' replace value of (/Data/Employee/Item[2]/Details/Column/@surname)[1]   with     "Hello" ')

SELECT @xmlData

No comments:

Post a Comment