Splitting and combining strings with TSQL

My current work includes a batch process that validates 1000's (and sometimes more) transactions against a dozen+ validation rules. Recently this process became the source of blocking issues, causing a lot of users to become very unhappy.

Previously the rules were written in a very straight-forward manner. Usually something similar to:

UPDATE transactions
SET 
reason = reason + 'A'
, status=6 -- exception
WHERE {some rule}

It's probably obvious where the blocking became a problem.

Our alternate approach was to collect the a list of invalid transactions and the rule that was violated into a table and mark all of the affected transactions with a single UPDATE.

CREATE TABLE #badRows (id BIGINT, rule char(1));

INSERT INTO #badRows (id, rule)
SELECT id, 'A'
FROM transactions
WHERE {some rule}

The next challenge was pivoting and rule-violation codes into a single string so that the reason field of the transaction could be updated, with the additional constraint that any existing rule-violation code had to be maintained. We came up with the following:

;WITH both AS (
-- combine existing violations with any new ones
SELECT t.id, t.reason 
FROM transactions t
INNER JOIN #badRows br ON br.id = t.id
UNION ALL
SELECT id, rule FROM #badRows
)
, ix AS
( 
-- split any strings into single characters
SELECT id, SUBSTRING(reason,Number,1) AS code
FROM both  
INNER JOIN master.dbo.spt_values ON Number BETWEEN 1 AND LEN(reason) AND type='P'
)
, crushedReasonCodes AS (
-- combine multiple rows into a single string
SELECT DISTINCT id, (
SELECT DISTINCT code AS [text()]
FROM ix
WHERE ix.id = o.id
FOR XML PATH ('')) as reason
FROM ix AS o
)
UPDATE transactions
SET reason = crc.reason
, status = 6
FROM transactions t
INNER JOIN crushedReasonCodes crc ON crc.id = t.id