sql - Replace XML node -

i have table in there column named "serviceconfig" datatype xml.

i have record xml below :

<config>   <services>     <service name="pro" />     <service name="postdata" />   </services> </config> 

now, want replace <service name="pro" /> <service name="pro" username="u" password="p"/>. resultant xml

<config>   <services>     <service name="pro" username="u" password="p"/>     <service name="postdata" />   </services> </config> 

how achieve it?

one way add new attributes this...

declare @xmltest table (serviceconfig xml) declare @username varchar(15) = 'u' declare @password varchar(15) = 'p' declare @xml xml  insert @xmltest values ('<config>                            <services>                              <service name="pro" />                              <service name="postdata" />                            </services>                          </config>')  select @xml = serviceconfig @xmltest  set @xml.modify('insert (attribute username {sql:variable("@username")},                           attribute password {sql:variable("@password")})                           last (/config/services/service)[1]')  update @xmltest set serviceconfig = @xml  select * @xmltest 

click here go sql fiddle demo of above code.