T-SQL to modify XML Data

Question

I will like to modify the value for TransactionAmount where TransactionAmount > 15000 using TSQL. Table has over 50K rows.

Could someone pls help me? I am having trouble finding any examples anywhere.

<BillingTransactionInfo xmlns="http://xed.com/bc/gx.billsactioninfomodel">
  <AccountingDate>2018-12-07T13:40:44</AccountingDate>
  <AccountingDay>7</AccountingDay>
  <AccountingMonth>12</AccountingMonth>
  <AccountingYear>2018</AccountingYear>
  <AccountNumber>PC:0049207</AccountNumber>
  <AccountType>insured</AccountType>
  <BillingReferenceNumber>50000018100</BillingReferenceNumber>
  <CustomerName>JOHN MCGEE</CustomerName>
  <GLMonth>12</GLMonth>
  <GLYear>2018</GLYear>
  <TransactionSubtypeCode>DirectBillMoneyReceivedTxn</TransactionSubtypeCode>
  <TransactionSubtypeDesc>Direct Bill Money Received</TransactionSubtypeDesc>
  <IssueDate>2018-12-07T13:40:37</IssueDate>
  <PaymentMethod>cash</PaymentMethod>
  <PolicyRiskState>AL</PolicyRiskState>
  <ReasonCode>Direct Bill Money Received</ReasonCode>
  <RecordCreationDate>2018-12-07T13:40:37</RecordCreationDate>
  <Source>BILLING</Source>
  <TransactionAmount>2570.77</TransactionAmount>
  <TransactionCreateDate>2018-12-07T13:40:37</TransactionCreateDate>
</BillingTransactionInfo>

How do you want to modify the values? It is easy to find all XMLs where the amount exceeds a given border, but you did not tell us, what else you want to place there... — Gottfried Lesigang
– Gottfried Lesigang, Commented Jan 18, 2019 at 7:43

Gottfried Lesigang · Accepted Answer · 2019-01-18 08:07:17Z

Your XML has got a default namespace. It is possible to use a wildcard (like *:ElementName), but it is recommended to declare namespaces.

Furthermore, the best way to filter for a value in cases, where you do not need that value is the native XML method .exist().

Try this:

WITH XMLNAMESPACES(DEFAULT('http://xed.com/bc/gx.billsactioninfomodel'))
UPDATE YourTable
SET TheXmlColumn.modify('replace value of (/BillingTransactionInfo/TransactionAmount/text())[1] with "12345"')
WHERE TheXmlColumn.exist('/BillingTransactionInfo[TransactionAmount > 15000]')=1;

You can read this as

Use the given default namespace, when there is no explicit namespace
Update your table and modify the XML in the stated way
But hit only rows where there exists a <BillingTransactionInfo> with a <TransactionAmount> higher then 15000.

Attention: Your sample does not look like this, but you must be sure, that your XML column will keep just one single <BillingTransactionInfo> as statet in your question!

Alan Burstein · Accepted Answer · 2019-01-18 15:41:54Z

Welcome to Stack Overflow. This should be rather simple. First lets generate 50K rows of sample data.

IF OBJECT_ID('tempdb..#things') IS NOT NULL DROP TABLE #things;

DECLARE @xml XML =
'<BillingTransactionInfo xmlns="http://xed.com/bc/gx.billsactioninfomodel">
  <AccountingDate>2018-12-07T13:40:44</AccountingDate>
  <AccountingDay>7</AccountingDay>
  <AccountingMonth>12</AccountingMonth>
  <AccountingYear>2018</AccountingYear>
  <AccountNumber>PC:0049207</AccountNumber>
  <AccountType>insured</AccountType>
  <BillingReferenceNumber>50000018100</BillingReferenceNumber>
  <CustomerName>JOHN MCGEE</CustomerName>
  <GLMonth>12</GLMonth>
  <GLYear>2018</GLYear>
  <TransactionSubtypeCode>DirectBillMoneyReceivedTxn</TransactionSubtypeCode>
  <TransactionSubtypeDesc>Direct Bill Money Received</TransactionSubtypeDesc>
  <IssueDate>2018-12-07T13:40:37</IssueDate>
  <PaymentMethod>cash</PaymentMethod>
  <PolicyRiskState>AL</PolicyRiskState>
  <ReasonCode>Direct Bill Money Received</ReasonCode>
  <RecordCreationDate>2018-12-07T13:40:37</RecordCreationDate>
  <Source>BILLING</Source>
  <TransactionAmount>2570.77</TransactionAmount>
  <TransactionCreateDate>2018-12-07T13:40:37</TransactionCreateDate>
</BillingTransactionInfo>';
SELECT TOP (50000)
  id = IDENTITY(INT,1,1),
  X  = CAST(REPLACE(CAST(@xml AS VARCHAR(8000)),
  '<TransactionAmount>2570.77</TransactionAmount>',
  CONCAT('<TransactionAmount>',ABS(CHECKSUM(NEWID())%2250000)*.01,'</TransactionAmount>')) AS XML)
INTO    #things
FROM   sys.all_columns a, sys.all_columns b;

Solution:

SELECT id, tr.amt
FROM #things AS t
CROSS APPLY (VALUES(t.X.value(
  '(/*:BillingTransactionInfo/*:TransactionAmount/text())[1]','DECIMAL(10,2)'))) AS tr(amt)
WHERE tr.amt > 15000;

Returns:

id          amt
----------- ------------
201         21876.97
202         21229.64
204         19188.62
209         21680.17
212         18603.47
213         20507.21
216         19536.31
218         19490.95
...

Updated to demonstrate how to modify the values to 10000 as per the OP's request.

UPDATE t
SET X.modify('
    replace value of (/*:BillingTransactionInfo/*:TransactionAmount/text())[1]
    with "10000"')
FROM #things AS t
CROSS APPLY (VALUES(t.X.value(
  '(/*:BillingTransactionInfo/*:TransactionAmount/text())[1]','DECIMAL(10,2)'))) AS tr(amt)
WHERE tr.amt > 15000;

Thank you so much. I was able to select amounts greater than 15000. Please how could I update them?
@DanKalio I updated my answer to show how to modify the value. Let me know if you have other questions

HaukurHaf · Accepted Answer · 2019-01-17 22:54:24Z

To find the rows which have TransactionAmount of more than 15000, something like this should work:

SELECT * 
FROM TableName 
WHERE ColumnWithXml.value('(/BillingTransactionInfo/TransactionAmount)[1]','int') > 15000

Where TableName is the name of the table and ColumnWithXml is the name of the XML column (needs to be of type XML)

To update those rows, something like this should work:

UPDATE TableName
SET ColumnWithXml.modify('replace value of 
(/BillingTransactionInfo/TransactionAmount)[1] with ("25000")')

WHERE ColumnWithXml.value('(/BillingTransactionInfo/TransactionAmount)[1]','int') > 15000

See here (Where clauses on XML columns): https://www.sqlservercentral.com/Forums/Topic1545273-392-1.aspx

and here (updating data): https://www.mssqltips.com/sqlservertip/2738/examples-of-using-xquery-to-update-xml-data-in-sql-server/

Collectives™ on Stack Overflow

T-SQL to modify XML Data

3 Answers 3

Comments

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related