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
