sql server - FOR XML ... TYPE much slower than FOR XML? -

running sql server 2014. have stored procedure returns quite large xml. goes this:

select(    ...    xml path (n''), root, type 

now, query runs in 1 second. if remove type runs in around half time:

select(    ...    xml path (n''), root 

obviously, latter returns nvarchar(max) instead of xml. want xml data, if ask xml gets slower! if want fetch xml data on client, necessary convert xml using type directive above?

q: anyway, why for xml ... type significantly slower for xml ...? there way improve conversion?

did try set variables results xml , varchar(max) without displaying them? maybe time difference measure bound preparing viewer? pasting first letters grid column faster creating formed, indented, displayable xml...

sepcifying "type" not needed in cases. need nested xml only. play around aliases, path- , root-literals , - of course - or without type:

and - important! - try call surrounding select , without:

select  (     select tbls.table_name [@tablename]           ,(              select column_name [@columname]              information_schema.columns cols               cols.table_name=tbls.table_name              xml path('column') /*,type*/             ) /*as alias*/     information_schema.tables tbls     xml path('table'),root('all_tables') /*,type*/  ) /*as alias*/ 

i don't know, how continue generated xml. if transfer application plain string anyway.

conclusio: take faster approach :-)

by way...

i not know stored procedure , else done there besides select...

in cases bad habit use sps read data.

if sp not more wrapper around select should think (single-statement!) table valued function retrieve data.

this function queried , transformed xml with

select * dbo.myfunction(/*parameters*/) xml path('therowsname'),root('therootname') [,type] 

or - if need xml everytime, might define scalar function delivering xml or varchar(max). re-usability of functions way better sps...