Ich möchte den Wert für TransactionAmount ändern, wobei TransactionAmount> 15000 mit TSQL. Die Tabelle enthält über 50.000 Zeilen.

Könnte mir bitte jemand helfen? Ich habe Probleme, irgendwo Beispiele zu finden.

<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>
 
0
Dan Kalio 18 Jän. 2019 im 01:26

3 Antworten

Beste Antwort

Willkommen bei Stack Overflow. Dies sollte ziemlich einfach sein. Lassen Sie uns zunächst 50.000 Zeilen mit Beispieldaten generieren.

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;

Lösung:

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;

Rückgabe:

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
...

Aktualisiert, um zu demonstrieren, wie die Werte gemäß der OP-Anforderung auf 10000 geändert werden können.

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;
0
Alan Burstein 18 Jän. 2019 im 15:41

Ihr XML hat einen Standard-Namespace . Es ist möglich, einen Platzhalter (wie *:ElementName) zu verwenden, es wird jedoch empfohlen, Namespaces zu deklarieren.

Darüber hinaus ist die native XML-Methode .exist() der beste Weg, um nach einem Wert zu filtern, wenn Sie diesen Wert nicht benötigen .

Versuche dies:

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;

Sie können dies als lesen

  • Verwenden Sie den angegebenen Standard-Namespace, wenn kein expliziter Namespace vorhanden ist
  • Aktualisieren Sie Ihre Tabelle und ändern Sie das XML auf die angegebene Weise
  • Treffen Sie jedoch nur Zeilen, in denen ein <BillingTransactionInfo> mit einem <TransactionAmount> höher als 15000 vorhanden ist.

Achtung : Ihr Beispiel sieht nicht so aus, aber Sie müssen sicher sein, dass Ihre XML-Spalte nur ein einziges <BillingTransactionInfo> als Statet in Ihrer Frage enthält!

1
Shnugo 18 Jän. 2019 im 08:07

Um die Zeilen mit einem TransactionAmount von mehr als 15000 zu finden, sollte Folgendes funktionieren:

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

Wobei TableName der Name der Tabelle und ColumnWithXml der Name der XML-Spalte ist (muss vom Typ XML sein)

Um diese Zeilen zu aktualisieren, sollte etwa Folgendes funktionieren:

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

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

Siehe hier (Where-Klauseln für XML-Spalten): https://www.sqlservercentral.com/Forums/Topic1545273-392-1. aspx

Und hier (Daten aktualisieren): https : //www.mssqltips.com/sqlservertip/2738/examples-of-using-xquery-to-update-xml-data-in-sql-server/

0
HaukurHaf 17 Jän. 2019 im 22:54