Database Programming: The String Concatenation XML Trick, Sans Entitization


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 (5)

  1. Anonymous says:

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

  2. Anonymous says:

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

  3. Anonymous says:

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

  4. Anonymous says:

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

  5. Scott Whigham says:

    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/