sql 拼接有多个子节点的xml

 

SELECT DT.ZCID,'id="'+CAST(OD.ZCID AS VARCHAR(36))+'" name="'+OD.Dept_Name+'" att2="'+OD.Dept_Code+'" att3="生产任务启动通知单协办部门" ' AS NODE  INTO #TB2
FROM DesignTask DT LEFT JOIN CooperateDept CD ON DT.DesignTaskId=CD.DesignTaskId
LEFT JOIN Org_Department OD ON CD.Dept_ID=OD.Dept_ID
WHERE OD.ZCID IS NOT NULL
ORDER BY DT.ZCID,OD.Dept_Code 
 
 
 
 ------ 还需要加上root
select ZCID, NODE = (stuff((select '' + NODE from #TB2 where ZCID = 
    a.ZCID for xml path('o')),1,0,'')) INTO #TB4 from #TB2 a group by ZCID
 
 select * from #TB4
 
 -----<被替换成了<>
 SELECT ZCID ,NODE=(STUFF((SELECT ''+ NODE FROM #TB4 WHERE ZCID=D.ZCID FOR XML PATH('root')),1,0,''))
 FROM #TB4 D
 GROUP BY ZCID

-----比较正确
SELECT NODE AS 'o' 
FROM #TB2
group by zcid,node
FOR XML PATH('root') 


-----内容正确,但不是xml格式
 SELECT ZCID ,NODE=(STUFF((SELECT  cast(NODE as xml) FROM #TB4 WHERE ZCID=D.ZCID FOR XML PATH('root')),1,0,''))  into #tb5
 FROM #TB4 D
 GROUP BY ZCID
---正确
select ZCID,CAST(NODE as xml)
from #tb5