Welcome to TechNet Blogs Sign in | Join | Help

Database Programming: Operator Precedence in DTS is a Matter of Inheritance

This post will (hopefully) close a thread which runs here and here.

When we last addressed this issue, I mentioned that an answer to the operator precedence question for DTS raised by Scott R. would depend on either my research or the kindness of others.

Chalk one up for the kindness of others.  Bob Duffy, newly minted MCA: Database, was kind enough to send along the following:

Hi Ward,

I didn’t think that DTS in SQL 2000 has or needed the concept of operator precedence as such. This is because it doesn’t have its own expression language like SSIS – it has ActiveX/VBScript  tasks and SQL tasks and they have their own rules on precedence depending on language used.

The VBScript operator precedence is here: http://msdn.microsoft.com/en-us/library/6s7zy3d1(VS.85).aspx

So, Scott, the operator precedence changes from DTS/SQL2K to SSIS/SQL2K5 depends on the tool you're using to code your expressions in DTS.

Thanks, Bob, for helping me understand this issue, and congratulations on your SQL Ranger certification!

-wp

Database Programming: Operator Precedence In SSIS

This morning's post on Operator Precedence was a little misleading, as Scott pointed out in his comment:

The example expression highlighted in green above (A <> 0 AND B / A > 1) is said to evaluate differently in SQL 2000 and SQL 2005, but I don't see any ungrouped use of the unary + or - that would affect their evaluation.

Did I miss something in this example?

Nope..  I missed something.  Greg's original message referenced the operator precendence list for SSIS, and I found the list for SQL Server.  Believe it or not, they're different.

Here's a link to the SSIS 2005 Operator Precedence list, and here's a pointer to a forum thread discussing a change in some of this documentation.  I'm still looking for the list for DTS in SQL Server 2000.

More as I learn it..  either via research or the kindness of others..

     -wp

Database Programming: Operator Precedence In SQL Server

