SQLServer中将XML值转成表格内容

原始XML值

<usertasks>
	<usertask usertaskid="165204" userid="103536" username="丁**" suggection="本研究是根据《药品不良反应报告和监测管理办法》要求开展的一项上市后重点监测研究" datetime="9/7/2020 12:06:24 PM" type="cra"/>
	<usertask usertaskid="165205" type="departdirector" userid="100232" username="宋**" suggection="" resultstatus="APPROVE" issignoff="1" datetime="9/7/2020 4:19:52 PM"/>
	<usertask usertaskid="165254" type="pi" userid="100033" username="王**" suggection="" resultstatus="APPROVE" issignoff="1" datetime="9/9/2020 5:22:51 PM"/>
	<usertask usertaskid="165593" type="departdirector" userid="100072" username="吴**" suggection="" resultstatus="APPROVE" issignoff="1" datetime="9/11/2020 8:29:04 AM"/>
	<usertask usertaskid="165794" type="secretary" userid="100619" username="李**" suggection="1、请完善系统研究人员名单； 2、请上传药物批件。 3、请上传药品说明书； 4、请上传保险保单。" resultstatus="REJECT" datetime="11/2/2020 2:50:02 PM"/>
	<usertask usertaskid="171681" userid="103536" username="丁**" suggection="本研究是根据《药品不良反应报告和监测管理办法》要求开展的一项上市后重点监测研究" datetime="11/9/2020 3:26:43 PM" type="crc"/>
	<usertask usertaskid="173216" type="secretary" userid="100619" username="李**" suggection="" resultstatus="APPROVE" datetime="11/11/2020 5:03:53 PM"/>
	<usertask usertaskid="173878" type="orgdirector" userid="100001" username="刘**" suggection="同意" resultstatus="APPROVE" issignoff="1" datetime="11/12/2020 2:58:29 PM"/>
</usertasks>

SQL处理

DECLARE @usertask XML;
SET @usertask = '
<usertasks>
	<usertask usertaskid="165204" userid="103536" username="丁**" suggection="本研究是根据《药品不良反应报告和监测管理办法》要求开展的一项上市后重点监测研究" datetime="9/7/2020 12:06:24 PM" type="cra"/>
	<usertask usertaskid="165205" type="departdirector" userid="100232" username="宋**" suggection="" resultstatus="APPROVE" issignoff="1" datetime="9/7/2020 4:19:52 PM"/>
	<usertask usertaskid="165254" type="pi" userid="100033" username="王**" suggection="" resultstatus="APPROVE" issignoff="1" datetime="9/9/2020 5:22:51 PM"/>
	<usertask usertaskid="165593" type="departdirector" userid="100072" username="吴**" suggection="" resultstatus="APPROVE" issignoff="1" datetime="9/11/2020 8:29:04 AM"/>
	<usertask usertaskid="165794" type="secretary" userid="100619" username="李**" suggection="1、请完善系统研究人员名单； 2、请上传药物批件。 3、请上传药品说明书； 4、请上传保险保单。" resultstatus="REJECT" datetime="11/2/2020 2:50:02 PM"/>
	<usertask usertaskid="171681" userid="103536" username="丁**" suggection="本研究是根据《药品不良反应报告和监测管理办法》要求开展的一项上市后重点监测研究" datetime="11/9/2020 3:26:43 PM" type="crc"/>
	<usertask usertaskid="173216" type="secretary" userid="100619" username="李**" suggection="" resultstatus="APPROVE" datetime="11/11/2020 5:03:53 PM"/>
	<usertask usertaskid="173878" type="orgdirector" userid="100001" username="刘**" suggection="同意" resultstatus="APPROVE" issignoff="1" datetime="11/12/2020 2:58:29 PM"/>
</usertasks>';

SELECT  T.C.value('@usertaskid', 'int') AS usertaskid ,
        T.C.value('@userid', 'int') AS userid ,
        T.C.value('@username', 'VARCHAR(160)') AS username ,
        T.C.value('@resultstatus', 'VARCHAR(160)') AS resultstatus ,
        T.C.value('@suggection', 'VARCHAR(320)') AS [suggection] ,
        T.C.value('@datetime', 'datetime') AS [createdate]
FROM    @usertask.nodes('/usertasks/usertask') AS T ( C );

通过SQL语句转成表格内容

usertaskid	userid	username	resultstatus	suggection	createdate
165204	103536	丁**	NULL	本研究是根据《药品不良反应报告和监测管理办法》要求开展的一项上市后重点监测研究	2020-09-07 12:06:24.000
165205	100232	宋**	APPROVE		2020-09-07 16:19:52.000
165254	100033	王**	APPROVE		2020-09-09 17:22:51.000
165593	100072	吴**	APPROVE		2020-09-11 08:29:04.000
165794	100619	李**	REJECT	1、请完善系统研究人员名单； 2、请上传药物批件。 3、请上传药品说明书； 4、请上传保险保单。	2020-11-02 14:50:02.000
171681	103536	丁**	NULL	本研究是根据《药品不良反应报告和监测管理办法》要求开展的一项上市后重点监测研究	2020-11-09 15:26:43.000
173216	100619	李**	APPROVE		2020-11-11 17:03:53.000
173878	100001	刘**	APPROVE	同意	2020-11-12 14:58:29.000

实际使用

-- 查询最近一次流程的任务处理概况
SELECT  T.C.value('@usertaskid', 'int') AS usertaskid ,
        T.C.value('@userid', 'int') AS userid ,
        T.C.value('@username', 'VARCHAR(160)') AS username ,
        CASE WHEN T.C.value('@appresult', 'VARCHAR(160)') IS NOT NULL
             THEN T.C.value('@appresult', 'VARCHAR(160)')
             WHEN T.C.value('@resultstatus', 'VARCHAR(160)') IS NOT NULL
             THEN T.C.value('@resultstatus', 'VARCHAR(160)')
             ELSE 'APPLY'
        END AS appresult ,
        T.C.value('@suggection', 'VARCHAR(320)') AS [suggestion] ,
        T.C.value('@datetime', 'datetime') AS [createdate]
FROM    ( SELECT    CONVERT(XML, wis.Value) AS usertask
          FROM      WorkflowInstanceSettings AS wis
          WHERE     wis.Name = 'result'
                    AND wis.DeletedDate IS NULL
                    AND WorkflowInstanceID = ( SELECT TOP ( 1 )
                                                        WorkflowInstanceID
                                               FROM     dbo.UserTasks
                                               WHERE    DeletedDate IS NULL
                                               ORDER BY CreatedDate DESC
                                             )
        ) AS k
        CROSS APPLY k.usertask.nodes('/usertasks/usertask') AS T ( C )
ORDER BY createdate;