SQL FOR XML also has a way of writing (with tag and union all, simple and easy to understand)

SQL FOR XML also has a way of writing (with tag and union all, simple and easy to understand)

Test environment: SQL, R2, 2010, 2012, 2014, etc.

Declare @agent table (agentid int, Fname varchar (5), SSN varchar (one)) insert into @agent Select 1, ' Vimal ', ' 123-23-4521 ' UNION ALL Select 2, ' Jacob ', ' 321-52-4562 ' union ALL select 3, ' Tom ', ' 252-52-4563 ' Declare @address table (Addressid I NT, addresstype varchar, Address1 varchar, Address2 varchar, city varchar (+), Agentid int) insert INTO @addr ESS Select 1, ' Home ', ' abc ', ' XYZ road ', ' RJ ', 1 union ALL Select 2, ' Office ', ' temp ', ' PPP road ', ' RJ ', 1 UNION ALL Selec T 3, ' Home ', ' xxx ', ' AAA road ', ' NY ', 2 union ALL Select 4, ' Office ', ' CCC ', ' Oli Com ', ' CL ', 2 union ALL Select 5, ' Temp ' , ' Eee ', ' Olkiu road ', ' CL ', 2 union ALL Select 6, ' Home ', ' ttt ', ' Loik road ', ' NY ', 3--select--1 as Tag,--NULL as Parent ,--0 as ' agents!1! Sort!hide ',--null as ' agents!1! ',--null as ' agent!2! Agentid ',--NULL as ' agent!2! fname! Element ',--NULL as ' agent!2! Ssn! Element ',--NULL as ' addresscollection!3! Element ',--NULL as ' address!4!! XML ',--NULL as ' address!4! addresstype! Element ',--NULL as ' Address!4! address1! Element ',--NULL as ' address!4! address2! Element ',--NULL as ' address!4! city! Element '--union all--select--2 as Tag,--1 as Parent,--Agentid *,--null, Agentid, Fname, SSN,--null, NULL, NULL, NULL, NULL, Null--from @Agent--union all--select--3 as Tag,--2 as Parent,--Agentid * + 1,--null,null,null,--null,--NULL,--N ULL, NULL, NULL, NULL, Null--from @Agent--union all--select--4 as Tag,--3 as Parent,--Agentid * + 2,--null, NULL, NULL  , NULL, NULL,--' <!--' + addresstype + ' Address--', AddressType,--address1, Address2, City--from @Address--order by [agents!1! Sort!hide]--for XML EXPLICIT SELECT 1 as Tag, null as Parent, NULL as [agents!1!], null as [Agent !2! Agentid], NULL as [agent!2! fname! Element], NULL as [agent!2! Ssn! Element], NULL as [addresscollection!3! Element], NULL as [address!4! addresstype! Element], NULL as [address!4! address1! Element], NULL as [address!4! address2! Element], NULL as [Address!4! city!  Element] UNION all SELECT 2 as Tag, 1 as Parent, NULL, Agentid, Fname, SSN, null,null, NULL, NULL, Null from @Agent UNION all SELECT 3 as Tag, 2 as Parent, null,agentid,null, NULL, NULL, NULL, NU       LL, NULL, NULL from @Agent UNION all SELECT 4 as Tag, 3 as Parent, Null,agentid,null,null,null, AddressType, Address1, Address2, city from @Address ORDER by--all properties of the every agent--(from tag 2 and 4:SSN, fname and adresses)--'ll be sorted by Agentid and combined into--separate groups.       It's necessary in same cases. [agent!2! Agentid], [addresscollection!3! Element],--optional because NULL everywhere [address!4! addresstype!  element]--any ordering by elements of the tag 4 for XML EXPLICIT

Execution Result:

Article Source: http://social.msdn.microsoft.com/Forums/sqlserver/zh-CN/97f79941-324e-479e-ba5b-851cc534ebe5/ Problem-in-for-xml-explicit-query?

Forum=sqlxml

SQL FOR XML also has a way of writing (with tag and union all, simple and easy to understand)