Database Programming: The String Concatenation XML Trick Revisited (Or, Adam Is Right, But We Can Fix It)
A find shared by one friend leads to correspondence from another..
The redoubtable Adam Machanic left a comment on The Technique That Lance Found which points out that special XML characters in a string will get entitized.
As usual, Adam is correct. If we make a subtle change to the contents of the Parent table in the original script:
INSERT Parent VALUES (1, '<Parent 1 String>')
INSERT Parent VALUES (2, '<Parent 2 String>')
INSERT Parent VALUES (3, '<Parent 3 String>')
.. the results clearly reflect Adam's point:
| Parent_CSV |
| <Parent 1 String>,<Parent 2 String>,<Parent 3 String> |
As an aside, another potential flaw in this code would be the inclusion of the low-order ASCII characters (below ASCII 32, with three exceptions) in the input string for the XML. These would spawn objections from the SQL Server XML parser in the form of a runtime error. Back when the blog was relatively new, here I blogged a method for stripping these characters from inbound character and text data.
Adam also gave us Tony Rogerson's fix, which I attempted to apply to Lance's artifact. I have thus far fallen short of success, but I'll keep plugging. In the meantime I can offer the slightly cold comfort of nested REPLACEs, shown here for the two characters I've introduced above (if this is the best we can do, we'd have to expand the nested REPLACEs to cover all of the entitizable characters in XML).
Here's the current state of affairs:
-- PERFORM THE REVISED TRICK
-- PIVOT Parent VALUES INTO 1 COLUMN FOR 1 BASE ROW
-- AND REPLACE TWO OF THE CHARACTERS ENTITIZED BY XML
SELECT REPLACE(REPLACE(STUFF(( SELECT [text()]= ',' + ISNULL(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
FOR XML PATH('')), 1,1, ''), N'<', N'<'), N'>', N'>')
.. and, not to beat a dead horse, but here's the result:
| Parent_CSV |
| <Parent 1 String>,<Parent 2 String>,<Parent 3 String> |
More to come, in the form of either new code or capitulation.
Thanks to Adam Machanic for insisting, as he always does, on the highest standards of clarity and quality.
-wp
