Execute Procedure for XML Auto

Introduction

For several years now, SQL Server has had the ability to convert the results of an arbitrary SELECT statement into XML by appending the FOR XML AUTO clause. For example, whilst:

  SELECT TOP 10 * FROM sys.types

generates a standard rowset:

name	system_type_id	user_type_id	schema_id	principal_id	max_length	precision	scale	collation_name	is_nullable	is_user_defined	is_assembly_type	default_object_id	rule_object_id	is_table_type
image	34	34	4	NULL	16	0	0	NULL	1	0	0	0	0	0
text	35	35	4	NULL	16	0	0	Latin1_General_CI_AS	1	0	0	0	0	0
uniqueidentifier	36	36	4	NULL	16	0	0	NULL	1	0	0	0	0	0
date	40	40	4	NULL	3	10	0	NULL	1	0	0	0	0	0
time	41	41	4	NULL	5	16	7	NULL	1	0	0	0	0	0
datetime2	42	42	4	NULL	8	27	7	NULL	1	0	0	0	0	0
datetimeoffset	43	43	4	NULL	10	34	7	NULL	1	0	0	0	0	0
tinyint	48	48	4	NULL	1	3	0	NULL	1	0	0	0	0	0
smallint	52	52	4	NULL	2	5	0	NULL	1	0	0	0	0	0
int	56	56	4	NULL	4	10	0	NULL	1	0	0	0	0	0

simply appending a FOR XML clause (in this case FOR XML AUTO):

  SELECT TOP 10 * FROM sys.types FOR XML AUTO

generates an XML fragment instead:

XML_F52E2B61-18A1-11d1-B105-00805F49916B

<sys.types name="image" system_type_id="34" user_type_id="34" schema_id="4" max_length="16" precision="0" scale="0" is_nullable="1" is_user_defined="0" is_assembly_type="0" default_object_id="0" rule_object_id="0" is_table_type="0"/><sys.types name="text" system_type_id="35" user_type_id="35" schema_id="4" max_length="16" precision="0" scale="0" collation_name="Latin1_General_CI_AS" is_nullable="1" is_user_defined="0" is_assembly_type="0" default_object_id="0" rule_object_id="0" is_table_type="0"/><sys.types name="uniqueidentifier" system_type_id="36" user_type_id="36" schema_id="4" max_length="16" precision="0" scale="0" is_nullable="1" is_user_defined="0" is_assembly_type="0" default_object_id="0" rule_object_id="0" is_table_type="0"/><sys.types name="date" system_type_id="40" user_type_id="40" schema_id="4" max_length="3" precision="10" scale="0" is_nullable="1" is_user_defined="0" is_assembly_type="0" default_object_id="0" rule_object_id="0" is_table_type="0"/><sys.types name="time" system_type_id="41" user_type_id="41" schema_id="4" max_length="5" precision="16" scale="7" is_nullable="1" is_user_defined="0" is_assembly_type="0" default_object_id="0" rule_object_id="0" is_table_type="0"/><sys.types name="datetime2" system_type_id="42" user_type_id="42" schema_id="4" max_length="8" precision="27" scale="7" is_nullable="1" is_user_defined="0" is_assembly_type="0" default_object_id="0" rule_object_id="0" is_table_type="0"/><sys.types name="datetimeoffset" system_type_id="43" user_type_id="43" schema_id="4" max_length="10" precision="34" scale="7" is_nullable="1" is_user_defined="0" is_assembly_type="0" default_object_id="0" rule_object_id="0" is_table_type="0"/><sys.types name="tinyint" system_type_id="48" user_type_id="48" schema_id="4" max_length="1" precision="3" scale="0" is_nullable="1" is_user_defined="0" is_assembly_type="0" default_object_id="0" rule_object_id="0" is_table_type="0"/><sys.types name="smallint" system_type_id="52" user_type_id="52" schema_id="4" max_length="2" precision="5"

Clicking on the hyperlink shows you the full XML fragment in all its glory:

<sys.types name="image" system_type_id="34" user_type_id="34" schema_id="4" max_length="16" precision="0" scale="0" is_nullable="1" is_user_defined="0" is_assembly_type="0" default_object_id="0" rule_object_id="0" is_table_type="0" />
<sys.types name="text" system_type_id="35" user_type_id="35" schema_id="4" max_length="16" precision="0" scale="0" collation_name="Latin1_General_CI_AS" is_nullable="1" is_user_defined="0" is_assembly_type="0" default_object_id="0" rule_object_id="0" is_table_type="0" />
<sys.types name="uniqueidentifier" system_type_id="36" user_type_id="36" schema_id="4" max_length="16" precision="0" scale="0" is_nullable="1" is_user_defined="0" is_assembly_type="0" default_object_id="0" rule_object_id="0" is_table_type="0" />
<sys.types name="date" system_type_id="40" user_type_id="40" schema_id="4" max_length="3" precision="10" scale="0" is_nullable="1" is_user_defined="0" is_assembly_type="0" default_object_id="0" rule_object_id="0" is_table_type="0" />
<sys.types name="time" system_type_id="41" user_type_id="41" schema_id="4" max_length="5" precision="16" scale="7" is_nullable="1" is_user_defined="0" is_assembly_type="0" default_object_id="0" rule_object_id="0" is_table_type="0" />
<sys.types name="datetime2" system_type_id="42" user_type_id="42" schema_id="4" max_length="8" precision="27" scale="7" is_nullable="1" is_user_defined="0" is_assembly_type="0" default_object_id="0" rule_object_id="0" is_table_type="0" />
<sys.types name="datetimeoffset" system_type_id="43" user_type_id="43" schema_id="4" max_length="10" precision="34" scale="7" is_nullable="1" is_user_defined="0" is_assembly_type="0" default_object_id="0" rule_object_id="0" is_table_type="0" />
<sys.types name="tinyint" system_type_id="48" user_type_id="48" schema_id="4" max_length="1" precision="3" scale="0" is_nullable="1" is_user_defined="0" is_assembly_type="0" default_object_id="0" rule_object_id="0" is_table_type="0" />
<sys.types name="smallint" system_type_id="52" user_type_id="52" schema_id="4" max_length="2" precision="5" scale="0" is_nullable="1" is_user_defined="0" is_assembly_type="0" default_object_id="0" rule_object_id="0" is_table_type="0" />
<sys.types name="int" system_type_id="56" user_type_id="56" schema_id="4" max_length="4" precision="10" scale="0" is_nullable="1" is_user_defined="0" is_assembly_type="0" default_object_id="0" rule_object_id="0" is_table_type="0" />

Note that I've referred to this as an XML fragment as it contains no root element. We can add one if we like by modifying the FOR XML AUTO clause, but I want to keep this as vanilla as possible.

Now comes the tricky part - image the data you want to return as XML isn't available directly from a table or view, but is selected by a stored procedure. How you you select that that data as XML?

Well, one option is simply to update the stored procedure such that it selects XML by adding a FOR XML clause within the procedure itself. But that'll break existing callers of the stored procedure.

You could copy/paste the stored procedure into a new one, and alter the new one to select XML. But that creates a maintenance burden as every time one stored procedure it changed, someone needs to remember to change the other one too to keep them in sync.

You could wrap one stored procedure in another. So your new stored procedures creates a temporary table, or perhaps a table variable, and does a INSERT INTO ... EXEC to populate the table with the results of the actual stored procedure, then selects the results from the temporary table with a FOR XML clause. For a lot of people, this is the option that'll work best. But it relies on the 'outer' stored procedure knowing the exact structure of the rowset selected by the 'inner' stored procedure, which may change at some point in the future. It's a solution which only works for the one stored procedure it's been coded for.

Isn't there a more flexible, generic solution? Yes there is...

Continue reading this post here »