How to Update an Xml Attribute Value in an Xml Variable Using T-Sql

  • Home
  • Languages
  • SQL
  • How to Update an Xml Attribute Value in an Xml Variable Using T-Sql

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

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

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

Updating Xml attributes with new values in a SQL Server 2008 table

From the early versions of your question it looks like your XML actually is on different rows in a table. If that is the case you can use this.

update YourTable set
  XMLText.modify('replace value of (/Identification/@Age)[1] with "40"')
where XMLText.value('(/Identification/@Age)[1]', 'int') = 30

Working sample using a table variable.

declare @T table(XMLText xml)

insert into @T values('<Identification Name="John"  Family="Brown"   Age="30" />')
insert into @T values('<Identification Name="Smith" Family="Johnson" Age="35" />') 
insert into @T values('<Identification Name="Jessy" Family="Albert"  Age="60" />')
insert into @T values('<Identification Name="Mike"  Family="Brown"   Age="23" />')
insert into @T values('<Identification Name="Sarah" Family="Johnson" Age="30" />')

update @T set
  XMLText.modify('replace value of (/Identification/@Age)[1] with "40"')
where XMLText.value('(/Identification/@Age)[1]', 'int') = 30

select *
from @T

Get and update XML value with a certain attribute

Unfortunately, MS SQL Server XQuery implementation is not fully conformant with the XQuery standards. That's why XQuery .modify() method makes one update at a time.

So we can update XML in a loop by using the XQuery .exist() method until there is nothing to update.

Please up-vote my suggestion: https://feedback.azure.com/d365community/idea/153a9604-7025-ec11-b6e6-000d3a4f0da0

SQL

