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.
Add a comment
|
2 Answers
set @xml.modify('replace value of (/a/@abb)[1] with 344')
Read more about it here. XML Data Modification Language (XML DML)
3 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
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
