Posted by Tony Rogerson on 12/05/05 20:26

The beuty about this fellow, eg..

select type,
(
select name + ',' as [text()]
from sys.objects soi
where soi.type = t.type
order by name
for xml path( '' ), root( 'sysobjects' ), type
)
from ( select distinct type from sys.objects ) as t

Will give output like this...

D <sysobjects>DF__spt_value__statu__436BFEE3,</sysobjects>
IT
<sysobjects>queue_messages_1003150619,queue_messages_1035150733,queue_messages_1067150847,</sysobjects>
P <sysobjects>sp_MSrepl_startup,sp_MScleanupmergepublisher,</sysobjects>
S
<sysobjects>sysrowsetcolumns,sysrowsets,sysallocunits,sysfiles1,syshobtcolumns,</sysobjects>
SQ
<sysobjects>QueryNotificationErrorsQueue,EventNotificationErrorsQueue,ServiceBrokerQueue,</sysobjects>
U
<sysobjects>spt_fallback_db,spt_fallback_dev,spt_fallback_usg,spt_monitor,spt_values,</sysobjects>

Which isn't XML, in fact take the root off and you are just left with the
concatenated data - no tags, its an extension to the FOR XML just for this
purpose which is requested a lot.

Taking the ROOT off gives...

D DF__spt_value__statu__436BFEE3,
IT
queue_messages_1003150619,queue_messages_1035150733,queue_messages_1067150847,
P sp_MScleanupmergepublisher,sp_MSrepl_startup,
S
sysallocunits,sysasymkeys,sysbinobjs,sysbinsubobjs,syscerts,syschildinsts,sysclsobjs,syscolpars,sysconvgroup,sysdbfiles,sysdbreg,sysdercv,sysdesend,sysendpts,sysfiles1,sysftinds,sysguidrefs,syshobtcolumns,syshobts,sysidxstats,sysiscols,syslnklgns,syslogshippers,sysmultiobjrefs,sysnsobjs,sysobjkeycrypts,sysobjvalues,sysowners,sysprivs,sysqnames,sysremsvcbinds,sysrmtlgns,sysrowsetcolumns,sysrowsetrefs,sysrowsets,sysrts,sysscalartypes,sysschobjs,sysserefs,syssingleobjrefs,syssqlguides,systypedsubobjs,sysusermsgs,syswebmethods,sysxlgns,sysxmitqueue,sysxmlcomponent,sysxmlfacet,sysxmlplacement,sysxprops,sysxsrvs,
SQ
EventNotificationErrorsQueue,QueryNotificationErrorsQueue,ServiceBrokerQueue,
U
MSreplication_options,seqnumbers,spt_fallback_db,spt_fallback_dev,spt_fallback_usg,spt_monitor,spt_values,

--
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials


"Alexander Kuznetsov" <AK_TIREDOFSPAM@hotmail.COM> wrote in message
news:1133805541.815085.250880@g43g2000cwa.googlegroups.com...
>> In SQL Server 2005 we can do it in one very simple statement utilising
>> FOR
>> XML extensions, this makes for less code, less complexity and the logic
>> is
>> coded once in a central location - do you not agree that is good?
>
> Tony,
>
> how much is the output from FOR XML taxing the network bandwidth?
> Most of the xml I'm dealing with is at least 50 times smaller when
> zipped.
>

[Back to original message]