Help an amateur out with a script/query

Question

Help an amateur out with a script/query

acid_burn013

Grasshopper

Points: 13
More actions
January 12, 2017 at 2:07 pm

#325299

I have a somewhat rudimentary understanding of SQL, I've yet to encounter a situation where I needed to edit/work with xml files. I have a single value inside of an xml file that I need to change. Here is the file:
<PrepaidTicket xmlns:i="http://www.w3.org/2001/XMLSchema-instance">
<PrepaidTicketItems>
<PrepaidTicketItem>
  <CouponAmount>10.0000</CouponAmount>
  <CouponCode>BOGOHO GOOD GOLLY</CouponCode>
  <CouponSymbol>$</CouponSymbol>
  <PrepaidTicketItemID>1</PrepaidTicketItemID>
  <SystemItemTypeCode>COUPON</SystemItemTypeCode>
</PrepaidTicketItem>
</PrepaidTicketItems>
</PrepaidTicket>
I simply need to change the coupon amount of 10.0000 into 12.0000. Can anyone help me with a script that will do this?
Forum Etiquette: How to post Reporting Services problems

[/url]

Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
[/url]

How to Post Performance Problems - by Gail Shaw
[/url]

mister.magoo SSC-Forever Points: 47174 More actions · Answer 1

acid_burn013 - Thursday, January 12, 2017 2:07 PM
I have a somewhat rudimentary understanding of SQL, I've yet to encounter a situation where I needed to edit/work with xml files. I have a single value inside of an xml file that I need to change. Here is the file:
<PrepaidTicket xmlns:i="http://www.w3.org/2001/XMLSchema-instance">
<PrepaidTicketItems>
<PrepaidTicketItem>
  <CouponAmount>10.0000</CouponAmount>
  <CouponCode>BOGOHO GOOD GOLLY</CouponCode>
  <CouponSymbol>$</CouponSymbol>
  <PrepaidTicketItemID>1</PrepaidTicketItemID>
  <SystemItemTypeCode>COUPON</SystemItemTypeCode>
</PrepaidTicketItem>
</PrepaidTicketItems>
</PrepaidTicket>
I simply need to change the coupon amount of 10.0000 into 12.0000. Can anyone help me with a script that will do this?

