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

I am using a table with an XML data field to store the audit trails of all other tables in the database.

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

That means the same XML field has various XML information. For example my table has two records with XML data like this:

这意味着相同的XML字段具有各种XML信息。例如,我的表有两条带有XML数据的记录,如下所示:

1^st record:

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

2^nd record:

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

These are the two sample formats and just two records. The table actually has many more XML formats in the same field and many records in each format.

这是两种样本格式,只有两种记录。该表实际上在同一字段中有更多XML格式,每种格式都有许多记录。

Now my problem is that upon query I need these XML formats to be converted into tabular result sets.

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

What are the options for me? It would be a regular task to query this table and generate reports from it. I want to create a stored procedure to which I can pass that I need to query "<emp>" or "<client>", then my stored procedure should return tabular data.

我有什么选择?查询此表并从中生成报告将是一项常规任务。我想创建一个我可以传递的存储过程,我需要查询“ ”或“ ”,然后我的存储过程应该返回表格数据。

4 个解决方案

#1

does this help?

这有帮助吗?

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
*/

#2

Neither xyz432-54-4231 nor abc5000 is valid XML.

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

You can try to select only one particular format with a like statement, f.e.:

您可以尝试仅使用类似语句选择一种特定格式,例如:

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

This would match 3 letters followed by 4 numbers.

这将匹配3个字母,然后是4个数字。

A better option is probably to add an extra column to the table, where you save the type of the logging. Then you can use that column to select all "emp" or "client" rows.

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

#3

An option would be to create a series of views that present the aduit table, per type in the relations that you're execpting for example

一个选项是创建一系列视图来呈现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)

you could then follow the same pattern for the emp

然后你可以按照相同的模式为emp

you could also create a view (or computed column) to identify each xml type like this:

您还可以创建一个视图(或计算列)来标识每个xml类型,如下所示:

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

#4

Use open xml method

使用open xml方法

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

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

SELECT * into #test
FROM OPENXML (@idoc, 'xmlfilepath',2)
WITH (Name varchar(50),ssn varchar(20)
)

SELECT * into #test FROM OPENXML(@idoc,'xmlfilepath',2)WITH(Name varchar(50),ssn varchar(20))

EXEC sp_xml_removedocument @idoc

after you get the data in the #test and you can manipulate this.

在#test中获取数据后,您可以操作它。

you may be put the diff data in diff xml file.

你可能会将diff数据放在diff xml文件中。

#1