sql-server - 使用 T-SQL 粉碎 XML - 从组内部提取 Line 值的正确语法是什么？

Question

我正在尝试使用我在 XML 中接收的一些日志数据。
在清理数据以使其成为有效的 XML 之后，我在 SQL Server 中获得了 XML 数据包。 (并从 JSON 包装器等中获取其他属性)
但是现在我被困在试图读取 XML 以从各个行中检索值
我的示例 XML 如下所示:

<?xml version="1.0" encoding="UTF-8"?>
<general>
   <group id="0" comment="Application">
      <N1 comment="Start Date">2020-11-03T00:05:48Z</N1>
      <N2 comment="Name/Description">ProgramName</N2>
      <N3 comment="Version Number">ReleaseNumber</N3>
      <N5 comment="Compilation Date">2020-10-01T01:05:01Z</N5>
      <N6 comment="Up Time">1899-12-30T00:00:56Z</N6>
   </group>
   <group id="1" comment="Exception">
      <N1 comment="Date">Tue, 3 Nov 2020 11:06:45 +1100</N1>
      <N2 comment="Address">MemoryAddress</N2>
      <N3 comment="Module Name">ModuleName</N3>
      <N4 comment="Module Version">ModuleVersionNumber</N4>
      <N5 comment="Type">ExceptionType</N5>
      <N6 comment="Message">Insufficient memory for this operation.</N6>
      <N7 comment="ID">ExceptionID</N7>
      <N8 comment="Count">1</N8>
      <N9 comment="Status">New</N9>
      <N11 comment="Sent">0</N11>
   </group>
</general>

问题是，我不确定如何查询 Group 0 N6，我一直在使用:

DECLARE @x XML
select @X
,LEFT(@X.value('(/log/@version)[1]','VARCHAR(10)') ,10)

但是我无法围绕必要的 XQuery/XPath 将子行的值拉入特定编号的组中。

@X.value('(/log/group[1]/N2)[1]','VARCHAR(50)') ,10)

任何人都可以分享可以从 N2 查询值的魔法吗？我怀疑答案在“包含”中，但我在寻找编写代码教程以将指令嵌入到我的脑海中时遇到了问题。
(这很复杂，因为我想从 XML 中的 3 个不同组中提取 10 个值。要查询我收到的不同版本的日志(我将其分解为一个平面文件，以便我可以提取属性)我结束了运行:

    ,ExceptionAddress=LEFT(@X.value('(/Doc/Log/General/Line_2.2/@Value)[1]','VARCHAR(10)') ,10)
    ,ExceptionType=LEFT(@X.value('(/Doc/Log/General/Line_2.5/@Value)[1]','VARCHAR(50)') ,50)
    ,ExceptionMessage=LEFT(@X.value('(/Doc/Log/General/Line_2.6/@Value)[1]','NVARCHAR(200)') ,200)
    ,FormClass=LEFT(@X.value('(/Doc/Log/General/Line_4.1/@Value)[1]','VARCHAR(50)') ,50)
    ,FormText=LEFT(@X.value('(/Doc/Log/General/Line_4.2/@Value)[1]','NVARCHAR(50)') ,50)
    ,ControlClass=LEFT(@X.value('(/Doc/Log/General/Line_4.3/@Value)[1]','VARCHAR(50)') ,50)
    ,ControlText=LEFT(@X.value('(/Doc/Log/General/Line_4.4/@Value)[1]','NVARCHAR(50)') ,50)
    ,OSType=LEFT(@X.value('(/Doc/Log/General/Line_6.1/@Value)[1]','VARCHAR(50)') ,50)
    ,OSBuild=LEFT(@X.value('(/Doc/Log/General/Line_6.2/@Value)[1]','VARCHAR(50)') ,50)
    ,OSUpdate=LEFT(@X.value('(/Doc/Log/General/Line_6.3/@Value)[1]','VARCHAR(50)') ,50)

Answer 1

您的 XML 在许多方面存在缺陷，这就是为什么没有简单粗俗的答案的原因:

DECLARE @xml XML=
'<general>
   <group id="0" comment="Application">
      <N1 comment="Start Date">2020-11-03T00:05:48Z</N1>
      <N2 comment="Name/Description">ProgramName</N2>
      <N3 comment="Version Number">ReleaseNumber</N3>
      <N5 comment="Compilation Date">2020-10-01T01:05:01Z</N5>
      <N6 comment="Up Time">1899-12-30T00:00:56Z</N6>
   </group>
   <group id="1" comment="Exception">
      <N1 comment="Date">Tue, 3 Nov 2020 11:06:45 +1100</N1>
      <N2 comment="Address">MemoryAddress</N2>
      <N3 comment="Module Name">ModuleName</N3>
      <N4 comment="Module Version">ModuleVersionNumber</N4>
      <N5 comment="Type">ExceptionType</N5>
      <N6 comment="Message">Insufficient memory for this operation.</N6>
      <N7 comment="ID">ExceptionID</N7>
      <N8 comment="Count">1</N8>
      <N9 comment="Status">New</N9>
      <N11 comment="Sent">0</N11>
   </group>
</general>';

--这会得到一个列表，您可能会写入临时表并从那里继续

SELECT A.gr.value('@id','int') groupId
      ,A.gr.value('@comment','nvarchar(max)') groupComment
      ,B.nd.value('@comment','nvarchar(max)') NComment
      ,B.nd.value('text()[1]','nvarchar(max)') NContent
FROM @xml.nodes('/general/group') A(gr)
OUTER APPLY A.gr.nodes('*') B(nd);

--这会尝试以表格格式获取您的 EAV 数据

SELECT A.gr.value('@id','int') groupId
      ,A.gr.value('@comment','nvarchar(max)') groupComment
      ,A.gr.value('(*[@comment="Compilation Date"])[1]','datetime') NCompilationDate
      ,A.gr.value('(*[@comment="Date"])[1]','nvarchar(max)') NDate
      ,A.gr.value('(*[@comment="Count"])[1]','int') NCount
FROM @xml.nodes('/general/group') A(gr);

为什么您的 XML 有缺陷:

您不应命名数字元素(N1、N2、N3...)。它们都应该具有相同的名称。如果确实需要数字，请添加属性( nmbr="1" )。

您正在混合日期时间格式。在 XML 中，您应该只使用 ISO8601(因为它在您的第一组中)。最坏的情况是文化 和语言 依赖内容。在我的(德国)系统中，星期二的“星期二”将打破这一点。

我的建议是:
使用我的第二种方法，但为每种类型的组创建一个查询，并将它们分别读入具有给定类型列集的临时表中，然后继续执行此操作。