使用 T-SQL 切碎 XML - 从组内部提取行值的正确语法是什么？

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>

问题是，我不知道如何查询组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中查询值的魔力吗？我怀疑答案在Contains中，但我在找到编写代码教程以使指令在我的脑海中快速到位时遇到了问题。(这很复杂，因为我想在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在很多方面都有缺陷，这就是为什么没有简单的cheesy答案的原因：

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(就像在第一组中一样(。最糟糕的情况是依赖文化和语言的内容。在我的(德语(系统中；"星期二"；周二将打破这一局面

我的建议是：

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

Answer 2

好的，所以我已经找到了一个可能的解决方案。

如果我表达这个问题的方式对其他人有帮助，我的解决方案是：

,LEFT(@X.value('(/log/general/group[1]/N6)[1]','VARCHAR(50)') ,50) --Find first GROUP, returend first result for N6
,LEFT(@X.value('(/log/general/group[@id="0"]/N6)[1]','VARCHAR(50)') ,50) --Find group with ID=0, return first result for N6
,LEFT(@X.value('(/log/general/group[@id="1"]/N6)[1]','VARCHAR(50)') ,50) --Find group with ID=1, reture first result for N6
,LEFT(@X.value('(/log/general/group[2]/N2)[1]','VARCHAR(50)') ,50) --Find second GROUP, returend first result for N6

注意，红门简单对话的大量信息，他的文章最终为我点击。https://www.red-gate.com/simple-talk/sql/learn-sql-server/the-xml-methods-in-sql-server/

超级渴望看到其他更好的解决方案，如果有人有。