How to use XML parameters in stored procedures

Most professional business programmers have found themselves on the receiving end of more than a few crazy requirement or design documents at least once in their careers. These stoic programmers grind their teeth as they code designed-by-committee Rube Goldberg UIs, or facepalm after they've read a document specifying the "business need" to tack-on scads of required parameters to dozens of stored procedures heavily used by data classes and reports.
While nothing can fix the first problem (short of a different BA), the second problem can be simplified in code. The examples below show how to replace multiple stored procedure parameters using only one XML data type parameter. Even better, these examples show how to pass XML data to stored procedures, and how they can be made to read single or multiple sets of criteria easily, using only a few lines of TSQL.

Stored procedure XML

This screenshot lists the source of a SQL Server 2014 stored procedure hitting the AdventureWorks2012 database, selecting data from the Customer and SalesOrderHeader tables. Both of these tables in turn are joined to the XML criteria, limiting results.

Stephen Glasskeys

To execute, pass in an XML structure similar to one found in the screenshot below, or another XML structure better suited to your needs. Notice the flexibility, how easy the XML data type allows programmers to pass in a single or multiple rows of criteria.

Stephen Glasskeys

For reporting uses, XML parameters really shine: You can pass the same XML string to multiple stored procs, yet you won't need to modify all of them, if say, one report's stored procedure requires additional criteria fields at some point in the future (providing of course, existing xml field names and data types haven't been altered).
Finally, this snippet of working source illustrates how easy it is to INSERT XML data into temp tables and table variables, allowing even greater flexibility:

Stephen Glasskeys

This article is published as part of the IDG Contributor Network. Want to Join?