Brtdku

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>

 

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;

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!

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/