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__sta tu__436BFEE3,</sysobjects>
IT
<sysobjects>que ue_messages_100 3150619,queue_m essages_1035150 733,queue_messa ges_1067150847, </sysobjects>
P <sysobjects>sp_ MSrepl_startup, sp_MScleanupmer gepublisher,</sysobjects>
S
<sysobjects>sys rowsetcolumns,s ysrowsets,sysal locunits,sysfil es1,syshobtcolu mns,</sysobjects>
SQ
<sysobjects>Que ryNotificationE rrorsQueue,Even tNotificationEr rorsQueue,Servi ceBrokerQueue,</sysobjects>
U
<sysobjects>spt _fallback_db,sp t_fallback_dev, spt_fallback_us g,spt_monitor,s pt_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,queu e_messages_1035 150733,queue_me ssages_10671508 47,
P sp_MScleanupmer gepublisher,sp_ MSrepl_startup,
S
sysallocunits,s ysasymkeys,sysb inobjs,sysbinsu bobjs,syscerts, syschildinsts,s ysclsobjs,sysco lpars,sysconvgr oup,sysdbfiles, sysdbreg,sysder cv,sysdesend,sy sendpts,sysfile s1,sysftinds,sy sguidrefs,sysho btcolumns,sysho bts,sysidxstats ,sysiscols,sysl nklgns,syslogsh ippers,sysmulti objrefs,sysnsob js,sysobjkeycry pts,sysobjvalue s,sysowners,sys privs,sysqnames ,sysremsvcbinds ,sysrmtlgns,sys rowsetcolumns,s ysrowsetrefs,sy srowsets,sysrts ,sysscalartypes ,sysschobjs,sys serefs,syssingl eobjrefs,syssql guides,systyped subobjs,sysuser msgs,syswebmeth ods,sysxlgns,sy sxmitqueue,sysx mlcomponent,sys xmlfacet,sysxml placement,sysxp rops,sysxsrvs,
SQ
EventNotificati onErrorsQueue,Q ueryNotificatio nErrorsQueue,Se rviceBrokerQueu e,
U
MSreplication_o ptions,seqnumbe rs,spt_fallback _db,spt_fallbac k_dev,spt_fallb ack_usg,spt_mon itor,spt_values ,
--
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
"Alexander Kuznetsov" <AK************ @hotmail.COM> wrote in message
news:11******** **************@ g43g2000cwa.goo glegroups.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.
