XML查询和递归数据查信息

 XML SQL

--where a.CYHYZ.exist('/ArrayOfString[string="b41f1bb2-4de3-4a97-96a1-67e6fee268be"]')=1

----会议室报表----

   select a.HYZT as 会议主题,a.meetingRoom as 会议室名称,stuff((

select  ','+c.Name from OT_User c

 where

  c.ObjectID in(

  SELECT 

  T.c.value('text()[1]','varchar(36)') AS id

   FROM

     a.CYHYZ.nodes('/ArrayOfString/string') T(c)

) for xml path('')

),1,1,'') as  参与会议者

   ,a.CSR as 抄送人,c.Name as 会议纪要人,a.NBWB as 内部外部,a.HYZQ as 会议周期

   ,a.HYLX as 会议类型 ,a.HYSB as 会议设备,a.YWJCSX as 有无决策事项

   ,a.JYSXSPR as 决议事项审批人,a.YDRQ as 预订日期 

   --获取xml返回UserID后查询用户名 

   from [dbo].[I_XZ_HYSSQD] a 

   join [dbo].[OT_InstanceContext] b on a.ObjectID=b.BizObjectId and b.State=4

   join OT_User c on a.HYJYR=c.ObjectID   

 递归SQL

WITH CTE AS 

(

--父项

SELECT ObjectID,ParentID,Name 

FROM OT_OrganizationUnit WHERE ParentID='18f923a7-5a5e-426d-94ae-a55ad1a4b240'

UNION ALL 

--递归结果集中的下级 

SELECT a.ObjectID,a.ParentID,b.Name 

FROM OT_OrganizationUnit a

INNER JOIN CTE b ON b.ObjectID=a.ParentID

)

SELECT u.ObjectID as '用户id',t.Name as '部门'

FROM OT_User u 

INNER JOIN CTE t ON u.ParentID=t.ObjectID where u.ParentID<>'18f923a7-5a5e-426d-94ae-a55ad1a4b240'