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:
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 SUBQUERYORDER BY Child.ParentID FOR XML PATH('')), 1,1, ''), N'<', N'<'), N'>', N'>')
.. and, not to beat a dead horse, but here's the result:
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.
and don't forget the ampersand (&) character :)
Just another nested REPLACE, Mladen.. we were just proving concept here. :-) If I can get Tony Rogerson's syntax working with this construction, we hopefully won't have to instantiate every entitizable character.
Stay tuned.. :-)
When last we checked in on The Technique That Lance Found , Adam had noted that the method entitizes
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
The solution that I have been using is:
--Transformed FOR XML String Concatenation:
SELECT n + ','
SELECT 'a<b' AS n
FOR XML PATH(''), TYPE
Which seems to me to be both faster and more complete.
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