Turning semicolon delimited data into a table

Here’s a handy conversion tool that I built for myself in order to convert semi-colon delimited data (very common in files, etc) into a table. It comes in the form of a scalar function, that converts a varchar(max) to an XML value.

The reason I’ve chosen XML as the return format is because this allows for a dynamic number of columns, even on a row-for-row basis. I’ve named the elements in the return xml so that they will construct an HTML table (TABLE, TR and TD), but you could, of course, call them anything you like with a simple modification of the code.

First off, the CREATE FUNCTION statement and some variable declarations that we’re going to need:

CREATE FUNCTION dbo.sdv2xml(@txt varchar(max))

    --- Declarations:
        @row_sep    varchar(10)=CHAR(13)+CHAR(10),
        @col_sep    varchar(10)=';',
        @xml        xml;

Note that you can change the @col_sep and @row_sep constants so you, in theory, could have the function work with any kind of delimited file. For example, you could set @col_sep to CHAR(9) in order to work with tab separated files instead.

Next up, I’m building a recursive common table expression that splits the text string into one record per line (using @row_sep).

    --- [rows] is a recursive common table expression that
    --- splits the @sdv variable into rows, where each row
    --- in the data set represents a row in @sdv.

    WITH [rows] ([row], row_string, remain)
    AS (
        --- Anchor: "Row 0" is the anchor row of the
        --- recursive CTE.
        SELECT 0 AS [row],
            CAST(NULL AS varchar(max)) AS row_string,
            @txt AS remain

        UNION ALL

        --- Recursion: Iterate through the "remain" column of
        --- the [rows] common table expression. Each recursion
        --- adds another row, and increments the [row] counter
        --- column by 1.
        SELECT [row]+1 AS [row],
                CHARINDEX(@row_sep, remain+@row_sep)-1)
                AS varchar(max)) AS row_string,
                CHARINDEX(@row_sep, remain+@row_sep)+
                LEN(remain)) AS varchar(max)) AS remain
        FROM [rows]
        WHERE NULLIF(remain, '') IS NOT NULL),

Now that we have a recordset, “rows”, with one record per row in the string, here’s another recursive common table expression that splits these rows into columns, using more or less the same method. The resulting recordset will contain one record for each row and column of the original string:

    --- cols is a recursive common table expression, based on
    --- the [rows] cte, that splits each row in [rows] into
    --- columns.

         cols ([row], col, [value], remain)
    AS (
        --- Anchor: "Column 0" of each row is the anchor of
        --- the recursive CTE.
        SELECT [row],
            0 AS col,
            CAST(NULL AS varchar(max)) AS [value],
            row_string AS remain
        FROM [rows]
        WHERE [row]>0    --- Excluding the blank anchor.

        UNION ALL

        --- Recursion: Iterate through each column of the
        --- "remain" cte variable.
        SELECT [row],
            col+1 AS col,
                CHARINDEX(@col_sep, remain+@col_sep)-1)
                    AS varchar(max)) AS [value],
                CHARINDEX(@col_sep, remain+@col_sep)+
                LEN(remain)) AS varchar(max)) AS remain
        FROM cols
        WHERE NULLIF(remain, '') IS NOT NULL)

And finally, here’s the SELECT query to piece it all together into an XML document. If you’re new to FOR XML PATH(), check out my other article on the subject.

    --- Using the [rows] and cols common table expressions,
    --- create a single XML variable.

    SELECT @xml=(
        --- A TABLE element surrounds the entire set:
        SELECT (

            --- Rows in a TR elements:
            SELECT (

                --- Columns in TD elements:
                SELECT [value] AS TD
                FROM cols
                WHERE cols.[row]=[rows].[row]
                ORDER BY [row]
                FOR XML PATH (''), TYPE
                --- End of TD

                ) AS [TR]
            FROM [rows]
            ORDER BY [row]
            FOR XML PATH (''), TYPE
            --- End of TR

            ) AS [TABLE]
        FOR XML PATH (''), TYPE
        --- End of TABLE

Finally, returning @xml value to the caller.

    --- Return the result

    RETURN @xml;


That’s it. Here’s a test query:

DECLARE @x xml;

SET @x=dbo.sdv2xml(


Comments, ideas, questions – leave them in the comment field below!

One thought on “Turning semicolon delimited data into a table

Let me hear your thoughts!

Fill in your details below or click an icon to log in:

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