[UPDATED 9 May 2008; the information presented doesn't exactly answer Greg's question.  There's an update here; and DTS is discussed here] 

I received an inquiry yesterday from Greg Husemeier, who I met when he came to Redmond for the SQL Ranger program.  Greg asked a great question:

I hope you don’t mind me running a quick programming question regarding order of operators as predicates in SQL 2005.  I noticed that you discuss similar topics in your blog.  I was looking at a SQL 2005 upgrade “lessons learned” PPT recently and it stated the following regarding the query processor:

In SS2005, we have slightly changed the order of operators as predicates so,

(A <> 0 AND B / A > 1) will be different in SS2K vs SS2005

Recommendation:  Use parenthesis to force the order you wish.  Parenthesis always get executed first.

Do you know if this is documented anywhere?  I am unable to find a description of the differences in behavior between the two versions other than in this presentation.

This question begat a research jag, which resulted in the finding that there has indeed been a subtle change in this functionality between SQL Server 2000 (scroll to the bottom of the page) and SQL Server 2005 (no scrolling required).  Here's the operator precedence list for each platform; note the change in the handling of positive and negative numbers:

SQL Server 2000:

+ (Positive), - (Negative), ~ (Bitwise NOT)
* (Multiply), / (Division), % (Modulo)
+ (Add), (+ Concatenate), - (Subtract), & (Bitwise AND)
=,  >,  <,  >=,  <=,  <>,  !=,  !>,  !< (Comparison operators)
^ (Bitwise Exclusive OR), | (Bitwise OR)
NOT
AND
ALL, ANY, BETWEEN, IN, LIKE, OR, SOME
= (Assignment)

SQL Server 2005:

~ (Bitwise NOT)
* (Multiply), / (Division), % (Modulo)
+ (Positive), - (Negative), + (Add), (+ Concatenate), - (Subtract), & (Bitwise AND)
=, >, <, >=, <=, <>, !=, !>, !< (Comparison operators)
^ (Bitwise Exclusive OR), | (Bitwise OR)
NOT
AND
ALL, ANY, BETWEEN, IN, LIKE, OR, SOME
= (Assignment)

Thanks for the question, Greg!

-wp

Two Pieces of Technical News from Kalen

Kalen Delaney has one of the most consistently informative SQL Server blogs in all of the Internets, and unless I miss my guess, it was also she who left the first of the very supportive comments I've received in the wake of my recent series of personal posts.  Thank you, Kalen, and everyone else for your kindness and support.

Kalen has two posts this week of special interest.  This one discusses the recent release of Cumulative Update #7 for SQL Server 2005 SP2.  As Kalen notes, MS advises not to apply this package unless you're experiencing one of the scenarios listed in this KB article.

Here, Kalen discusses the establishment of the Microsoft Jim Gray Systems Lab in Madison, Wisconsin.  In cooperation with David DeWitt, a database researcher and emeritus professor of computer sciences at UW-Madison, graduate research will be conducted at the center.  Very cool stuff, and not just because Jim Gray was such an inspirational figure..

Thanks again, Kalen!

-wp
 
UW-Madison '82

Non-Technical News: Reflections Upon My Recent Attempt To Debunk Thomas Wolfe

Frequent readers of this space may be aware that I returned to my hometown last week for the first time in twenty-six years to pay my respects to a dear family friend who passed away suddenly.

While I'm no George Webber, I've been away and out of touch for so long that I wasn't sure what I'd find.  Many important things, such as the lighthouse at Old Field Point, are exactly as I remember them:

Old Field Lighthouse, looking northeast Old Field Lighthouse, looking west

I stopped by the old neighborhood and knocked on every door where I recognized the name on the driveway.  This led me to two houses, and some wonderful time catching up with two wonderful families.  Both were as kind and vital as I remember them; Flax Pond, and our old house at 8 Flax Pond Woods, are also much the same:

Flax Pond, looking south  Flax Pond, looking north, Long Island Sound in the background

8 Flax Pond Woods 8 Flax Pond Woods

Long Island Sound and the Setauket Mill Pond were also timelessly gorgeous (notice also that I figured out how to set the date on my daughter's camera about this time):

Long Island Sound from Flax Pond Woods Association Beach  Setauket Mill Pond (Frank Melville Jr. Memorial Park)

Monument, Frank Melville Jr. Memorial Park Setauket Mill Pond with Setauket Neighborhood House in the distance

The last (lower right) picture in this set shows the Setauket Mill Pond with the Setauket Neighborhood House in the background.  It was there that we gathered on Thursday evening to remember our beloved, departed friend, Tun-Hsu "Martha" McCoy.  There were probably a couple of hundred people there, and the memories flowed thick and full for over three hours.  Not only did I relive a profoundly formative period of my adolescence through the eyes of many people who were there, but I also learned many new things about my friend -- such as that she founded an organization which has sponsored over 100 teachers in China, many years after she smuggled ten pounds of manuscripts out of wartime China.

She cooked much more than the spaghetti and apple pie I remember, and she touched hundreds if not thousands of people in all walks of life.  To hear her classmates, several of whom came all the way from China to attend, was wonderfully humbling.  Martha's impact truly spanned the planet.

A veritable Who's Who of Democratic politicians and precinct workers recalled her ferocious energy and insistence on inclusion in all matters; one friend recalled her remarking about her cat, "I will not tolerate abridgment of Michael's rights just because he has four legs!"

A number of my old cronies from the Smithhaven Democratic Youth Caucus were present, and spoke.  Elizabeth Lorris-Ritter, now channeling Martha's spirit as an activist in the Washington Heights section of Manhattan.  Phil Schiff, a lawyer who honors Martha's example by providing pro-bono representation to those who cannot afford his considerable expertise.  Peter Pierce, who's working in public access television, an incredibly democratizing force of which Martha would entirely approve.  Florence Boroson read a remembrance from her son, Marty, who was unable to attend; Marty's father, Louis, was also in attendance.  Margalit Fox shared a remembrance from Elizabeth Rogers in addition to her own moving remarks.  Eileen Sandberg came up with a sick child at the last moment and couldn't be there.

And your local database geek recapitulated the thoughts he'd shared online a week before.

It was like a high school reunion occasioned by the passing of a favorite teacher.  It was sad and wondrous to share these event with Barry, and to wind up the evening by performing the musical number he asked for.

Oh..  and Martha's brother Gordon lives five miles away from us.  Yes, we'll be getting together.

Margalit, her husband George, Elizabeth, and I moved very slowly towards the rented Dodge Magnum, and I shepherded this hardy crew back to Washington Heights.  I spent a restful night and morning with Margalit, George, their cats, and their view of the Hudson River:

The Hudson River, from George & Margalit's window

I hopped the A train and had lunch with another dear, albeit apolitical, friend from high school, Ron Iwersen.  We spent a couple of hours catching up before he had to get back to work and I had to get back uptown to the Magnum. 

I had only one more stop I wanted to make, so I headed up Sixth Avenue from Ron's office above Penn Station, turned onto Broadway at Times Square, and after another ten blocks or so, I found my destination:

Marquee, Ed Sullivan Theater 

One t-shirt later, it was on to Columbus Circle, the A train, an all-too-brief farewell to George and Margalit, and a narrow avoidance of the Pope's entourage as I got off of Manhattan Island on my way to JFK.

=-=-=-=-=-=-=-=-=-=-=

So, what about Thomas Wolfe?  Can you go home again?

Well, no way could I afford to live there these days..  but not many of us could.  There were lots of big, new houses in places that were better off without big, new houses.  The lasting memory I'll take is of the people I saw. 

If people like Barry McCoy and JoAnne Young and Margaret Sullivan and Rita Sakitt and Ken Wishnia and Simone Adams and Clifford and Barbara Swartz weren't still there, it would have been incredbly hollow; a two-dimensional, funhouse version of my childhood.  While rampant development and growth have rendered the community of my childhood a memory, the area's fundamental beauty remains, and wonderful people are still wonderful people, no matter where they live.

To see Barry and the old SDYC crowd.. and my Cub Scout den mother, whose name I've sinfully forgotten..  and Ron the next day.. felt like a return rather than a discovery.

If that's not home, it's close enough for me..  and a far sight better than it had any right to turn out after I'd been away for twenty-six years!

To everyone named in this post: thank you, dear friends, for your many kindnesses during an intense and unforgettable thirty-five hours.

Farewell, dear Martha.

-wp

Posted by Ward Pond | 0 Comments

Tech·Ed North America 2008 Developers Update/See You Next Week

The last couple of days have certainly had a particular orientation.  In addition to booking my arrangement for tomorrow night's trip to New York, today I booked my trip and accommodations in Orlando, and I received my speaking schedule last week.  Microsoft Tech·Ed North America 2008 Developers is starting to look pretty real to me.

On Tuesday, June 3rd, I'll be presenting my Set-Based Thinking for the OLTP Developer talk.  I should be hoarse by end-of-day Wednesday, as SQL Tricks, Data Modeling for OLTP, and Version-Stamping of Database Objects will all be offered on the same day.

I'm certain I'll also be spending a good deal of time on the convention floor, so if you're at Tech·Ed North America 2008 Developers, make sure to stop by and say hello.

Other than a commuting-related auto-post tomorrow, I'll be away from the blog until next week.

Play nice..

-wp

More About Martha McCoy

I've heard from a number of old friends -- and, I suspect, a few new ones -- in the wake of yesterday's remembrance of my inspirational family friend, Tun-Hsu "Martha" McCoy.  I had no doubt that her loss would be keenly felt across a wide swath of the community, and the comments and private correspondence I've received have certainly borne that out.

I've got two other pieces of news to share.

The first is that a memorial service for Martha will be held a week from tonight, Thursday, April 17, 2008 at 7:30pm at the Setauket Neighborhood House, 95 Main Street, in East Setauket, NY.

The second is that, through the understanding of my colleagues and family, the generous support of my father, and Margalit Fox's timely offer of shelter from the storm, I'll be in attendance.

I've not been back to Long Island in nearly 25 years.  It will be a great comfort to feel the embrace of our "extended family" during such a difficult time.

See you next week, dear friends..

-wp

Posted by Ward Pond | 2 Comments

R.I.P. Tun-Hsu "Martha" McCoy -- and Thank You!

Every once in awhile, you get a piece of news that sets you looking at where you are and where you've been.  Today is one of those days.

There will be no technical content in this post, and it might ramble some.  Bear with me; it's been a contemplative night..

=-=-=-=-=-=-=-=

When I was a young man growing up on New York's Long Island, I was singularly blessed.  Like most singularly blessed young men, I was also singularly unaware of my divine state.  The son of a university executive vice president and a devoted community activist, I was accustomed from a very early age to seeing my parents out and about in the community, moving and shaking, always with the goal of helping people.

It was a confusing and exciting time.  Dad and his colleagues were literally building a world class university out of the mud.  Before he became Executive Vice President at the State University of New York at Stony Brook, he was Chairman of its Department of Physics.  All order of brilliant people passed through my parents' living room, but the physicists clearly held a special place in my father's heart -- people like our neighbor, Clifford Swartz; Max Dresden; Peter Kahn; and of course C.N. Yang, a Nobel Laureate lured to Stony Brook by, among others, my dad.

One of my favorites among the physicists was a gentleman named Barry McCoy.  I first met Barry and his wife Tun-Hsu in my parents' living room before my tenth birthday.  Barry and Tun-Hsu -- who everybody called Martha -- were then and are now well-known community activists and grass-roots organizers.  They are also two of the most wonderful people I will ever meet.

I got word tonight from a friend that Martha passed suddenly while vacationing in Peru with Barry (please follow this link for a remembrance of the "public" Martha).  She was only 64.

As I reflect upon this news, it occurs to me that I would most certainly not be who I am today without Barry and Martha.  Martha, in particular, did something that changed my life.

For this to make sense, I'll need to share some history.

Barry and Martha were two of the relatively few people who attended my parents' parties who treated their hosts' eldest child as a person worthy of attention.  They both took the time to speak to a young boy of their social and political activism, and for some reason it was incredibly powerful for me to receive this message from these two adults, in my parents' living room, as the rest of the Physics Department went about its business.

For them to take time away from the adults at the party to talk to a youngster like an actual three-dimensional human being was a huge thrill.  The stories they told were always exciting, and sounded earth-shatteringly important.  I was taken by a ten-year-old's impression that these folks were on top of things in a way that few adults were.

Then one day, they asked me if I wanted to help.  "I'm eleven years old," I responded.  "What can I do?"  "You can give your time and your energy," they told me, and so it came to pass that the next week I became a volunteer campaign worker for George McGovern.

If I was the only young person these two influenced, this would already be a pretty good creation myth.  But there were literally dozens of us.  Over the next couple of years, we organized into several complementary/competitive groups; and so the SmithHaven Democratic Youth Caucus begat Youth for Political Action and so on.  We organized, and we focused our efforts on the campaigns that touched us.  The spectacle of candidates for office -- including Congress -- coming before groups of teenagers who were all too young to vote, genuinely seeking their endorsement because of the work they knew they'd get, was pretty heady.

Barry and Martha were everywhere back then, advising us on running our organizations, and tirelessly getting all this low-cost, transportation-impaired labor where it needed to be, which was frequently the McCoy house.  I may possibly have eaten more spaghetti in Barry and Martha's kitchen between the ages of 11 and 16 than I did in my own home.

Many of those youngsters Barry and Martha touched have gone on to do amazing things.  Margalit Fox is writing for The New York TimesMartin Boroson's books just might have the power to change the world, which is, after all, what we were after when we were kids.  There are others, whom I've lost track of (too many to list, but you know who you are!), but I'm sure they'd all tell you what I'd tell you -- that those years working and bonding with Barry and Martha taught us that one voice can make change.

=-=-=-=-=-=-=-=

If this was the extent of Martha's gifts to me, it would be plenty.  But I'm only now getting to her most extraordinary offering.

As I've related previously, my Dad was something of a public figure in our small town.  This was a source of some stress in my teenage life.  It's every teenager's job to rebel against their parents (and a young man's to rebel against his father in particular), but Dad was on the side of the angels in so many things..  how could I do that?

I couldn't.  So..  what I ended up pursuing was an identity separate from that of my parents, and I did that through immersing myself in local politics after the McGovern experience.  In addition to the youth groups, I was sufficiently driven that I was a Co-Campaign Manager for a Democratic County Legislature candidate at age 14.  During that campaign, my parents and I attended several fund-raising events for various candidates (my parents as donors; I was working).

I had always been introduced as "Barb and Alec's son," I suppose because, well, I was.  Still am, if you want to get technical.

One night, Martha went out of her way to introduce Barb and Alec as "Ward's parents" to several party officials.  Also true, but not nearly as obvious to the adults in the room.

Except for Martha.

My parents had always supported my volunteerism, but I think in some sense they'd looked at it as an adjunct of their own activism.  But here was somebody they knew and respected from their world, putting my efforts on equal footing with theirs in front of some pretty high-octane people.  And my Mom and Dad to boot!

My parents never looked at me in the same way after that night.  With this simple gesture, Martha had shown them that their son had established an identity independent of, yet of a piece with, their own.

I don't think I ever thanked her.

So, in my typically late fashion..

Thank you, Martha, for your energy and your faith.  Thank you for seeing a whole person where others only saw a son, and for urging that person out into the light.  Thank you for the laughter and all that spaghetti.

And, most of all, thank you for living a life which offers an object lesson in the awesome power of a single human voice, whether raised in the halls of power or whispered to a ten-year-old boy in a loud, crowded room.

God bless you, Tun-Hsu, and rest peacefully.  You've certainly earned it.

I love you.

     -wp

Non-Technical News: All Is Finally Right With The World..

Last Tuesday and Wednesday very little was right with the world, as the first two games of the 2008 regular Major League Baseball season were played half a world away, and they started at 3AM Seattle time.  I'm all for globalizing the appeal of the game, but 3AM?!?  And then have both teams come back to the States and play more exhibition games?!?  It defies reason.

Last night, things got a little better.  The Washington Nationals hosted the first regular-season baseball game in their brand new USD$611 million ballpark.  One of their better players, Ryan Zimmerman, won the game with a walk-off home run.  Pretty cool way to open a stadium, even if it is almost 3000 miles away.

But tonight..  tonight the hometown team hit the field in one of fourteen scheduled games.  With snow blowing under the Safeco Field roof and drenching the denizens of the left-field bleachers, the hometown Mariners trotted out the newly acquired Eric Beddard and won their season opener, 5-2.

Baseball is back.  All the way back.  Winter is over, despite what the people in left field (or at my grandson's baseball practice this afternoon) will tell you.

At least for a day, the Mariners are alone in first place.

All is finally right with the world..

     -wp

Posted by Ward Pond | 0 Comments
Filed under: , ,

See You At Microsoft Tech·Ed North America 2008 Developers!

Happy news this week that I'll be traveling to Orlando, Florida the first week of June to attend and present at Microsoft Tech·Ed North America 2008 Developers June 3-6 at the Orange County Convention Center.

I'll be revisiting the three presentations I made last November in Barcelona..

  • Data Modeling for OLTP Developers: Insights From Microsoft IT
  • Set-Based Thinking for the T-SQL Developer: Insights From Microsoft IT
  • Version-stamping of Database Objects using SQLCMD and SQL Server Management Studio: Insights from Microsoft IT

.. as well a presentation and associated hands-on lab which I'm developing for the Orlando conference called (with apologies to David Letterman) Stupid SQL Tricks: Insights From Microsoft IT.

You can find more information about the Barcelona presentations at the two links above.  Stupid SQL Tricks will be a compendium of devious yet useful techniques for getting the most out of SQL Server.  Several recent threads in this blog will likely be topics of conversation.

If you've got any "stupid SQL tricks" that you'd like to share, please pass them on to me via the blog's contact mechanism.  If I include your submission in the presentation, all I can offer in return is my thanks and a citation on an acknowledgements slide.  That, and the unending awe of those in the audience who will marvel at your ingenuity.

More on these events as the date approaches..

     -wp

Database Programming: The String Concatenation XML Trick, Finalized

It's an especially Good Friday when we can close the loop on a technical conversation, and I believe that our modifications to The Technique That Lance Found, also discussed here and here, are complete.  Scott Whigham left a comment on the most recent post offering an XML implementation that will run in AdventureWorks.  A little fiddling with Scott's syntax yields this approach to the original issue:

;WITH ColumnToPivot ([data()]) AS (
    SELECT p.ParentString + N', '
    FROM Parent p
    JOIN Child c
    ON c.ParentId = p.ParentId
    WHERE c.ChildId = 2
    ORDER BY p.ParentId
    FOR XML PATH(''), TYPE
),
    XmlRawData (CSVString) AS (
        SELECT (SELECT [data()] AS mydata FROM ColumnToPivot AS d FOR XML RAW, TYPE).value( '/row[1]/mydata[1]', 'NVARCHAR(max)') AS CSV_Column
)
SELECT
LEFT(CSVString, LEN(CSVString)-1) AS CSVList
FROM XmlRawData

This construction has a number of things going for it.  It seems to be the fastest of the lot (no nested SELECTs!), which is always nice, and it manages to impose an ORDER BY without resorting to the use of TOP, so we're out of the "dirty trick" business.  It also strikes me as the easiest of the bunch to read.

For the record, here's the result set from this latest syntax:

CSV_List
<Parent 1 String>,<Parent 2 String>,<Parent 3 String>

Thanks again to Lance Larsen, Adam Machanic, and Scott Whigham for contributing to this conversation.  Isn't community grand?

     -wp

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

R.I.P. Arthur C. Clarke (1917-2008)

Sad word today from the world of letters that Arthur C. Clarke has passed at 90.

While his most remarkable book was made into a most remarkable film (which bears little resemblance to the book, in my opinion), Clarke was an incredibly prolific writer, penning over 100 books and gaining a popular reputation as "the godfather of the telecommunications satellite," a device most of us in this line of work would be lost without.

My condolences to his family, friends, and many admirers..

-wp

Posted by Ward Pond | 0 Comments

Another Word On For-Fee Aggregators

One more post before I turn in..

Blogging is about passion and community, which may well be two sides of the same coin.  Our passion makes us a community.  Even though I don't read the output of my fellows in the SQL Server blogosphere nearly as much as I should, I know they're out there.  If I'm lucky, between two projects at work and a family of six at home, I go on one blog reading binge every three weeks or so.  Usually I'm not so lucky, at least in the blog-binging regard.

So it was heartening to find a very kind link from Kevin Kline (who very skillfully moderated a panel I sat on at SQL PASS 2006) regarding my comments last week about those who would charge you for access to what both he and I are giving away.  He mentions that he uses a free tool from a company headquartered in Mountain View, CA to perform exactly the task that the for-fee aggregators would like you to pay them to "do for you."

The writer of one of the considerable number of emails I got in the wake of my initial post made the excellent point that the aggregators have the right to search the Internet for content and sell the links as a value-add.  Capitalism is everywhere.  We bloggers place our material on the web precisely because we wish it to be found.  I have a feeling, though, that if many of us wanted our readers to pay to read our posts, we'd find a way to get them to pay us.

So, while no laws are being broken, Kevin makes the excellent point that he can do the same thing, for himself, for free.

So can you, gentle readers.  So can you.

Caveat emptor, ladies and gentlemen.

     -wp

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
&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
    Paren