在开发SQL Server单元测试(ssut-请参阅相关博客文章)的过程中,我希望对来自测试对象的xml集进行标准化。由于我将多次调用测试对象,每次集合和记录的名称都是相同的。为了便于阅读,我希望将原始记录中的记录集命名为
<original_record_set><original_record /></original_record_set>
创下了
试验记录命名类似
<test_record_set><test_record /></ test_record_set >
显然,如果您可以将测试对象中的调用修改为第一个调用,那么这样做很简单:
SET @output = (SELECT col1, col2
FROM @test_object_result
FOR xml path ( test_record '), root( test_record_set '));
然后:
SET @output = (SELECT col1, col2
FROM @test_object_result
FOR xml path ( original_record'), root( original_record_set '));
但是,由于我多次调用同一对象,并且“for xml path”不允许
path('...')
和
root('...')
此函数接受一个xml树并构建一个新树,用值替换根节点
@relation_name
@tuple_name
. 新树是用原始树的所有属性构建的,即使每个记录有不同的数字。
例外情况
显然,这不适用于多个元素级别!我专门构建了它来处理一个基于单层属性的树,如下面的示例所示。我可能会在将来为多层混合属性/元素树构建它,但是我认为这样做的方法变得很明显,因为我已经解决了下面的基本问题,并且将把这个练习留给读者等待。
USE [unit_test];
GO
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[standardize_record_set]') AND type IN ( N'FN', N'IF', N'TF', N'FS', N'FT' ))
DROP FUNCTION [dbo].[standardize_record_set];
GO
SET ANSI_NULLS ON;
GO
SET QUOTED_IDENTIFIER ON;
GO
SET nocount ON;
GO
/*
DECLARE
@relation_name nvarchar(150)= N'standardized_record_set',
@tuple_name nvarchar(150)= N'standardized_record',
@xml xml,
@standardized_result xml;
SET @xml='<Root>
<row id="12" two="now1" three="thr1" four="four1" />
<row id="232" two="now22" three="thr22" />
<row id="233" two="now23" three="thr23" threeextra="extraattrinthree" />
<row id="234" two="now24" three="thr24" fourextra="mealsoin four rwo big mone" />
<row id="235" two="now25" three="thr25" />
</Root>';
execute @standardized_result = [dbo].[standardize_record_set] @relation_name=@relation_name, @tuple_name=@tuple_name, @xml=@xml;
select @standardized_result;
*/
CREATE FUNCTION [dbo].[standardize_record_set] (@relation_name nvarchar(150)= N'record_set',
@tuple_name nvarchar(150)= N'record', @xml xml )
returns XML
AS
BEGIN
DECLARE
@attribute_index int = 1,
@attribute_count int = 0,
@record_set xml = N'<' + @relation_name + ' />',
@record_name nvarchar(50) = @tuple_name,
@builder nvarchar(max),
@record xml,
@next_record xml;
DECLARE @record_table TABLE (
record xml );
INSERT INTO @record_table
SELECT t.c.query('.') AS record
FROM @xml.nodes('/*/*') T(c);
DECLARE record_table_cursor CURSOR FOR
SELECT cast([record] AS xml)
FROM @record_table
OPEN record_table_cursor
FETCH NEXT FROM record_table_cursor INTO @next_record
WHILE @@FETCH_STATUS = 0
BEGIN
SET @attribute_index=1;
SET @attribute_count = @next_record.query('count(/*[1]/@*)').value('.', 'int');
SET @builder = N'<' + @record_name + N' ';
-- build up attribute string
WHILE @attribute_index <= @attribute_count
BEGIN
SET @builder = @builder + @next_record.value('local-name((/*/@*[sql:variable("@attribute_index")])[1])',
'varchar(max)') + '="' + @next_record.value('((/*/@*[sql:variable("@attribute_index")])[1])',
'varchar(max)') + '" ';
SET @attribute_index = @attribute_index + 1
END
-- build record and add to record_set
SET @record = @builder + ' />';
SET @record_set.modify('insert sql:variable("@record") into (/*)[1]');
FETCH NEXT FROM record_table_cursor INTO @next_record
END
CLOSE record_table_cursor;
DEALLOCATE record_table_cursor;
RETURN @record_set;
END;
GO
