This was the coolest trick I’ve seen in a while.
Problem: need to parse a comma delimited file
, Split.a.value('.','Varchar(50)') as kid_age
, row_number() over (partition by email order by email) as row_cnt
, cast('' + REPLACE(kid_age,',','') + '' as xml) as kid_age
from stg_profile_data ) x
cross apply kid_age.nodes('/M') split(a)
Mucho thanks to where Google found it