How can I update multiple XML fields with MSSQL?

Question

Table Name : TBL_CLIENTS Table Field : XMLDATA

<REPORT xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" DESCRIPTION="TestClient" FILENUM="1234">
  <!--<REPORT DESCRIPTION="TestClient" FILENUM="1234">-->
  <TRACKING>
    <FIRSTNAME>Bobby</FIRSTNAME>
    <LASTNAME>Butcher</LASTNAME>
  </TRACKING>
</REPORT>

I want to change both the FIRSTNAME and LASTNAME. Is there anyway I can do it in a single query? The only way I can figure it out is using two queries.

UPDATE TBL_CLIENTS
SET [XMLDATA].modify('replace value of (/REPORT/TRACKING/FIRSTNAME/text())  [1] with ("Franny")')
WHERE ORDERID = 5

and

UPDATE TBL_CLIENTS
SET [XMLDATA].modify('replace value of (/REPORT/TRACKING/LASTNAME/text())[1]  with ("Farmer")')
WHERE ORDERID = 5

Dan Field · Accepted Answer · 2015-06-08 19:45:14Z

It's not possible - per MSDN, replace has to work on a single instance of an XML node - but you could avoid doing two UPDATEs on the table this way:

DECLARE @doc xml = '<REPORT xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" DESCRIPTION="TestClient" FILENUM="1234">
  <!--<REPORT DESCRIPTION="TestClient" FILENUM="1234">-->
  <TRACKING>
    <FIRSTNAME>Bobby</FIRSTNAME>
    <LASTNAME>Butcher</LASTNAME>
  </TRACKING>
</REPORT>';

DECLARE @t table (xmldata xml);

insert @t (xmldata) values (@doc);

-- grab the XML data from the table for manipulation...
DECLARE @xmlData xml;
SELECT @xmlData = xmldata FROM @t;

set @xmlData.modify('replace value of (/REPORT/TRACKING/FIRSTNAME/text())  [1] with ("Franny")')                   
set @xmlData.modify('replace value of (/REPORT/TRACKING/LASTNAME/text())   [1] with ("Farmer")')

-- now we only need to do one update on the table itself.
UPDATE @t 
SET [XMLDATA] = @xmlData

SELECT * FROM @t;

I know this isn't exactly what you were asking for, but it should result in less locking time and better performance on the table - if that's something you're aiming for.

Collectives™ on Stack Overflow

How can I update multiple XML fields with MSSQL?

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