See all products »
Curah! curation service
Microsoft Tech Companion App
Microsoft Technical Communities
Microsoft Virtual Academy
Server and Tools Blogs
TechNet Flash Newsletter
Cloud and Datacenter
Windows Server 2012 R2
System Center 2012 R2
Microsoft SQL Server 2012 SP1
Windows 8.1 Enterprise
See all trials »
Microsoft Download Center
TechNet Evaluation Center
Compatability & Converters
Microsoft Virtual Academy
Free Windows Server 2012 courses
Free Windows 8 courses
SQL Server training
MCSA: Windows 8
Windows Server Certification (MCSE)
Private Cloud Certification (MCSE)
SQL Server Certification (MCSE)
Second shot for certification
Born To Learn blog
Find technical communities in your area
For small and midsize businesses
For IT professionals
For technical support
For home users
Microsoft Premier Online
Microsoft Fix It Center
Security Bulletins & Advisories
International support solutions
Log a support ticket
Not an IT pro?
Microsoft Customer Support
Microsoft Community Forums
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)
Software Release Announcements
SQL Server 2000
SQL Server 2005
SQL Server 2008
T. Alexander Pond
TechEd Developers 2008
Browse by Tags
Ward Pond's SQL Server blog
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 a MERGE...
7 Feb 2010
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 MERGE and...
24 Aug 2009
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...
18 Jun 2009
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...
27 Feb 2009
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 easily explained...
28 Jan 2009
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...
21 Mar 2008
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...
19 Mar 2008
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...
15 Mar 2008
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...
13 Mar 2008
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...
21 Feb 2008
New To SSIS? Come Take A Guided Tour..
One of the more interesting and perhaps challenging aspects of a SQL Server 2000 to SQL Server 2005 upgrade is the migration from DTS to SSIS for ETL (Extract-Transform-Load) activities. SSIS isn't an upgrade of DTS, but is in fact an entirely new, and greatly enhanced, component. While it's possible...
10 Mar 2007
Database Programming: Using The modify() Method Against An XML Variable
Here's the first technical tid-bit on this blog in awhile, courtesy of a long-concluded internal conversation that I rediscovered when I was cleaning out three months' worth of email from my inbox. Roger wrote: I'm trying to use the modify() method against an XML variable, and I keep getting...
3 Feb 2007
Database Programming: Sergey Offers Another FOR XML EXPLICIT Syntax For Steve
If you've worked with databases long enough, you develop a set of truisms that you carry around in your head. One of mine, related to building T-SQL syntax, is, "there are at least 87 ways to skin any particular cat." While we're still 85 methods short of that benchmark in our current discussion, the...
12 Sep 2006
Database Programming: Buliding a FOR XML PATH Statement Containing Both an Element Value and an Attribute Associated With The Element
On Friday afternoon, I shared an answer to Steve's question seeking a FOR XML EXPLICIT query to produce this XML: < Product > < Category rank = " 0 " > Category Name </ Category > </ Product > Well, over the weekend my "geek beanie" went all atwirl wondering if this XML...
11 Sep 2006
Database Programming: Buliding a FOR XML EXPLICIT Statement Containing Both an Element Value and an Attribute Associated With The Element
Well, the title of this post is so long, I almost don't need to reconstruct Steve's question, but here it is.. I need to construct a FOR XML EXPLICIT statement that essentially produces something like < Product > < Category rank = " 0 " > Category Name </ Category > </ Product...
9 Sep 2006
Database Programming: SET Options That Impact XML Access In SQL Server 2005
The information in this post comes from Books OnLine (ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/6bb3bfaa-0ddd-423a-ac0d-e9bafcc1ad76.htm) and this online update. There are a number of SET options that impact access to data contained in the XML datatype, as well as the ability to construct and...
4 May 2006
Database Programming: A Word About XQuery Performance Tuning In SQL Server 2005
In an earlier post , I offered a performance analysis of two equivalent XPath queries. While the results of the analysis are sound, I wish to alert you to a flaw in a portion of my methodology which I learned of today. The crux of the matter is that the "query costing" functionality in SQL Server...
10 Mar 2006
Loading An XML Document Into SQL Server: Another OPENROWSET Trick
Have you ever had an XML document in a file that you needed to get into a database column quick-and-dirty? Well, another OPENROWSET trick is ready to come to our rescue. Up until I was shown this trick, every time I needed to import XML into a data column, I'd load up a copy of the XML in SQL Server...
8 Mar 2006
Database Programming: Applying APPLY -- Solving Parent Axis Access Performance Issues In XML Access In SQL Server 2005
This little nugget will save you an incredible amount of time if you face the need to retrieve data from multiple levels of the same XML document in one SELECT statement. The two methods we'll examine in this post are “parent axis access,” the “classic” method of addressing this requirement, and...
20 Feb 2006
XML Programming in SQL Server 2005
With the RTM of SQL Server 2005, excitement is understandably running very high in the SQL Server developer community. If you haven't had a chance to get your hands on this release, I urge you to do so as soon as you can. It absolutely rocks. MSDN subscribers can download it here (and will also like...
30 Oct 2005
A Solution For Stripping Invalid XML Characters From varchar/text Data Structures
One of the more subtle aspects of converting (n)varchar or (n)text data to XML is the fact that XML is choosy about which characters are permitted and (n)/varchar/(n)text is not. Any T-SQL programmer who runs conversions of this type is likely to run into this issue. Here's a code block that resolves...
7 Jul 2005
SQL Server 2005 XQuery Performance Tips
During some recent Xquery performance work, three discoveries emerged which dramatically improved the performance of my team's component. I'd like to share them here.. Specify A Single Root Node The XML parser in SQL2K5 doesn't assume that the XML it receives is well-formed. It will therefore look...
24 Jun 2005
© 2014 Microsoft Corporation.
Privacy & Cookies