Need help parsing out a column that contains a string

[Dom71555]

Hello everyone,
I am hoping you can help with this. Below is a table in our database called PaymentTransaction. One of the columns is a varchar(2000) that contains a string that contains data from a payment transaction from an outside source. This table records a parents payment to their children's account for school lunch. Each payment is shown in one row in the table. The field 'amount' is the total dollars that the parent submitted. How that total gets divided up to their children is shown in the column userDataXML. "PaymenttransactionState persons=" shows the internal personid of the child they put payment to (The personid is a key field to other tables in the database). "foodamounts=" shows the amount of dollars put toward each child. In some cases their may be one, two, three or more children that they are making payments toward.

Here is what the tabe and some of the data looks like:

PaymentTransaction table

paymentTransactionID int
transactionRef int
paymentID int
paymentTimestamp datetime
requestID varchar(100)
paymentUserAccountID int
paymentMethodRef_dep2011M int
amount decimal(7, 2)
convenienceFee decimal(6, 2)
transactionFee decimal(6, 2)
paymentTransactionStateID int
modifiedTimestamp datetime
modifiedBy int
userDataXML varchar(2000)
comments varchar(500)
paymentMethodReferenceID int
originalTransactionID int


Below is data extracted from the PaymentTransaction table

1 111111111 1 2015-08-12 12:04:50.637 3233333333333 1 NULL 5.00 2.75 0.62 2 NULL 20791 <PaymentTransactionState persons="163" accounts="1116" assignments="" feeamounts="" foodamounts="5.00" convenienceFee="2.75" > NULL 1 NULL
6 222222222 6 2015-08-20 19:26:32.873 1234567890000 6 NULL 40.00 2.75 1.85 2 NULL 22455 <PaymentTransactionState persons="6141,2116" accounts="2037,2036" assignments="" feeamounts="" foodamounts="20.00,20.00" convenienceFee="2.75"> NULL 5 NULL
9 222222222 6 2015-08-20 19:26:32.873 1234567890000 6 NULL 60.00 2.75 1.85 2 NULL 22455 <PaymentTransactionState persons="1234,56787,1357" accounts="1111,2036,1245" assignments="" feeamounts="" foodamounts="25.00,15.00,20.00" convenienceFee="2.75"> NULL 5 NULL



What I need to do is to get the above data in a table (NewTable) in a format that I can join to other tables to create a report.


NewTable

paymenttransactionid int,
paymentdate datetime,
personid int,
Amount decimal(7,2))

Data would look like this

1 2015-08-12 12:04 163 5.00
6 2015-08-20 19:26 6141 20.00
6 2015-08-20 19:26 2116 20.00
9 2015-08-20 19:26 1234 25.00
9 2015-08-20 19:26 56787 15.00
9 2015-08-20 19:26 1357 20.00

I do not know how to break out the data in the column userdataXML to accomplish this.
I appreciate any help you can give me on this
Thank you

 

[johnherman]

My general solution. Write the 2000 byte column to a separate table. Add a integer column (call it, say, OCCURS_COLUMN) to that table to contain the number of detail records embedded in each 2000 byte record. (in your example, 1, 2, 3, respectively). Use SQL to determine that number and update that column for each of the 2000 byte records. Then write a Transact-SQL cursor to walk through that new table and parse the record, writing the results to your new detail table. The cursor would need to loop through the 2000 bytes a number of times equal to the OCCURS_COLUMN as well as walking through the entire work table.

==================================
adaptive uber info galaxies (bigger, better, faster, and more adept than agile innovative cognitive big data clouds)

 

[Dom71555]

Thanks JohnHerman,
While I know just enough SQL to get me in trouble, I took some of your advice and was able to create a new table with the data below.
paymenttransactionid amount paymenttimestamp personid foodamount

9 80 8/21/2015 7,321,929 40.00,40.00
10 100 8/21/2015 60,123,267 50.00,50.00
11 600 8/21/2015 26,771,725 300.00,300.00
12 300 8/21/2015 26,771,725 150.00,150.00


Now I need to figure out how to split the personid and foodamount columns to get my desired results.

 

[johnherman]

ALTER TABLE tablename ADD OCCURS_COLUMN, Integer
(I think that't the correct T-SQL syntax - might not be correct however).
ALTER TABLE tablename ADD FOOD_AMT_WORK varchar(2000);
(we need a work area)

UPDATE tablename SET OCCURS_COLUMN = 1
(You will always have at least one person ID and foodamount)

UPDATE tablename SET OCCURS_COLUMN = OCCURS_COLUMN + 1 WHERE INSTRING (FOOD_AMT_WORK, ',') > 0;
UPDATE tablename SET FOOD_AMT_WORK = SUBSTRING( FOOD_AMT_WORK, 1, INSTRING (FOOD_AMT_WORK, ',') - 1) + '|' + SUBSTRING( FOOD_AMT_WORK, INSTRING (FOOD_AMT_WORK, ',') + 1, 2000);
COMMIT;
(this will count up the payments, you will need to run the above 3 lines until zero rows are updated)

Are you getting the idea?


==================================
adaptive uber info galaxies (bigger, better, faster, and more adept than agile innovative cognitive big data clouds)

 

[Dom71555]

Yes, thank you. I have been using CHARINDX and SUBSTRING. The proble here is getting the values in a table once they are split

I also am using a variable to determine how many personid are in the string.

Declare @delimetercount int
set @delimetercount = (select LEN(personid)- LEN(REPLACE(personid,',','')) from #PaymentTransactionTable

This will be a loop to determine how many personid's I have to get. If @delimetercount = 2 then I know there are 3 personids to loop through.

I am going to try your suggestion using the table.

 

[johnherman]

Another option is to first collect up all the delimiter positions for personID and FoodAmtWork. You can store these in a separate table if you want. Then run the INSERT into the permanent table.

Write records to a table with:
paymenttransactionid int,
paymentdate datetime,
personid_start_position int,
personid_end_position int,
Amount_start_position int,
Amount_end_position int

Check those result records.
Then the INSERT is easy, right?

==================================
adaptive uber info galaxies (bigger, better, faster, and more adept than agile innovative cognitive big data clouds)

 

[Dom71555]

Thank you again,
Yes I can get it to work now. Thank you again for your help, I appreciate it