Ward Pond's SQL Server blog

Ruminating on issues pertinent to the design and development of sound databases and processes under Microsoft SQL Server 2008, SQL Server 2005, and SQL Server 2000 (while reserving the right to vent about anything else that's on my mind)

Database Programming: The String Concatenation XML Trick Revisited (Or, Adam Is Right, But We Can Fix It)

Database Programming: The String Concatenation XML Trick Revisited (Or, Adam Is Right, But We Can Fix It)

  • Comments 8
  • Likes

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
&lt;Parent 1 String&gt;,&lt;Parent 2 String&gt;,&lt;Parent 3 String&gt;

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
  • 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 (

    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.

  • 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

Your comment has been posted.   Close
Thank you, your comment requires moderation so it may take a while to appear.   Close
Leave a Comment