That raises some questions....
1. Do you want to do this to lots of PrepaidTickets?
2. Do you want to do this only where CouponAmount = 10.00?
3. Is this a fixed swap : 10.00 for 12.00 or a percentage/value increase based on the original value?
4. Where is this data held? (In the database? In a file? Elsewhere? - I know you said file, but if it's in a file, why ask this on a database forum?)
5. Do you need to update the original or just copy it somewhere with the new value?

MM

select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

acid_burn013 Grasshopper Points: 13 More actions · Answer 2

mister.magoo - Thursday, January 12, 2017 3:03 PM
acid_burn013 - Thursday, January 12, 2017 2:07 PM
I have a somewhat rudimentary understanding of SQL, I've yet to encounter a situation where I needed to edit/work with xml files. I have a single value inside of an xml file that I need to change. Here is the file:
<PrepaidTicket xmlns:i="http://www.w3.org/2001/XMLSchema-instance">
<PrepaidTicketItems>
<PrepaidTicketItem>
  <CouponAmount>10.0000</CouponAmount>
  <CouponCode>BOGOHO GOOD GOLLY</CouponCode>
  <CouponSymbol>$</CouponSymbol>
  <PrepaidTicketItemID>1</PrepaidTicketItemID>
  <SystemItemTypeCode>COUPON</SystemItemTypeCode>
</PrepaidTicketItem>
</PrepaidTicketItems>
</PrepaidTicket>
I simply need to change the coupon amount of 10.0000 into 12.0000. Can anyone help me with a script that will do this?
That raises some questions....
1. Do you want to do this to lots of PrepaidTickets?
2. Do you want to do this only where CouponAmount = 10.00?
3. Is this a fixed swap : 10.00 for 12.00 or a percentage/value increase based on the original value?
4. Where is this data held? (In the database? In a file? Elsewhere? - I know you said file, but if it's in a file, why ask this on a database forum?)
5. Do you need to update the original or just copy it somewhere with the new value?

Hi Mister magoo-

1. Only with one
2. Yes only where coupon amount = 10.00
3. fixed swap, 10 for 12.
4. Data is held in a database, if it was held in a file elsewhere then yes I would just find the file and edit it, eazy peezy.
5. I need to update the original.

Phil Parkin SSC Guru Points: 246960 More actions · Answer 3

acid_burn013 - Thursday, January 12, 2017 4:13 PM
mister.magoo - Thursday, January 12, 2017 3:03 PM
acid_burn013 - Thursday, January 12, 2017 2:07 PM
I have a somewhat rudimentary understanding of SQL, I've yet to encounter a situation where I needed to edit/work with xml files. I have a single value inside of an xml file that I need to change. Here is the file:
<PrepaidTicket xmlns:i="http://www.w3.org/2001/XMLSchema-instance">
<PrepaidTicketItems>
<PrepaidTicketItem>
  <CouponAmount>10.0000</CouponAmount>
  <CouponCode>BOGOHO GOOD GOLLY</CouponCode>
  <CouponSymbol>$</CouponSymbol>
  <PrepaidTicketItemID>1</PrepaidTicketItemID>
  <SystemItemTypeCode>COUPON</SystemItemTypeCode>
</PrepaidTicketItem>
</PrepaidTicketItems>
</PrepaidTicket>
I simply need to change the coupon amount of 10.0000 into 12.0000. Can anyone help me with a script that will do this?
That raises some questions....
1. Do you want to do this to lots of PrepaidTickets?
2. Do you want to do this only where CouponAmount = 10.00?
3. Is this a fixed swap : 10.00 for 12.00 or a percentage/value increase based on the original value?
4. Where is this data held? (In the database? In a file? Elsewhere? - I know you said file, but if it's in a file, why ask this on a database forum?)
5. Do you need to update the original or just copy it somewhere with the new value?
Hi Mister magoo-
1. Only with one
2. Yes only where coupon amount = 10.00
3. fixed swap, 10 for 12.
4. Data is held in a database, if it was held in a file elsewhere then yes I would just find the file and edit it, eazy peezy.
5. I need to update the original.

There's probably a fancy way of doing this, but it's also easy enough to cheat, as follows:

IF OBJECT_ID('tempdb..#Test', 'U') IS NOT NULL DROP TABLE #Test;

CREATE TABLE #Test (XMLCol XML);

INSERT #Test ( XMLCol ) VALUES ( '<PrepaidTicket xmlns:i="http://www.w3.org/2001/XMLSchema-instance"> <PrepaidTicketItems> <PrepaidTicketItem> <CouponAmount>10.0000</CouponAmount> <CouponCode>BOGOHO GOOD GOLLY</CouponCode> <CouponSymbol>$</CouponSymbol> <PrepaidTicketItemID>1</PrepaidTicketItemID> <SystemItemTypeCode>COUPON</SystemItemTypeCode> </PrepaidTicketItem> <PrepaidTicketItem> <CouponAmount>20.0000</CouponAmount> <CouponCode>BOGOHO GOOD GOLLY</CouponCode> <CouponSymbol>$</CouponSymbol> <PrepaidTicketItemID>1</PrepaidTicketItemID> <SystemItemTypeCode>COUPON</SystemItemTypeCode> </PrepaidTicketItem> <PrepaidTicketItem> <CouponAmount>10.0000</CouponAmount> <CouponCode>BOGOHO GOOD GOLLY</CouponCode> <CouponSymbol>$</CouponSymbol> <PrepaidTicketItemID>1</PrepaidTicketItemID> <SystemItemTypeCode>COUPON</SystemItemTypeCode> </PrepaidTicketItem> </PrepaidTicketItems> </PrepaidTicket>' );

SELECT * FROM #Test t;

UPDATE #Test SET XMLCol = CAST(REPLACE( CAST(XMLCol AS VARCHAR(8000)) , '<CouponAmount>10.0000</CouponAmount>' , '<CouponAmount>12.0000</CouponAmount>' ) AS XML);

SELECT * FROM #Test t;

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

Eirikur Eiriksson SSC Guru Points: 182911 More actions · Answer 4

This update operation is elementary using the XML modify operation, here is a quick example
😎

USE TEEST; GO SET NOCOUNT ON;

-- Sample XML DECLARE @TXML XML = '<PrepaidTicket xmlns:i="http://www.w3.org/2001/XMLSchema-instance"> <PrepaidTicketItems> <PrepaidTicketItem> <CouponAmount>10.0000</CouponAmount> <CouponCode>BOGOHO GOOD GOLLY</CouponCode> <CouponSymbol>$</CouponSymbol> <PrepaidTicketItemID>1</PrepaidTicketItemID> <SystemItemTypeCode>COUPON</SystemItemTypeCode> </PrepaidTicketItem> </PrepaidTicketItems> </PrepaidTicket>' ; -- The new value for CouponAmount DECLARE @CouponAmount DECIMAL(18,4) = 12.0;

-- XML modify statement SET @TXML.modify('replace value of (/PrepaidTicket/PrepaidTicketItems/PrepaidTicketItem/CouponAmount/text())[1] with sql:variable("@CouponAmount")');

-- Check the value, this query can also be used to filter the update set from a table! SELECT @TXML.value('(/PrepaidTicket/PrepaidTicketItems/PrepaidTicketItem/CouponAmount/text())[1]','DECIMAL(18,4)') AS CouponAmount;

Phil Parkin SSC Guru Points: 246960 More actions · Answer 5

Eirikur Eiriksson - Friday, January 13, 2017 4:08 AM
This update operation is elementary using the XML modify operation, here is a quick example
😎
USE TEEST; GO SET NOCOUNT ON;
-- Sample XML DECLARE @TXML XML = '<PrepaidTicket xmlns:i="http://www.w3.org/2001/XMLSchema-instance"> <PrepaidTicketItems> <PrepaidTicketItem> <CouponAmount>10.0000</CouponAmount> <CouponCode>BOGOHO GOOD GOLLY</CouponCode> <CouponSymbol>$</CouponSymbol> <PrepaidTicketItemID>1</PrepaidTicketItemID> <SystemItemTypeCode>COUPON</SystemItemTypeCode> </PrepaidTicketItem> </PrepaidTicketItems> </PrepaidTicket>' ; -- The new value for CouponAmount DECLARE @CouponAmount DECIMAL(18,4) = 12.0;
-- XML modify statement SET @TXML.modify('replace value of (/PrepaidTicket/PrepaidTicketItems/PrepaidTicketItem/CouponAmount/text())[1] with sql:variable("@CouponAmount")');
-- Check the value, this query can also be used to filter the update set from a table! SELECT @TXML.value('(/PrepaidTicket/PrepaidTicketItems/PrepaidTicketItem/CouponAmount/text())[1]','DECIMAL(18,4)') AS CouponAmount;

Hi Eirikur, does your solution update multiple occurrences of CouponAmount = 10? It's not totally clear tom me from the requirements whether this matters or not.

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin