--hack3r--
New Member
I am looking at whether it is possible to execute arbitrary SQL commands (dynamic SQL statements or stored procedures) and return the result as XML, in an Azure SQL Database.I know it can be done with regular, on premises SQL Server database - in our case we use a CLR function. Alternatives 'regular' solutions include using OPENROWSET or OPENQUERY, neither of which is available for Azure.A new EXECUTE ... AS FOR XML option is available as of SQL Server 2012, but when I try it I get an error - I am unable to locate correct examples of its use.\[code\]exec ('select ''A'', 2, ''d''')with result sets (as for xml)\[/code\]returns\[quote\] Msg 11537, Level 16, State 1, Line 1 EXECUTE statement failed because its WITH RESULT SETS clause specified 1 column(s) for result set number 1, but the statement sent 3 column(s) at run time.\[/quote\]To be explicitly clear; I can't control the command being passed - it is most likely a stored procedure, and it most likely returns a single 'regular' (i.e. non-xml) resultset. The use case for this is in a set of SQL tests, not the actual production code.