MERGE, XML Access, and a CTE: Not So Fast

A bit of old news, as I return to the blog in earnest after some time away.. Last summer, in the posts Database Programming: What I’ve Learned About SQL Server 2008 (with a little on SQL Server 2005 thrown in) and Database Programming: Why I Like MERGE, I commented that I’d been able to build…

0

Database Programming: Why I Like MERGE

In the wake of last month’s post on my new development efforts in SQL Server 2008, several of you have asked me to go into further detail.  This post is my first effort to answer those questions; right now, we’ll focus on these two, which relate to this statement: By using a CTE with a…

1

Database Programming: Using the .modify() Method Against an XML Variable in SQL Server 2008

Make a note..  here’s my first useful programming tip for SQL Server 2008 (and yes, there will be more to come; I’m about a week and a half into my first SQL Server 2008 development project).. Way back in February of aught-seven, I shared Jerry’s solution for running the .modify() method against an XML memory…

0

Database Programming: The String Concatenation XML Trick, Revisited

UPDATED 27 February 2009 for spelling  I’ve got to pay more punctual attention to my comment pool.. RBarryYoung’s movingsql.com will be on my blog roll shortly after I get this posted (second attempt; first thwarted by a laptop hang.  I have suspended unit testing of SQLRAP 2.5 changes until I’m done.) for two very good…

6

SINGLE_BLOB Size Limit: An Affirmation for Bert

Bert left a comment on this post pointing out a limitation of “the other OPENROWSET trick” (the one which loads an XML file into a single XML column): I tried this, but had an error because my XML file is 6gB. The SINGLE_BLOB seems to be limited to 2gB Bert is correct; fortunately, this is…

1

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…

4

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…

5

Database Programming: The String Concatenation XML Trick

Courtesy of my good friend and once-and-always colleague, Lance Larsen, who writes: I recently ran into this little trick. Joining two tables having a one-to-many relationship and stuffing a set of column values from the many side into a single column on the one side. Works only for one base row at a time –…

8

An Answer For Spazecaze: A Reasonable Method To Implement User Defined Fields

Spazecaze discovered a February, 2006 discussion of order of operations and poses the following question in its comment area: So how would you go about designing a database that allows for end user defined fields?   For instance, a system is built to allow users to enter data into fields on a form.  These fields are…

3