Replace XML node

Question

I have a table in which there is a column named "ServiceConfig" with datatype xml.

I have a record with xml like below :

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

Now, I want to replace <service name="pro" /> with <service name="pro" username="u" password="p"/>. So the resultant XML will be

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

How do I achieve it?

davmos · Accepted Answer · 2013-05-22 08:50:51Z

2

One way would be to add the new attributes like 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 from @xmltest

SET @xml.modify('insert (attribute username {sql:variable("@username")}, 
                         attribute password {sql:variable("@password")}) 
                         as last into (/Config/services/service)[1]')

UPDATE @xmltest SET ServiceConfig = @xml

SELECT * FROM @xmltest

Click here to go to SQL Fiddle demo of above code.

edited May 22, 2013 at 8:50

answered May 21, 2013 at 11:15

davmos

9,6074 gold badges45 silver badges46 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Replace XML node

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related