How to update an xml attribute value in an xml variable using t-sql?

Question

Let's have a sample snippet:

DECLARE @xml XML = N'
<a abb="122">
    <b>
    </b>
</a>
';
SELECT @xml;

--need to update abb to be 344 in @xml here

SELECT @xml;

I don't know how to update that attribute abb's value.

Solution 1

set @xml.modify('replace value of (/a/@abb)[1] with 344')

Read more about it here. XML Data Modification Language (XML DML)

Solution 2

For those wanting to update from a VARIABLE, here is an example...

DECLARE @XML XML = '<Event auditId="00000000-0000-0000-0000-000000000000" createdOn="2018-12-29T19:54:01.140" retryCount="0" version="1.0.0">
  <DataSource machineName="LONELYMOUNTAIN">SqlBroker.ApplicationSender</DataSource>
  <Topic>
    <Filter>Meter.Created</Filter>
  </Topic>
  <Name>Meter.Created</Name>
  <Contexts>
    <Context>
      <Name>Meter</Name>
      <Key>
        <Id>1</Id>
        <MeterGlobalId>DC3995A1-790B-E911-AC2F-D4BED9FD41CB</MeterGlobalId>
      </Key>
    </Context>
  </Contexts>
  <Payload type="Entity">
    <Device>
      <Id>1</Id>
      <DeviceGlobalId>27C03D8C-790B-E911-AC2F-D4BED9FD41CB</DeviceGlobalId>
      <DeviceName>Station X</DeviceName>
    </Device>
    <Meter>
      <Id>1</Id>
      <MeterGlobalId>DC3995A1-790B-E911-AC2F-D4BED9FD41CB</MeterGlobalId>
      <DeviceId>1</DeviceId>
      <MeterName>Meter Awesome</MeterName>
      <MeterNumber>1111</MeterNumber>
    </Meter>
  </Payload>
</Event>'

DECLARE @Audit TABLE (Id UNIQUEIDENTIFIER);
DECLARE @AuditId UNIQUEIDENTIFIER;

-- GET Id
SELECT @AuditId = NEWID()

-- REPLACE Id
SET @XML.modify('replace value of (/Event/@auditId)[1] with sql:variable("@AuditId")')

SELECT @XML

Comments

[Nam G VU]

Just want to ask more advance. How can we make updates to all attribute abb instead of just one node?

[Nam G VU]

I've post that question here stackoverflow.com/questions/5347890/…

[Prisoner ZERO]

SIDE NOTE: This only works when a previous value exists