Je n'arrive pas à comprendre comment effectuer une recherche dans une colonne XML de mon tableau.

Question

J'ai une table appelée v_EpisodeAudit, avec une colonne appelée EventData qui contient des données XML. Les données XML diffèrent d'une ligne à l'autre, de sorte qu'un enregistrement peut contenir des données XML dans cette colonne qui ressemblent à ceci :

<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>

tandis qu'une autre ligne peut contenir les données XML suivantes :

<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>

Il existe également différents types/configurations de données XML. J'ai lu une vingtaine de sources différentes ce matin pour essayer de trouver comment rechercher dans les données XML de cette colonne un numéro d'épisode spécifique dans les XML CloseEpisode, mais je n'y parviens pas. Quelqu'un peut-il m'aider à trouver une requête qui permette de trouver un numéro d'épisode spécifique dans cette colonne ?

Answer 1

XML peut être interrogé de manière très générique. Quelques approches :

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

--Ceci renverra le tout premier nœud du deuxième niveau.

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

--Ceci renverra tous les nœuds du deuxième niveau et utilisera les données suivantes WHERE con LIKE pour trouver les éléments de l'épisode..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'

--Similaire mais filtrage au niveau 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)

Answer 2

Utiliser les fonctions d'interrogation xml

select EventData.value('(/CloseEpisode/EpisodeId)[1]','int')
from v_EpisodeAudit
where EventData.value('local-name(/*[1])','varchar(100)')='CloseEpisode'

ou peut-être

select EventData 
from @v_EpisodeAudit
where EventData.value('(/CloseEpisode/EpisodeId)[1]','int')=652503

en fonction de ce que vous essayez de faire.

Si vous ne connaissez pas le nom du nœud racine, vous pouvez utiliser

 select EventData.value('(//EpisodeId)[1]','int')
 from v_EpisodeAudit
 where EventData.exist('//EpisodeId')=1

Voir https://docs.microsoft.com/en-us/sql/t-sql/xml/value-method-xml-data-type