-- DDL and sample data population, start
DECLARE @tbl TABLE  (ID INT IDENTITY PRIMARY KEY, xmldata XML);
INSERT INTO @tbl (xmldata) VALUES
(N'<array>
    <foo>
        <property name="Name">Foo 1</property>
        <property name="Gender">M</property>
        <property name="DOB">2020-01-01</property>
    </foo>
    <foo>
        <property name="Name">Foo 2</property>
        <property name="Gender">M</property>
        <property name="DOB">2020-01-02</property>
    </foo>
</array>'),
(N'<array>
    <foo>
        <property name="Name">Foo 3</property>
        <property name="Gender">F</property>
        <property name="DOB">2020-01-03</property>
    </foo>
</array>');
-- DDL and sample data population, end

DECLARE @from VARCHAR(30) = '1'
   , @to VARCHAR(30) = '1'
   , @UPDATE_STATUS BIT = 1;

-- before
SELECT * FROM @tbl
WHERE xmldata.exist('/array/foo/property[@name="Name"][(./text())[1] ne sql:variable("@from")]') = 1;

WHILE @UPDATE_STATUS > 0
BEGIN
   UPDATE t
   SET xmldata.modify('replace value of (/array/foo/property[@name="Name"][(./text())[1] ne sql:variable("@from")]/text())[1]
      with (sql:variable("@to"))')
   FROM @tbl AS t
   WHERE xmldata.exist('/array/foo/property[@name="Name"][(./text())[1] ne sql:variable("@from")]') = 1;
    
   SET @UPDATE_STATUS = @@ROWCOUNT;
   PRINT @UPDATE_STATUS;
END;

-- after
SELECT * FROM @tbl;

How can I use XQuery to update an attribute value in an XML variable?

Without the actual XML this is flying blind, but you might be looking for this:

set @x.modify('declare namespace abc="http://abcsystems.com/";
replace value of
(/abc:App/abc:Client/abc:Addresses/abc:PostalAddress/@AddressLine1)[1] with "555 Service Rd."')

The (xpath here)[1] is typically used to enforce one single node

UPDATE: Working example

declare @x xml=
N'<abc:App xmlns:abc="http://abcsystems.com/">
  <abc:Client>
    <abc:Addresses>
      <abc:PostalAddress AddressLine1="test" />
    </abc:Addresses>
  </abc:Client>
</abc:App>';

set @x.modify('declare namespace abc="http://abcsystems.com/";
replace value of
(/abc:App/abc:Client/abc:Addresses/abc:PostalAddress/@AddressLine1)[1] 
with "555 Service Rd."');

select @x;

The result

<abc:App xmlns:abc="http://abcsystems.com/">
  <abc:Client>
    <abc:Addresses>
      <abc:PostalAddress AddressLine1="555 Service Rd." />
    </abc:Addresses>
  </abc:Client>
</abc:App>

How to modify an xml variable with a conditional/where clause

Here is the answer for the "..REAL LIFE case...". I modified the input XML by adding some additional elements. The XQuery was adjusted accordingly.

SQL

-- DDL and sample data population, start
DECLARE @xml xml = N'<Container>
  <city>Miami</city>
  <state>FL</state>
  <Collection>
    <foo>One</foo>
    <bar>true</bar>
    <baz>false</baz>
  </Collection>
  <Collection>
    <foo>Two</foo>
    <bar>true</bar>
    <baz>true</baz>
  </Collection>
  <Collection>
    <foo>Three</foo>
    <bar>true</bar>
    <baz>true</baz>
  </Collection>
</Container>';
-- DDL and sample data population, end

-- before
SELECT @xml AS [before];

-- update single element
SET @xml.modify('replace value of (/Container/Collection[upper-case((foo/text())[1]) = "TWO"]/bar/text())[1] with "false"')

-- after
SELECT @xml AS [After];

-- Method #1, via FLWOR expression
-- update all <bar> elements with the false' value
DECLARE @bar VARCHAR(10) = 'false';

SET @xml = @xml.query('<Container>
{
   for $x in /Container/*[not(local-name(.)=("Collection"))]
   return $x
}
{
    for $y in /Container/Collection
    return <Collection>
    {
        for $z in $y/*
        return 
        if (not(local-name($z) = ("bar"))) then $z
        else 
        (
            element bar {sql:variable("@bar")}
        )
    }
    </Collection>
}
</Container>');

Updating XML attribute in SQL Server XML column

You should use the XQuery functions - not string together your XML like this....

Try this instead:

DECLARE @newquantity INT = 55

UPDATE dbo.Customers
SET voucherXML.modify('replace value of (/ArrayOfCampaignVoucher/CampaignVoucher[@VouCode="Vouc002"]/@Qty)[1] with sql:variable("@NewQuantity") ')

This works for me - the Qty attribute of the node with VouCode="Vouc002" gets updated to the value I defined before in a SQL variable

Update: to insert a new <CampaignVoucher> entry, use XQuery something like this:

UPDATE dbo.Customers
SET voucherXML.modify('insert <CampaignVoucher VouCode="Vouc003" Qty="42" /> 
                       as last into (/ArrayOfCampaignVoucher)[1]')

Inserting new XML attribute using .modify() - how to make it last attribute?

I came to the decision with insert, and then delete.

declare @spTemp table(PolicyXML xml);

declare @policyType nvarchar(max)='default',
        @source nvarchar(max)='ZZ_MYNEWVALUE_ZZ',
        @groupId nvarchar(max)='00000000-0000-0000-0000-000000000000',
        @accessPermissions nvarchar(max)='31',
        @domainId nvarchar(max)='00000000-0000-0000-0000-000000000000',
        @px xml='
<policies xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
          xmlns:xsd="http://www.w3.org/2001/XMLSchema">
  <policy type="default">
    <groups>
      <group id="00000000-0000-0000-0000-000000000000"
             accessPermissions="31"
             domainId="00000000-0000-0000-0000-000000000000" />
    </groups>
  </policy>
</policies>';

insert into @spTemp(PolicyXML) values(@px);

UPDATE @spTemp SET PolicyXML.modify('
insert (
for $g in /policies/policy[@type=sql:variable("@policyType")]/groups
          /group[@id=sql:variable("@groupId") and not (@source) and
                 @accessPermissions=sql:variable("@accessPermissions") and
                 @domainId=sql:variable("@domainId")]
  return
    <group>
      {$g/@*}
      {attribute source {sql:variable("@source")}}
    </group>
) after (/policies/policy[@type=sql:variable("@policyType")]/groups
         /group[@id=sql:variable("@groupId") and not (@source) and
                @accessPermissions=sql:variable("@accessPermissions") and
                @domainId=sql:variable("@domainId")])[1]')
OUTPUT INSERTED.PolicyXML as ins_px,DELETED.PolicyXML del_px
WHERE PolicyXML.exist('
/policies/policy[@type=sql:variable("@policyType")]/groups
/group[@id=sql:variable("@groupId") and not (@source) and
       @accessPermissions=sql:variable("@accessPermissions") and
       @domainId=sql:variable("@domainId")]')=1;

UPDATE @spTemp SET PolicyXML.modify('
delete /policies/policy[@type=sql:variable("@policyType")]/groups
       /group[@id=sql:variable("@groupId") and not (@source) and
              @accessPermissions=sql:variable("@accessPermissions") and
              @domainId=sql:variable("@domainId")]')
OUTPUT INSERTED.PolicyXML as ins_px,DELETED.PolicyXML del_px
WHERE PolicyXML.exist('
/policies/policy[@type=sql:variable("@policyType")]/groups
/group[@id=sql:variable("@groupId") and not (@source) and
       @accessPermissions=sql:variable("@accessPermissions") and
       @domainId=sql:variable("@domainId")]')=1;

How to update XML node using XML DML in SQL Server

Yes, but you must add the filter as a predicate:

Your mockup. I added an ID column and three cases

  1. One "efg"
  2. No "efg"
  3. Multiple occurances of "efg"

--Check it out

DECLARE @xmlTable table (ID INT IDENTITY, xmlfield xml)

INSERT INTO @xmlTable (xmlfield) 
VALUES (
    '<ArrayOfFieldInfo>
        <FieldInfo>
            <Name>abc</Name>
            <FriendlyName>friend2</FriendlyName>
        </FieldInfo>
        <FieldInfo>
            <Name>efg</Name>
            <FriendlyName>friend1</FriendlyName>
        </FieldInfo>
    </ArrayOfFieldInfo>'
)
,(
    '<ArrayOfFieldInfo>
        <FieldInfo>
            <Name>abc</Name>
            <FriendlyName>friend2</FriendlyName>
        </FieldInfo>
        <FieldInfo>
            <Name>xyz</Name>
            <FriendlyName>friend1</FriendlyName>
        </FieldInfo>
    </ArrayOfFieldInfo>'
)
,(
    '<ArrayOfFieldInfo>
        <FieldInfo>
            <Name>efg</Name>
            <FriendlyName>friend2</FriendlyName>
        </FieldInfo>
        <FieldInfo>
            <Name>efg</Name>
            <FriendlyName>friend1</FriendlyName>
        </FieldInfo>
    </ArrayOfFieldInfo>'
)

--Let's use some variables to get this more generic

DECLARE @SearchName NVARCHAR(100)=N'efg';
DECLARE @ReplaceWith NVARCHAR(100)=N'ABCDEFG';

--The query

UPDATE @xmlTable
SET xmlfield.modify('replace value of 
                     (/ArrayOfFieldInfo
                      /FieldInfo[Name=sql:variable("@SearchName")]
                      /FriendlyName/text())[1] 
                     with sql:variable("@ReplaceWith")')
WHERE xmlfield.exist('/ArrayOfFieldInfo
                      /FieldInfo[Name=sql:variable("@SearchName")]')=1;

--Check the result

SELECT * 
FROM @xmlTable t
ORDER BY t.ID;

The first case will get the value replaced.

No replacements in the second case.

Just ONE (the first) occurance in the third case is replaced.

The idea in short:

The XML-DML-method replace value of needs a singleton. You tried to filter with a WHERE externally, but you must tell the engine which node within the XML you want to address. It is the <FieldInfo>, where the interal <Name> equals your search phrase. Below this <FieldInfo> we pick the <FriendlyName> and replace the first one we've found.

I added a WHERE using .exist(). This will/can speed up the process as it will reduce the .modify() to rows, which have at least one fitting target element.

And you can see, that .modify() is limited to just one change per call. This can be very annoying... A workaround might be to shred and recreate the XML or to use XQuery-FLWOR.

How can I find and update a node attribute value in an XML document based on match of a different attribute value in the same node?

Okay, after your last question you decided to got the CURSOR route, correct? ;-)

Try it like this.

--Your mockup up
Declare @SearchID varchar(35) = '5280301.2019050148902.00023';

Declare @DocListXML XML=
N'<JobList ListItems="7">
  <Job JobFriendlyName="EMAIL INVOICES">
    <DocumentList>
      <Document Doc="1" ID="5280301.2019050148902.00020" Date="05-03-2019" Status="NEW" />
      <Document Doc="2" ID="5280301.2019050148902.00022" Date="05-03-2019" Status="NEW" />
      <Document Doc="3" ID="5280301.2019050148902.00023" Date="05-03-2019" Status="NEW" />
      <Document Doc="4" ID="5280301.2019050104301.00055" Date="05-02-2019" Status="NEW" />
      <Document Doc="5" ID="5280301.2019050104301.00056" Date="05-02-2019" Status="NEW" />
    </DocumentList>
  </Job>
  <Job JobFriendlyName="INVOICES">
    <DocumentList>
      <Document Doc="6" ID="5280300.2019050148901.00001" Date="05-03-2019" Status="NEW" />
      <Document Doc="7" ID="5280300.2019050148901.00002" Date="05-03-2019" Status="NEW" />
    </DocumentList>
  </Job>
</JobList>';

--This is the modifying command

SET @DocListXML.modify(N'replace value of (/JobList
                                           /Job
                                           /DocumentList
                                           /Document[@ID=sql:variable("@SearchID")]
                                           /@Status)[1] with "OLD"');

--check the result

SELECT @DocListXML;

Hint 1: This works only in cases where there is just one document with the given ID per XML!

Hint 2: You might shorten the command to this:

SET @DocListXML.modify(N'replace value of (//Document[@ID=sql:variable("@SearchID")]/@Status)[1] with "OLD"');

The deep search (triggered by the double slash // at the beginning of the XPath) tells the engine to find a <Document> fullfilling the predicate anywhere in your XML.

Update an XML node that is stored as string in Database

You can try the following methods.

It shows how to modify the <salary> element value when:

  • xmldata column is XML data type.
  • xmldata column is NVARCHAR(MAX) data type.

SQL #1

-- DDL and sample data population, start
DECLARE @tbl TABLE (ID INT IDENTITY PRIMARY KEY, xmldata XML);
INSERT INTO @tbl ( xmldata) VALUES
(N'<?xml version="1.0" encoding="utf-16"?>
<EmployeeDetail xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
                xmlns:xsd="http://www.w3.org/2001/XMLSchema">
    <Name>MyName</Name>
    <Age>20</Age>
    <Salary>15000</Salary>
</EmployeeDetail>');
-- DDL and sample data population, end

-- before
SELECT * FROM @tbl;

DECLARE @salary VARCHAR(10) = '10000';

UPDATE @tbl
SET xmldata.modify('replace value of 
    (/EmployeeDetail/Salary/text())[1] 
    with (sql:variable("@salary"))
');

-- after
SELECT * FROM @tbl;

SQL #2

-- DDL and sample data population, start
DECLARE @tbl TABLE (ID INT IDENTITY PRIMARY KEY, xmldata NVARCHAR(MAX));
INSERT INTO @tbl ( xmldata) VALUES
(N'<?xml version="1.0" encoding="utf-16"?>
<EmployeeDetail xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
                xmlns:xsd="http://www.w3.org/2001/XMLSchema">
    <Name>MyName</Name>
    <Age>20</Age>
    <Salary>15000</Salary>
</EmployeeDetail>');

DECLARE @realXML TABLE (ID INT, realXML XML);
-- DDL and sample data population, end

-- populate temporary table
INSERT INTO @realXML
SELECT ID, cast(xmldata AS XML)
FROM @tbl;

DECLARE @salary VARCHAR(10) = '10000';

/*
Unfortunately, we can not assign from a xml.modify() statement. 
Modify works on a variable and a column only. 
Also, we cannot use modify on a CAST().
*/
-- update temporary table
UPDATE @realXML
SET realXML.modify('replace value of 
            (/EmployeeDetail/Salary/text())[1] 
            with (sql:variable("@salary"))
        ');

-- update real table 
UPDATE tbl
SET xmldata = CAST(t.realXML AS NVARCHAR(MAX))
FROM @realXML AS t INNER JOIN @tbl AS tbl
ON tbl.ID = t.ID;

Related Topics

How to Count Most Consecutive Occurrences of a Value in a Column in SQL Server

Create Enum in SQL Server

Query to Check Overlapping Ranges in SQL Server

How to Use Oracle Outer Join with a Filter Where Clause

What Is The Query to Get "Related Tags" Like in

Wordpress: Automatically Delete Posts That Are X Days Old

Update Row When Matching Row Exists in Another Table

Oracle Unique Constraint Based on Column Value

Whats The Best Sqlite Data Type for a Long String

Executing SQL Query on Multiple Databases

How to Set a Default Value for One Column in SQL Based on Another Column

How to Search for Names with Apostrophe in SQL Server

Does Sqlite Support Replication

Sql Order by on Multiple Column

Postgresql: Table Name/Schema Confusion

Is It Faster to Check If Length = 0 Than to Compare It to an Empty String

Indexed View Vs Indexes on Table

Full Text Search Installed or Not


Leave a reply



Submit