sql - 从 XML 列中获取所有值

Question

我有一个 COTS 应用程序，它有一个审计表，其中有一个 XML 格式的列。我正在尝试解析所有数据，以便编写面向客户的 SSRS 报告。应用程序在系统保存时将原始数据和更改后的值写入 XML 列。这意味着该列可能包含一个或多个值，并且可能是修改的多种数据类型之一。

我想以一个查询结束，该查询将显示更改了哪些数据以及更改了什么。

1. 我能否编写一个查询来返回列中存在的所有值，以便在最坏的情况下我可以明确写出到目前为止发生的每个值？

2. 是否可以让它在识别数据是什么的同时动态拉取数据？

示例 XML 条目:

<LogMessage>
   <Fields>
      <TransactionCount />
      <PersonnelType>
          <OldValue> Contractor </OldValue>
          <NewValue> Employee </NewValue>
      </PersonnelType>
      <Disabled>
          <OldValue> TRUE </OldValue>
          <NewValue> FALSE </NewValue>
      </Disabled>
      <Expiration>
          <OldValue> 10/31/2018</OldValue>
          <NewValue> 12/31/2019 </NewValue>
      </Expiration>
   </Fields>
</LogMessage>

上面的交易计数仅表示它已更改但未被跟踪。所以我一直在做一个 CASE 语句，如果它存在则返回一个值，如果不存在则将其保留为 NULL。在我查看的所有条目中，旧值总是在新值之前。

每个单元格可以有一个或多个条目，并且无法提前知道它们是什么。

我开始沿着路径查看单元格，然后调用每个可能的实例来提取数据。

XML.value('(LogMessage/Fields/Disabled/OldValue)[1]','varchar(5)') AS 'Old_Disabled'
XML.value('(LogMessage/Fields/Disabled/NewValue)[1]','varchar(5)') AS 'New_Disabled'

然后我将尝试使用 TSQL 在非空时执行某种串联逻辑。

我使用了这段返回所有值的代码，但由于它去除了所有标签信息，我无法确定发生了什么变化

XML.value('(LogMessage/Fields)[1]','varchar(max)') AS 'Raw_Data'

以上针对示例的字符串将返回此(所有值，无空格，不指示值代表什么):ContractorEmployeeTRUEFALSE10/31/201812/31/2019

如果有一种方法可以修改上面的代码以返回与此类似的东西，那就太好了，但它需要是动态的。

PersonnelType 旧:承包商 新:员工

禁用旧:真新:假

过期旧的:10/31/2018 新的:12/31/2019

即使这样也很棒:

人员类型 Contractor, Employee

禁用 是的，是的

2018 年 10 月 31 日、2019 年 12 月 31 日到期

Answer 1

您没有明确说明您的预期输出，但这似乎很容易。 XML 可以很好地处理通用结构:

DECLARE @xml XML=
'<LogMessage>
   <Fields>
      <TransactionCount />
      <PersonnelType>
          <OldValue> Contractor </OldValue>
          <NewValue> Employee </NewValue>
      </PersonnelType>
      <Disabled>
          <OldValue> TRUE </OldValue>
          <NewValue> FALSE </NewValue>
      </Disabled>
      <Expiration>
          <OldValue> 10/31/2018</OldValue>
          <NewValue> 12/31/2019 </NewValue>
      </Expiration>
   </Fields>
</LogMessage>';

--查询将使用.nodes()路径为 /* .
--这将返回 <Fields> 下面的所有元素, 但是它们被命名为
-- 查询将返回元素的名称 ( local-name(.) ) 以及用于旧值和新值的两个嵌套元素:

SELECT fld.value('local-name(.)','nvarchar(max)') AS FieldName
      ,fld.value('(OldValue/text())[1]','nvarchar(max)') AS OldValue
      ,fld.value('(NewValue/text())[1]','nvarchar(max)') AS NewValue
FROM @xml.nodes('/LogMessage/Fields/*') A(fld);

结果

FieldName           OldValue    NewValue
-----------------------------------------
TransactionCount    NULL        NULL
PersonnelType       Contractor  Employee 
Disabled            TRUE        FALSE 
Expiration          10/31/2018  12/31/2019 

更新

与表的列相同:

DECLARE @mockup TABLE(ID INT IDENTITY,YourXml XML)
INSERT INTO @mockup VALUES
('<LogMessage>
   <Fields>
      <TransactionCount />
      <PersonnelType>
          <OldValue> Contractor </OldValue>
          <NewValue> Employee </NewValue>
      </PersonnelType>
      <Disabled>
          <OldValue> TRUE </OldValue>
          <NewValue> FALSE </NewValue>
      </Disabled>
      <Expiration>
          <OldValue> 10/31/2018</OldValue>
          <NewValue> 12/31/2019 </NewValue>
      </Expiration>
   </Fields>
</LogMessage>');

SELECT fld.value('local-name(.)','nvarchar(max)') AS FieldName
      ,fld.value('(OldValue/text())[1]','nvarchar(max)') AS OldValue
      ,fld.value('(NewValue/text())[1]','nvarchar(max)') AS NewValue
FROM @mockup m
OUTER APPLY m.YourXml.nodes('/LogMessage/Fields/*') A(fld)