SQL Server：使用存储过程将 XML 字段输出为表格数据

Question

我使用带有 XML 数据字段的表来存储数据库中所有其他表的审计跟踪。

这意味着同一个 XML 字段具有不同的 XML 信息。 例如，我的表有两条带有 XML 数据的记录，如下所示：

第 1^st 记录：

<client>
  <name>xyz</name>
  <ssn>432-54-4231</ssn>
</client>

第 2^nd 记录：

<emp>
  <name>abc</name>
  <sal>5000</sal>
</emp>

这是两种示例格式，只有两条记录。 该表实际上在同一字段中具有更多 XML 格式，并且每种格式都有许多记录。

现在我的问题是，在查询时，我需要将这些 XML 格式转换为表格结果集。

我有哪些选择？ 查询该表并从中生成报告将是一项常规任务。 我想创建一个存储过程，我可以将其传递给我需要查询“”或“”的存储过程，那么我的存储过程应该返回表格数据。

Answer 1

一个选项是创建一系列视图，根据您正在执行的关系中的每种类型来显示 aduit 表
例如，

select
    c.value('name','nvarchar(50)') as name,
    c.value('ssn', 'nvarchar(20)') as ssn
from yourtable
cross apply yourxmlcolumn.nodes('/client') as t(c)

您可以对 emp 遵循相同的模式，

您还可以创建一个视图（或计算列）来识别每个 xml 类型，如下所示：

select yourxmlcolumn.value('local-name(/*[1])', 'varchar(100)') as objectType
from yourtable

Answer 2

使用开放 xml 方法

DECLARE @idoc int
EXEC sp_xml_preparedocument @idoc OUTPUT，@xmldoc

SELECT * 进入#test
FROM OPENXML (@idoc, 'xmlfilepath',2)
with (名称 varchar(50),ssn varchar(20)
)

执行 sp_xml_removedocument @idoc

在 #test 中获取数据后
你可以操纵它。

您可以将 diff 数据放入 diff xml 文件中。

Answer 3

这有帮助吗？

INSERT INTO @t (data) SELECT '
<client>
  <name>xyz</name>
  <ssn>432-54-4231</ssn>
</client>'

INSERT INTO @t (data) SELECT '
<emp>
  <name>abc</name>
  <sal>5000</sal>
</emp>'
DECLARE @el VARCHAR(20)

SELECT @el = 'client'

SELECT
    x.value('local-name(.)', 'VARCHAR(20)') AS ColumnName,
    x.value('.','VARCHAR(20)') AS ColumnValue
FROM @t
CROSS APPLY data.nodes('/*[local-name(.)=sql:variable("@el")]') a (x)
/*
ColumnName           ColumnValue
-------------------- --------------------
client               xyz432-54-4231
*/

SELECT @el = 'emp'
SELECT
    x.value('local-name(.)', 'VARCHAR(20)') AS ColumnName,
    x.value('.','VARCHAR(20)') AS ColumnValue
FROM @t
CROSS APPLY data.nodes('/*[local-name(.)=sql:variable("@el")]') a (x)
/*
ColumnName           ColumnValue
-------------------- --------------------
emp                  abc5000
*/

Answer 4

xyz432-54-4231 和 abc5000 都不是有效的 XML。

您可以尝试使用 like 语句仅选择一种特定格式，fe：

select * 
from YourTable 
where YourColumn like '[a-z][a-z][a-z][0-9][0-9][0-9][0-9]'

这将匹配 3 个字母后跟 4 个数字。

更好的选择可能是向表中添加一个额外的列，在其中保存日志记录的类型。 然后您可以使用该列来选择所有“emp”或“client”行。