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

Advertisements

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 )

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s

Create a free website or blog at WordPress.com.

%d bloggers like this: