Update all XML element nodes in an XML field of a SQL table

I wrote an electronic invoicing solution for Dynamics GP a long time ago. The XML invoices of various formats are stored against the invoice number in the database. The column was upgraded to XML type when SQL sever started supporting it in SQL 2005. Although it is possible to cast a varchar into XML data type, it makes it simpler to work with as a native XML data type.

Recently an issue surfaced where the tax amount on the invoice lines needed to be zeroed for a set of invoices, as the XML values had been incorrectly generated. Once the invoices had been corrected (only a dozen), they could be marked for retransmission. But how to quickly and easily update the invoices?

We have been using XML functionality in SQL server for another project recently, so it seemed natural to address the issue with with the inbuilt XML functions of SQL server.

Documents

The nodes in the document all look like this:

<core:TotalTaxAmount>  <core:MonetaryAmount>7.680core:MonetaryAmount>core:TotalTaxAmount>

Whatever the value is, it needs to be zero after the update.

<core:TotalTaxAmount>  <core:MonetaryAmount>0core:MonetaryAmount>core:TotalTaxAmount>

Modify() function

The following is a good guide to using the XML modify() statement in TSQL

Using the modify() method: replace value of

Using this as a guide I end up with the xPath looking like this

UPDATE [dbo].[CA_SOP_EINVOICE_Output]SETXMLToSend.modify('replace value of (//core:TotalTaxAmount/core:MonetaryAmount[text()!="0"])[1]with ("0")')WHERE SOPNUMBE=@SOPNUMBE ANDSOPTYPE=@SOPTYPE

This gives us a name space issue error due to the :core namespaces

XQuery [dbo.CA_SOP_EINVOICE_Output.XMLToSend.modify()]: The name "core" does not denote a namespace.

This is easily resolved by specifying the namespace with

;WITH XMLNAMESPACES('rrn:org.xcbl:schemas/xcbl/v4_0/core/core.xsd' AS core)

Next we get an error of

The target of 'replace value of' must be a non-metadata attribute or an element with simple typed content

This is due to the schema not defining a type for our node, so just update it as text, by adding text()

(//core:TotalTaxAmount/core:MonetaryAmount[text()!="0"]/text())[1]

Only one replace is allowed at a time, this is a real shame as it makes an otherwise trivial statement into a script. To make it work for this problem, a loop is created that will keep updating the first node it finds until all the nodes have been set to zero. This then gives us the final script to do the task as shown below:

DECLARE @SOPTYPE smallint
DECLARE @SOPNUMBE CHAR(21)

DECLARE @KeepLooping bit

SELECT @SOPTYPE=3, @SOPNUMBE='[Enter Doc Number]'

BEGIN TRANSACTION

-- Declare the namespace we are using before each select/update
;WITH XMLNAMESPACES('rrn:org.xcbl:schemas/xcbl/v4_0/core/core.xsd' AS core) 
SELECT @KeepLooping= XMLToSend.exist('//core:TotalTaxAmount/core:MonetaryAmount!=0')  
FROM [dbo].[CA_SOP_EINVOICE_Output]
WHERE 
SOPNUMBE=@SOPNUMBE 
AND SOPTYPE=@SOPTYPE

WHILE
(@KeepLooping=1)
BEGIN

    -- Do the update on first non-matching node each time until they are all done
    ;WITH XMLNAMESPACES('rrn:org.xcbl:schemas/xcbl/v4_0/core/core.xsd' AS core)
    UPDATE 
    [dbo].[CA_SOP_EINVOICE_Output]
    SET
    XMLToSend.modify('
    replace value of 
    (//core:TotalTaxAmount/core:MonetaryAmount[text()!="0"]/text())[1]
    with ("0")')
    WHERE 
    SOPNUMBE=@SOPNUMBE AND
    SOPTYPE=@SOPTYPE

    ;WITH XMLNAMESPACES('rrn:org.xcbl:schemas/xcbl/v4_0/core/core.xsd' AS core) 
    SELECT @KeepLooping= 
           XMLToSend.exist('//core:TotalTaxAmount/core:MonetaryAmount[text()!="0"]')  
    FROM [dbo].[CA_SOP_EINVOICE_Output]
    WHERE 
    SOPNUMBE=@SOPNUMBE AND 
    SOPTYPE=@SOPTYPE

END
ROLLBACK TRANSACTION

That was another interesting dip into SQL Server support for XML!