sql for xml 另一种写法（采用 tag 与 union all，简洁易懂）


测试环境：sql 08, 08 R2, 2010,  2012, 2014 等

declare @agent table
 (
 AgentID int,
 Fname varchar(5),
 SSN varchar(11)
 )
 
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 int,
 AddressType varchar(12),
 Address1 varchar(20),
 Address2 varchar(20),
 City varchar(25),
 AgentID int
 )
 
insert into @address
 select 1, 'Home', 'abc', 'xyz road', 'RJ', 1 union all
 select 2, 'Office', 'temp', 'ppp road', 'RJ', 1 union all
 select 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 * 100,
--  NULL, AgentID, Fname, SSN,
--  NULL, NULL, NULL, NULL, NULL, NULL
--FROM @Agent
--UNION ALL
--  SELECT
--  3 AS Tag,
--  2 AS Parent,
--  AgentID * 100 + 1,
--  NULL,NULL,NULL, 
--  NULL,
--  NULL,
--  NULL, NULL, NULL, NULL, NULL
--FROM @Agent
--UNION ALL
--SELECT
--  4 AS Tag,
--  3 AS Parent,
--  AgentID * 100 + 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, NULL, 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 every agent
       -- (from tag 2 and 4: SSN, fname and adresses)
       -- will be sorted by agentID and combined into
       -- separate groups. It is necessary in same cases.
       [Agent!2!AgentID],
       [AddressCollection!3!Element], -- optional because NULL everywhere
       [Address!4!AddressType!Element]-- any ordering by elements of tag 4
 
FOR XML EXPLICIT


运行结果：

sql for xml 另一种写法（采用 tag 与 union all，简洁易懂）_xml