Ward Pond's SQL Server blog

Ruminating on issues pertinent to the design and development of sound databases and processes under Microsoft SQL Server 2008, SQL Server 2005, and SQL Server 2000 (while reserving the right to vent about anything else that's on my mind)

Database Programming: The String Concatenation XML Trick, Sans Entitization

Database Programming: The String Concatenation XML Trick, Sans Entitization

  • Comments 6
  • Likes

When last we checked in on The Technique That Lance Found, Adam had noted that the method entitizes XML special characters, a state of affairs which limits its utility somewhat.  I tried to leverage Tony Rogerson's technique, which Adam passed along in his comment, but it was late and I was tired, so that didn't go so well.  I provided a relatively lame proof-of-concept using nested REPLACEs and called it an evening.

Well, it's amazing what a couple of good nights' sleep will do (not to mention clearing several important deliverables from my plate).  I present herewith the Rogerson Permutation of The Technique That Lance Found:

-- PERFORM THE TRICK WITH THE ROGERSON MODIFICATION
-- PIVOT Parent VALUES INTO 1 COLUMN FOR 1 BASE ROW 

SELECT LEFT(Parent_CSV,LEN(Parent_CSV)-1) FROM (
    SELECT (SELECT mydata FROM (
            SELECT x AS [data()] FROM (
                SELECT ParentString + N', ' FROM (
                    SELECT  TOP 100 PERCENT Child.ParentId, Parent.ParentString
                    FROM    Child 
                    JOIN    Parent 
                    ON      Child.ParentId = Parent.ParentId  
                    WHERE   Child.ChildId = 2  -- MUST SPECIFY 1 BASE ROW.  COULD BE A CORRELATED SUBQUERY 
                    ORDER BY Child.ParentId
                ) a
            ) AS y (x) FOR XML PATH(''), TYPE
        ) AS d ( mydata ) FOR XML RAW, TYPE ).value( '/row[1]/mydata[1]', 'varchar(max)' )
      AS Parent_CSV
) a

The results here address Adam's concern:

Parent_CSV
<Parent 1 String>,<Parent 2 String>,<Parent 3 String>

I'm not wild about the TOP 100 PERCENT/ORDER BY combination, as that's something of a dirty trick.  When I have a little more time I'll look at using a CTE to get the ordered result.

Thanks to Adam for the link to Tony's work, and to Tony for having it there in the first place.

More to come..

-wp

Comments
  • I love this :) Fun stuff! Here's a CTE version using AdventureWorks - 11ms was my execution time for the CTE - superfast -

    -- List of all Production.ProductSubcategories that a contact has ordered:

    -- Choose contact that has ordered the most items

    DECLARE @ContactId INT

    SELECT @ContactId = ContactId

    FROM Person.Contact

    WHERE ContactId = (SELECT TOP(1) ContactId FROM Sales.SalesOrderHeader GROUP BY ContactId ORDER BY COUNT(*) DESC);

    WITH ColumnToPivot ([data()]) AS (

    SELECT TOP 100 PERCENT ps.Name + N', ' AS ColumnYouWantToMakeACSVList_PlusAComma

    FROM Production.ProductSubcategory ps

    WHERE EXISTS (

    SELECT *

    FROM Production.Product p JOIN Sales.SalesOrderDetail od

    ON od.ProductId = p.ProductId

    JOIN Sales.SalesOrderHeader o

    ON o.SalesOrderId = od.SalesOrderId

    WHERE o.ContactId = @ContactId

    AND p.ProductSubcategoryID = ps.ProductSubcategoryID

    )

    ORDER BY ColumnYouWantToMakeACSVList_PlusAComma -- better to just order by ps.Name but, for example purposes, I include this

    FOR XML PATH(''), TYPE

    )

    , XmlRawData (CSVString) AS (

    SELECT (SELECT [data()] AS mydata FROM ColumnToPivot AS d FOR XML RAW, TYPE).value( '/row[1]/mydata[1]', 'NVARCHAR(max)') AS CSV_Column

    )

    SELECT LEFT(CSVString, LEN(CSVString)-1) AS CSVList

    FROM XmlRawData

    I hope the formatting looks okay... I moved the FOR XML clause from a nested derived table to the main CTE - I couldn't find fault with it but perhaps I missed something?

    Thanks for sharing, guys :)

    Scott Whigham

    http://www.learnsqlserver.com/

  • It's an especially Good Friday when we can close the loop on a technical conversation, and I believe

  • UPDATED 20 Dec 2008 to fix links It’s that time of year again, when I disappear from the blogosphere

  • I’ve got to pay more punctual attention to my comment pool.. RBarryYoung’s movingsql.com will be on my

  • It's been quite a while since the LIKE vs ? Puzzle , and I feel like it's time for another one. Response

Your comment has been posted.   Close
Thank you, your comment requires moderation so it may take a while to appear.   Close
Leave a Comment