sql-server - 不知道如何在我的表中搜索 XML 列

Question

我有一个名为 v_EpisodeAudit 的表，其中有一个名为 EventData 的列，其中包含 XML 数据。 XML 数据因行而异，因此一条记录可能在此列中包含如下所示的 XML 数据:

<AddMDMDocument>
  <EpisodeMDMId>282521</EpisodeMDMId>
  <OncologyReferral>0</OncologyReferral>
  <SpecialPalliativeReferral>0</SpecialPalliativeReferral>
  <SurgeonReferral>0</SurgeonReferral>
  <MDMReport>0</MDMReport>
  <GPReferral>0</GPReferral>
  <GPReferralApproval>0</GPReferralApproval>
  <GeneralPalliativeCare>0</GeneralPalliativeCare>
  <AuditLogin>mkell010</AuditLogin>
  <AuditTrust>4</AuditTrust>
  <Error />
</AddMDMDocument>

而另一行可能包含以下 XML 数据:

<CloseEpisode>
  <EpisodeId>652503</EpisodeId>
  <TrackingStatusId>9</TrackingStatusId>
  <TrackingClosureReason>100</TrackingClosureReason>
  <DateOfTrackingClosure>Sep 25 2017 12:37PM</DateOfTrackingClosure>
  <AuditLogin>ccass001</AuditLogin>
  <AuditTrust>1</AuditTrust>
  <Error />
</CloseEpisode>

还有更多不同类型/配置的 XML 数据。今天早上我阅读了大约 20 个不同的来源，试图找出如何搜索本专栏中的 XML 数据以获取 CloseEpisode XML 中的特定 EpisodeId，但我终究无法弄清楚。任何人都可以帮助我进行查询以在此列中找到指定的 EpisodeId 吗？

Answer 1

XML 可以非常通用地查询。一些方法:

DECLARE @v_EpisodeAudit TABLE(ID INT IDENTITY, [EventData] XML);
INSERT INTO @v_EpisodeAudit VALUES
 (N'<AddMDMDocument>
  <EpisodeMDMId>282521</EpisodeMDMId>
  <OncologyReferral>0</OncologyReferral>
  <SpecialPalliativeReferral>0</SpecialPalliativeReferral>
  <SurgeonReferral>0</SurgeonReferral>
  <MDMReport>0</MDMReport>
  <GPReferral>0</GPReferral>
  <GPReferralApproval>0</GPReferralApproval>
  <GeneralPalliativeCare>0</GeneralPalliativeCare>
  <AuditLogin>mkell010</AuditLogin>
  <AuditTrust>4</AuditTrust>
  <Error />
</AddMDMDocument>')
,(N'<CloseEpisode>
  <EpisodeId>652503</EpisodeId>
  <TrackingStatusId>9</TrackingStatusId>
  <TrackingClosureReason>100</TrackingClosureReason>
  <DateOfTrackingClosure>Sep 25 2017 12:37PM</DateOfTrackingClosure>
  <AuditLogin>ccass001</AuditLogin>
  <AuditTrust>1</AuditTrust>
  <Error />
</CloseEpisode>');00

--这将返回第二层的第一个节点

SELECT ID
      ,vEA.[EventData].value(N'local-name(/*[1]/*[1])',N'nvarchar(max)') AS NodeName
      ,vEA.[EventData].value(N'/*[1]/*[1]/text()[1]',N'nvarchar(max)') AS NodeValue
FROM @v_EpisodeAudit AS vEA

--这将返回第 sevond 级别的所有节点，并使用 WHERE 和 LIKE 来查找 Episode..Id 元素

SELECT ID
      ,SecondLevelNode.Nd.value(N'local-name(.)',N'nvarchar(max)') AS NodeName
      ,SecondLevelNode.Nd.value(N'text()[1]',N'nvarchar(max)') AS NodeValue
FROM @v_EpisodeAudit AS vEA
OUTER APPLY vEA.[EventData].nodes(N'/*/*') AS SecondLevelNode(Nd)
WHERE SecondLevelNode.Nd.value(N'local-name(.)',N'nvarchar(max)') LIKE 'Episode%' --or LIKE 'Episode%Id'

--类似但在XQuery级别过滤

SELECT ID
      ,SecondLevelNode.Nd.value(N'local-name(.)',N'nvarchar(max)') AS NodeName
      ,SecondLevelNode.Nd.value(N'text()[1]',N'nvarchar(max)') AS NodeValue
FROM @v_EpisodeAudit AS vEA
OUTER APPLY vEA.[EventData].nodes(N'/*/*[substring(local-name(),1,7)="Episode"]') AS SecondLevelNode(Nd)