从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语句，如果存在则返回一个值，否则将其保留为空。在我查看的所有条目中，旧值总是在新值之前。

每个单元格可以有一个或多个条目，事先无法确定它们将是什么。

我开始检查单元格并调用每个可能的实例来提取数据。

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'

对于上述的例子，以下是其返回结果（所有值无间隔，没有表明值代表什么的信息）：承包员工真假10/31/201812/31/2019

如果有一种方法可以修改上述代码，以便返回类似于以下内容的东西，那就太棒了，但它需要是动态的。

人事类型旧值：承包商 新值：员工

禁用旧值：真 新值：假

过期旧值：10/31/2018 新值：12/31/2019

甚至这个也很棒：

人事类型 承包商，员工

禁用 真，是

过期 10/31/2018，12/31/2019

- CPickler

---

新的 Disabled 的值不是 Yes，而是 FALSE，对吗？ - HABO

你说得对，应该是FALSE。我会更新它。那只是我的笔误。谢谢你指出来。 - CPickler

Answer 1

我曾经使用过OPENXML，请检查是否适合。

DECLARE @InputXml xml;
set @InputXml ='<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 @InputXml

declare @idoc int
exec sp_xml_preparedocument @idoc out, @InputXml

select FieldName, 
       replace(FieldValue, '  ', ', ') as value
from openxml(@idoc, '/LogMessage/Fields/*',2) 
  with (
         FieldName  varchar(50) '@mp:localname',
         FieldValue varchar(50) '.'
       )

exec sp_xml_removedocument @idoc

RexTesterDEMO

- Mani Deep

---

这看起来很不错。虽然我对OpenXML不是非常熟悉，但在RexTester中看起来很不错。有没有办法让变量动态地提取将出现的值，而不需要硬编码？XML条目可能有任意数量的条目，我想要一些动态解析值的东西。我知道你的示例中必须声明某些内容，但我能否将@InputXML变量设置为dbname.AuditView.XML之类的内容？XML列只是视图中的众多列之一。 - CPickler

FROM OPENXML 与其对应的存储过程用于准备和删除文档已经过时，不应再使用。相反，应该使用适当的 XML 数据类型提供的方法。这些方法可以在临时查询中针对表列进行操作，而这种方法需要使用一组过程性步骤。 - Shnugo

Answer 2

您没有清楚地说明您期望的输出，但这似乎相当容易。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)

- Shnugo

---

那个非常有效。谢谢。有没有办法将结果与它们来自的条目联系起来？例如，我可能正在运行一个回溯4个月的审计报告，因此我有10个条目针对我。这会在一个表格中显示它们所有。所以如果我有条目1，其中包含人员类型、禁用和到期值，以及条目2，其中包含名称、时间表、薪资等。 - CPickler

@CPickler，请使用我的模拟场景，并将类似的内容放入你的问题中。提供一些真实的XML和预期输出。 - Shnugo

没事了，我意识到我仍然可以像往常一样在这个选择语句中调用其他列。 - CPickler

Answer 3

我为你提供以下关于表格更新追踪的答案。你可以编写一个触发器来动态地追踪表格的更新或更改。下面是该SQL Server触发器的代码：

CREATE TRIGGER [dbo].[TR_Employee_AUDIT] ON [dbo].[Employee_mstr] FOR UPDATE
AS

DECLARE @bit INT ,
       @field INT ,
       @maxfield INT ,
       @char INT ,
       @fieldname VARCHAR(128) ,
       @TableName VARCHAR(128) ,
       @PKCols VARCHAR(1000) ,
       @sql VARCHAR(2000), 
       @UpdateDate VARCHAR(21) ,
       @UserName VARCHAR(128) ,
       @Type CHAR(1) ,
       @PKSelect VARCHAR(1000),
       @empcode VARCHAR(20)


--You will need to change @TableName to match the table to be audited. 
-- Here we made GUESTS for your example.
SELECT @TableName = 'Employee_Mstr'

-- date and user
SELECT         @UserName = SYSTEM_USER ,
       @UpdateDate = CONVERT (NVARCHAR(30),GETDATE(),126)

-- Action
IF EXISTS (SELECT * FROM inserted)
       IF EXISTS (SELECT * FROM deleted)
               SELECT @Type = 'U'
       ELSE
               SELECT @Type = 'I'
ELSE
       SELECT @Type = 'D'

-- get list of columns
SELECT * INTO #ins FROM inserted
SELECT * INTO #del FROM deleted

select @UserName = EMP_ModifiedBy, @empcode = emp_cd from #ins
if isnull(@UserName,'') = ''
select @UserName = EMP_ModifiedBy, @empcode = emp_cd from #del

