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'&lt;', N'<'), N'&gt;', 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

Comments

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

• Anonymous
  January 01, 2003
  When last we checked in on The Technique That Lance Found , Adam had noted that the method entitizes

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

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

• Anonymous
  January 01, 2003
  The solution that I have been using is: --===== --Transformed FOR XML String Concatenation: select ( SELECT n + ',' FROM ( SELECT 'a<b' AS n UNION ALL SELECT 'b>a' UNION ALL SELECT 'b&a' UNION ALL SELECT 'b a') r FOR XML PATH(''), TYPE ).value('.[1]','varchar(max)') -- ===== Which seems to me to be both faster and more complete.

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

• Anonymous
  January 01, 2003
  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..  :-)

• Anonymous
  March 15, 2008
  and don't forget the ampersand (&) character :)