[SQL Server] Interroger un contenu XML

  • Imprimer

DECLARE @ID_XML int
DECLARE @XML nvarchar(max) =
'<OptimizerStatsUsage>
<StatisticsInfo LastUpdate="2024-09-09T08:20:02.46" ModificationCount="0" SamplingPercent="100" Statistics="[PK_MaTable_1_ID]" Table="[MaTable_1]" Schema="[dbo]" Database="[MaBase]">
</StatisticsInfo>
<StatisticsInfo LastUpdate="2024-09-12T10:29:12.13" ModificationCount="0" SamplingPercent="1.48539" Statistics="[_WA_Sys_BSL_NOM_1C405085]" Table="[MaTable_2]" Schema="[dbo]" Database="[MaBase]">
</StatisticsInfo>
<StatisticsInfo LastUpdate="2024-09-12T10:29:10.98" ModificationCount="0" SamplingPercent="1.48928" Statistics="[_WA_Sys_ACTIF_1C405085]" Table="[MaTable_3]" Schema="[dbo]" Database="[MaBase]">
</StatisticsInfo>
</OptimizerStatsUsage>'

exec sp_xml_preparedocument @ID_XML OUT, @XML

SELECT * FROM (
SELECT *
FROM OPENXML(@ID_XML, 'OptimizerStatsUsage/*')
WITH ([Database] nvarchar(150), [Table] nvarchar(150),[Statistics] nvarchar(150), LastUpdate datetime, ModificationCount int,SamplingPercent float)
) T1 ORDER BY ModificationCount DESC, LastUpdate ASC

exec sp_xml_removedocument @ID_XML