-- Get primary key columns for full outer join
SELECT @PKCols = COALESCE(@PKCols + ' and', ' on') 
               + ' i.' + c.COLUMN_NAME + ' = d.' + c.COLUMN_NAME
       FROM    INFORMATION_SCHEMA.TABLE_CONSTRAINTS pk ,

              INFORMATION_SCHEMA.KEY_COLUMN_USAGE c
       WHERE   pk.TABLE_NAME = @TableName
       AND     CONSTRAINT_TYPE = 'PRIMARY KEY'
       AND     c.TABLE_NAME = pk.TABLE_NAME
       AND     c.CONSTRAINT_NAME = pk.CONSTRAINT_NAME

-- Get primary key select for insert
SELECT @PKSelect = COALESCE(@PKSelect+'+','') 
       + '''<' + COLUMN_NAME 
       + '=''+convert(varchar(100),
coalesce(i.' + COLUMN_NAME +',d.' + COLUMN_NAME + '))+''>''' 
       FROM    INFORMATION_SCHEMA.TABLE_CONSTRAINTS pk ,
               INFORMATION_SCHEMA.KEY_COLUMN_USAGE c
       WHERE   pk.TABLE_NAME = @TableName
       AND     CONSTRAINT_TYPE = 'PRIMARY KEY'
       AND     c.TABLE_NAME = pk.TABLE_NAME
       AND     c.CONSTRAINT_NAME = pk.CONSTRAINT_NAME

IF @PKCols IS NULL
BEGIN
       RAISERROR('no PK on table %s', 16, -1, @TableName)
       RETURN
END


SELECT         @field = 0, 
       @maxfield = MAX(ORDINAL_POSITION) 
       FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = @TableName
       AND COLUMN_NAME NOT IN ('EMP_ModifiedOn','EMP_ModifiedBy')
WHILE @field < @maxfield
BEGIN
       SELECT @field = MIN(ORDINAL_POSITION) 
               FROM INFORMATION_SCHEMA.COLUMNS 
               WHERE TABLE_NAME = @TableName 
               AND ORDINAL_POSITION > @field
               AND COLUMN_NAME NOT IN ('EMP_ModifiedOn','EMP_ModifiedBy')
       SELECT @bit = (@field - 1 )% 8 + 1
       SELECT @bit = POWER(2,@bit - 1)
       SELECT @char = ((@field - 1) / 8) + 1
       IF SUBSTRING(COLUMNS_UPDATED(),@char, 1) & @bit > 0
                                       OR @Type IN ('I','D')
       BEGIN
               SELECT @fieldname = COLUMN_NAME 
                       FROM INFORMATION_SCHEMA.COLUMNS 
                       WHERE TABLE_NAME = @TableName 
                       AND ORDINAL_POSITION = @field
                       AND COLUMN_NAME NOT IN ('EMP_ModifiedOn','EMP_ModifiedBy')
               SELECT @sql = '
insert NewAuditLog (    Type, 
               TableName, 
               PK, 
               FieldName, 
               OldValue, 
               NewValue, 
               UpdateDate, 
               UserName)
select ''' + @Type + ''',''' 
       + @TableName + ''',''' + @empcode + ''',''' + @fieldname + ''''
       + ',convert(varchar(1000),d.' + @fieldname + ')'
       + ',convert(varchar(1000),i.' + @fieldname + ')'
       + ',''' + @UpdateDate + ''''
       + ',''' + @UserName + ''''
       + ' from #ins i full outer join #del d'
       + @PKCols
       + ' where i.' + @fieldname + ' <> d.' + @fieldname 
       + ' or (i.' + @fieldname + ' is null and  d.'
                                + @fieldname
                                + ' is not null)' 
       + ' or (i.' + @fieldname + ' is not null and  d.' 
                                + @fieldname
                                + ' is null)' 
               EXEC (@sql)
       END
END

你需要创建一个表格来存储如下所示的更改值。

CREATE TABLE [dbo].[NewAuditLog](
    [Type] [char](1) NULL,
    [TableName] [varchar](128) NULL,
    [PK] [varchar](1000) NULL,
    [FieldName] [varchar](128) NULL,
    [OldValue] [varchar](1000) NULL,
    [NewValue] [varchar](1000) NULL,
    [UpdateDate] [datetime] NULL,
    [UserName] [varchar](128) NULL
) ON [PRIMARY]

一旦表格中的数据被更改，输出结果将会存储如下所示。

- Suraj Kumar

---

非常感谢您提供的这个信息丰富的回复。不幸的是，对于我的目的来说，更改涉及到数百个表格，并已经被加载到一个具有大量列的单个审计点中。它们只使用XML，我正在尝试找出更好的解析所有现有或可能存在的数据的方法。然而，我可能会在另一个项目中使用这些信息。谢谢。 - CPickler