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.680</core:MonetaryAmount>
</core:TotalTaxAmount>
Whatever the value is, it needs to be zero after the update.
<core:TotalTaxAmount>
<core:MonetaryAmount>0</core:MonetaryAmount>
</core:TotalTaxAmount>
Modify() function
The following is a good guide to using the XML modify() statement in TSQL
UPDATE
[dbo].[CA_SOP_EINVOICE_Output]
SET
XMLToSend.modify('
replace value of
(//core:TotalTaxAmount/core:MonetaryAmount[text()!="0"])[1]
with ("0")')
WHERE
[email protected] AND
[email protected]
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)
The target of 'replace value of' must be a non-metadata attribute or an
element with simple typed content
(//core:TotalTaxAmount/core:MonetaryAmount[text()!="0"]/text())[1]
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
[email protected]
AND [email protected]
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
[email protected] AND
[email protected]
;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
[email protected] AND
[email protected]
END
ROLLBACK TRANSACTION