SQL : CSV To Table using xml

Note: Thank you for visiting my blog. However, this blog is not maintained actively anymore. This post is now also in my new blog. Feel free to leave a comment there.

In this article I'm going to discuss about how to convert a comma separated string to a table to enable it for set based operation. 
Lets say we've a table which has the definition like this :

Create Table Employee 
(     
    EmpId Int,     
    EmployeeName varchar(100) 
)

Problem Statement
You need to retrieve  Employee Name based on EmpId provided. Now it is easier to retrieve the data when you have the EmpIds separately and the same can be used as below :
Select * from Employee where EmpId in (10,21,32,43,54,74,78,47,56,12,68)
The above query treats every EmpId separately. Now what if you get all the EmpIds in a single comma separated string like : '10,21,32,43,54,74,78,47,56,12,68,'. In that case you can not write query as below:
Select * from Employee where EmpId in ('10,21,32,43,54,74,78,47,56,12,68,')
The above query will not treat the EmpIds separately, but as a single csv string as a result this query will produce an erroneous result or may not parsed at all.


Solution
Now to process the csv effectively(so that it can be used for set based operation) we need to convert this csv to a table(set). You need to create the below procedure for the same.
CREATE proc csvToTable_xml
(
    @csv nvarchar(4000),
    @Delimiter NCHAR(1) = N',' 
)
AS
BEGIN
    declare @xml xml,@hDoc int

    select @xml=N'<Table>'+N'<row><col>'+Replace(@csv,@Delimiter,'</col></row><row><col>')+N'</col></row>'+N'</Table>'

    Exec sp_xml_preparedocument @hDoc OUTPUT, @xml    
     
    Select LTRIM(RTRIM(col)) From
    OPENXML(@hDoc, '/Table/row',2) WITH  ( col varchar(100))
    Where Isnull(LTRIM(RTRIM(col)),'')<>''
END
 

Usage:
 
Declare @tab Table
(
    SerialNo INT IDENTITY(1,1), 
    VAL  VARCHAR(100) 
)
insert into @tab
Exec csvToTable_xml @csv=N'10,21,32,43,54,74,78,47,56,12,68,',@Delimiter=N','
select * from  @tab
 
OUTPUT
SerialNoVAL
110
221
332
443
554
674
778
847
956
1012
1168

The above table variable can also be used as below :

select b.* 
 
from  @tab a inner join Employee b on a.VAL=b.EmpID
 
Explanation
Our Stored procedure csvToTable_xml accepts two parameters : @csv & @Delimiter. @csv contains the delimited string. @Delimiter contains the delimiter, yes you've guessed right, you can use not only comma but any delimiter as you like. Now the idea is to convert this csv to xml something like this : 
 
<Table>   
    <row>     <col>10</col>   </row>   
    <row>     <col>21</col>   </row>   
    <row>     <col>32</col>   </row>   
    <row>     <col>43</col>   </row>   
    <row>     <col>54</col>   </row>   
    <row>     <col>74</col>   </row>   
    <row>     <col>78</col>   </row>   
    <row>     <col>47</col>   </row>   
    <row>     <col>56</col>   </row>   
    <row>     <col>12</col>   </row>   
    <row>     <col>68</col>   </row>   
    <row>     <col />   </row> 
</Table>
Now from this xml we can easily produce a record set using standard xml functions provided by MSSQL(which we're not going to discuss in this article as it is out of scope).

0 comments: (+add yours?)

Post a Comment