Doing Things with Strings: SQL, Xml, and String Manipulation, Part II

This series of posts is about using SQL Server’s Xml features to do string manipulations.  Part I talked about creating comma-separated (CSV) lists from SQL database data.  Part II, this post, will talk about parsing a comma-separated list.

Until Xml-typed parameters came along in SQL Server 2005, the only easy way to pass a set of information into a stored procedure was by passing a string parameter that represented serialized data.  The two most common forms were an Xml fragment, and a CSV string.  Parsing these CSV strings usually required a custom table-valued function that used T-SQL string manipulation operations to parse out the information and build the return table. 

This is a perfectly good solution, but there’s a faster one.  I’ll give you the code for it, right up front:

CREATE FUNCTION ParseCsv
(
    @Csv varchar(max)
)
RETURNS TABLE
AS
    RETURN 
    (
        -- We're being passed in a CSV string; we'll replace the commas with
        -- endtag/start tag pairs.
        WITH A
        AS  (
            SELECT  REPLACE(@Csv, ',', '</dummy><dummy>') as XmlFrag1
        )
        ,   B
        AS  (
            -- We're building an XML fragment out of the CSV, in fact.
            SELECT  '<dummy>' + XmlFrag1 + '</dummy>' as XmlFrag2
            FROM    A
        )
        ,   C
        AS  (
            -- Convert it actually to type XML
            SELECT  CONVERT(xml, XmlFrag2) as XmlFrag3
            FROM    B
        )
            -- For more on XmlFragment, see below.  Right now, we want the
            -- content of the fragment, which we're getting with the value()
            -- function, and converting it to "int". 
        SELECT  D.XmlFragment.value('.', 'int') as Value
        FROM    C
            -- When you want to access the Xml field in every record, and 
            -- apply the "nodes" function to it, you need to remember that
            -- nodes() is a *function*, and thus can be used with CROSS APPLY
            -- to run the function for every record in the record source (C).
            -- Hence, we run nodes() on C.XmlFrag3 for every record, and call
            -- the resulting set of data D, with the results of "nodes" being
            -- the field XmlFragment.  
            CROSS APPLY C.XmlFrag3.nodes('/dummy') as D(XmlFragment)
     )
     
  

You’ll want to test it, so here’s code (using the AdventureWorksLT database) that generates a CSV string. 

DECLARE @csv varchar(max)
WITH A
as (
    SELECT (
        SELECT  ProductID 
        FROM    SalesLT.Product
        FOR XML AUTO, ELEMENTS, TYPE
    ) as ProductXml
   )
   
,   B
AS  (
    SELECT A.ProductXml.query('data(*)') AS ProductSeries 
    FROM A
)
SELECT  @csv = REPLACE(CONVERT(varchar(max), ProductSeries),' ',',')
        FROM B
SELECT @csv
select * from ParseCsv(@csv)

And here’s the result you get from the test.  The first recordset is just the select on @Csv, the second one is the result of the function.

 

In tests, this consistently ran in half the time of a T-SQL string manipulation solution, regardless of the number of values in the CSV.

Doing Things With Strings: SQL, Xml, and String Manipulation, Part I

SQL Server 2005 introduced new features for handling and manipulating Xml. Generating Xml via T-SQL is much simpler than it used to be. Parsing Xml is much, much easier, and we have a an Xml data type to use, too. All of that is pretty well documented in the Books Online. So if you need to produce or consume Xml, that's where to look.

But what these posts will be about is using Xml and the Xml features to get some non-Xml things done. Specifically, I'm going to talk about some types of string manipulation that the Xml features make faster and easier.

Generating Comma-Separated Lists (CSV Strings)

Sometimes, when you're summarizing data, you want the values in a column to be aggregated into a comma-separated list. An example of this might be a list of your biggest customers, with the trade associations or buying groups they belong to listed off to one side.

As you know, there is no built-in aggregate to do this in SQL Server. Microsoft (and, I believe, others) have published a SQL CLR aggregate function that will do this for you.

But, thanks to the Xml features, this can be also be done with T-SQL code, without publishing assemblies or writing custom functions. I’m going to demonstrate it with a series of CTEs, so that each step in the transformation is separate, but you can combine all of them into one expression if you like.

Let’s suppose that we are querying the AdventureWorks2008 database, and we want a list of stores with a list of contacts for each store in the same row, in a comma-separated list.

We start by querying the Sales.Store table, and include a subquery of the Person.Contact table, using the Sales.StoreContact table to link between stores and contacts. Our subquery is going to return the names of the contacts as an Xml fragment.

select  CustomerId
    ,   Name
    ,   (
        Select  ContactName  = FirstName + ' ' + ISNULL(MiddleName + ' ','') + LastName
        from    Person.Contact pc
                    inner join
                Sales.StoreContact ssc
                    on    pc.ContactID = ssc.ContactID
        where   ssc.CustomerID = ss.CustomerID
        for     xml auto, elements, type
        ) as ContactList1
from    Sales.Store ss

This looks like this:





Note that we’re only returning one element in our fragments: the Contact Name element. This is the only text that the fragment contains.


Our next step transforms the ContactList1 Xml field into a space-delimited list of names. Because our data has spaces, we’re going to alter our subquery a little to replace the spaces in the data with a character unlikely to appear in a name. You’ll see why in a minute.

With A
as  (        
    Select    ContactName  = FirstName + ' ' + ISNULL(MiddleName + ' ','') + LastName
         ,    ssc.CustomerID   
    from      Person.Contact pc            
                  inner join                  
              Sales.StoreContact ssc              
                  on   pc.ContactID = ssc.ContactID    
 ),  B
 as  (    
     select  CustomerId        
         ,   Name
         ,   (                
                 select  ContactName = REPLACE(A.ContactName, ' ','|')
                 from    A
                 where   CustomerID = ss.CustomerID 
                 for     xml auto, elements, type 
             ) as ContactList1    
     from   Sales.Store ss
)
select  B.*
   ,    ContactList2 = B.ContactList1.query('data(*)')
from    B

This produces results like this:





To convert this to a comma-separated list, we just need to do a conversion and some REPLACE statements:

With A
as  (
        Select    ContactName  = FirstName + ' ' + ISNULL(MiddleName + ' ','') + LastName
             ,    ssc.CustomerID
        from      Person.Contact pc
                        inner join
                  Sales.StoreContact ssc
                        on   pc.ContactID = ssc.ContactID
    )
,   B
as  (
    select  CustomerId
        ,   Name
        ,   (
                select  ContactName = REPLACE(A.ContactName, ' ','|') 
                from    A
                where   CustomerID = ss.CustomerID
                for     xml auto, elements, type
            ) as ContactList1
    from   Sales.Store ss
)
,   C
as  (
select  B.*
   ,    ContactList2 = B.ContactList1.query('data(*)')
from    B
)
select  C.CustomerID
    ,   C.Name
    ,   ContactList = REPLACE(
                        REPLACE(
                            CONVERT(varchar(max), C.ContactList2)
                        ,   ' '  -- convert spaces ...
                        ,   ', ' -- to commas (followed by spaces, if youlike)
                        )
                      , '|'  -- replace the pipe char ...
                      , ' '  -- with the original spaces!
                      )
from    C

… and the result looks like this:


Voilà: inline comma-separated lists!