Miscellaneous Ramblings on Hacking

July 15, 2009

Using XML datatype to parse comma-delimited columns

Filed under: Uncategorized — Zack Bethem @ 5:20 am

This was the coolest trick I’ve seen in a while.
Problem: need to parse a comma delimited file

select email
, Split.a.value('.','Varchar(50)') as kid_age
, row_number() over (partition by email order by email) as row_cnt
from (
select email
, 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


Leave a Comment »

No comments yet.

RSS feed for comments on this post. TrackBack URI

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s

Blog at WordPress.com.

%d bloggers like this: