xquery 循环遍历列

Question

我的任务是使用 Xquery 创建一个服务代理来处理对表集合的跟踪更改。我已经弄清楚如何传递消息（列名的 xml 以及语句的更新和删除表）。目的是获取列名称列表，然后比较每个更新/删除的行而不是更改的类似列。

下面是 XML 的示例：

<Update xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
  <TableName>
    <ID>2414</ID>
    <fkEvent>2664</fkEvent>
    <fkType xsi:nil="true" />
    <Description>Phil Test 3</Description>
    <DTS>2011-04-04T14:01:36.533</DTS>
    <uID>192204FA-612F-46F4-A6CB-1B4D53769A81</uID>
    <VersionID xsi:nil="true" />
    <UpdateDateTime>2011-04-04T14:04:31.013</UpdateDateTime>
    <DeleteFlag>0</DeleteFlag>
    <Updated>0</Updated>
    <Owner>42</Owner>
    <CreatedBy>42</CreatedBy>
  </TableName>
</Update>

生成者：

SET @xml1 = (SELECT * FROM TableName ORDER BY ID DESC FOR XML AUTO, ELEMENTS XSINIL, ROOT('MsgEnv'))

我有以下代码：

WHILE @cnt <= @totCnt BEGIN
SELECT @child = @ColNames.query('/Columns/name[position()=sql:variable("@cnt")]')
SET @CurrentCol = REPLACE(REPLACE(CAST(@child AS VARCHAR(500)), '<name>', ''), '</name>', '')
PRINT @CurrentCol
WHILE @updateCnt <= @updateCntTotal BEGIN
    SELECT @childUpdate = @xml1.query('/Update/TableName/sql:variable("@CurrentCol")')
    PRINT CAST(@childUpdate AS VARCHAR(MAX))

    WHILE @deleteCnt <= @deleteCntTotal BEGIN
        SELECT @deleteCnt = @deleteCnt + 1
    END

    SET @deleteCnt = 1
    SELECT @updateCnt = @updateCnt + 1
END

SET @updateCnt = 1
SELECT @cnt = @cnt + 1
END

我遇到的问题是动态设置此语句的列名：

SELECT @childUpdate = @xml1.query('/Update/TableName/sql:variable("@CurrentCol")')

我使用 sql:variable 尝试了几种不同的变体。难道不能这样做吗？我希望能够动态地执行此操作，因为我们需要“审核”许多表的更改。

编辑 1：

SELECT @childUpdate = @xml1.query('/Update/TableName/*[name() = sql:variable("@CurrentCol")]')

产生此错误（包括 () 中的 . 具有类似的效果。

Msg 2395, Level 16, State 1, Line 34
XQuery [query()]: There is no function '{http://www.w3.org/2004/07/xpath-functions}:name()'

Answer 1

您的 XQuery 表达式：

/Update/TableName/sql:variable("@CurrentCol")

它将为每个 /Update/TableName 元素调用 sql:variable() 扩展函数。

如果您想选择 TableName 的子级与扩展函数的字符串结果同名，请使用：

/Update/TableName/*[name(.) = sql:variable("@CurrentCol")]

Answer 2

前面的答案根本没有帮助，但这是我发现适用于这种情况的方法。触发器将传入 4 个 XML 字符串。第一个包含列信息，接下来两个是 INSERTED 和 DELETED 临时表的 XML 内容，最后一个是元字符串（架构名称、表名称、用户更新、时间戳等）。

列 XML 代码如下所示：

DECLARE @ColNames XML
DECLARE @ColumnTypeInfo TABLE (
column_name varchar(100),
data_type varchar(100))

INSERT INTO @ColumnTypeInfo (column_name,data_type)
(
SELECT column_name 'column_name',
    CASE WHEN
        DATA_TYPE = 'datetime' OR DATA_TYPE = 'int' OR DATA_TYPE = 'bit' OR 
        DATA_TYPE = 'uniqueidentifier' OR DATA_TYPE = 'sql_variant'
    THEN DATA_TYPE ELSE
        CASE WHEN CHARACTER_MAXIMUM_LENGTH IS NOT NULL THEN
            data_type + '(' + 
                CAST(CHARACTER_MAXIMUM_LENGTH AS VARCHAR(10))
            + ')'
        ELSE
            CASE WHEN NUMERIC_PRECISION IS NOT NULL AND NUMERIC_SCALE IS NOT NULL THEN
                data_type + '(' +
                    CAST(NUMERIC_PRECISION AS VARCHAR(10))
                        + ',' + 
                    CAST(NUMERIC_SCALE AS VARCHAR(10))
                + ')'
            ELSE
            DATA_TYPE
            END
        END
    END 'data_type'
FROM information_schema.columns WHERE table_name = 'tbl_ActivityPart'
)

SET @ColNames = (
    SELECT * FROM @ColumnTypeInfo 
    FOR XML PATH ('Column'), ROOT('ColumnDef')
)

@ColNames 被传递到消息队列中。

这是处理排队消息的过程的基础：

WHILE @cnt <= @totCnt BEGIN
    SET @CurrentCol = CAST(@ColNames.query('for $b in /ColumnDef/Column[position()=sql:variable("@cnt")]/column_name return ($b)') AS VARCHAR(MAX))
    SET @CurrentCol = REPLACE(REPLACE(@CurrentCol, '<column_name>', ''), '</column_name>', '')

    SET @DataType = CAST(@ColNames.query('for $b in /ColumnDef/Column[position()=sql:variable("@cnt")]/data_type return ($b)') AS VARCHAR(MAX))
    SET @DataType = REPLACE(REPLACE(@DataType, '<data_type>', ''), '</data_type>', '')  

    SET @updateQuery = '/Update/Scheme.TableName/'+@CurrentCol
    SET @SQL = 'SELECT @TmpXML = @UpdatedXML.query(''' + @updateQuery + ''')'
    EXEC sp_executesql @SQL, N'@UpdatedXML xml, @TmpXML XML output', @UpdatedXML, @TmpXML output
    SET @childUpdate = @TmpXML

    SET @NewValue = REPLACE(REPLACE(CAST(@childUpdate AS VARCHAR(8000)), '<'+@CurrentCol+'>', ''), '</'+@CurrentCol+'>', '')
        IF (CHARINDEX('xsi:nil="true"', CONVERT(VARCHAR(8000), @NewValue)) <> 0) BEGIN
            SET @NewValue = NULL
        END

    SET @deleteQuery = '/Delete/Scheme.TableName/'+@CurrentCol
    SET @SQL = 'SELECT @TmpXML = @DeletedXML.query(''' + @deleteQuery + ''')'
    EXEC sp_executesql @SQL, N'@DeletedXML xml, @TmpXML XML output', @DeletedXML, @TmpXML output
    SET @childDelete = @TmpXML

    SET @OldValue = REPLACE(REPLACE(CAST(@childDelete AS VARCHAR(8000)), '<'+@CurrentCol+'>', ''), '</'+@CurrentCol+'>', '')
        IF (CHARINDEX('xsi:nil="true"', CONVERT(VARCHAR(8000), @OldValue)) <> 0) BEGIN
            SET @OldValue = NULL
        END

    IF @NewValue <> @OldValue BEGIN
        INSERT INTO @Changes (SchemaName, TableName, FieldName, DTS, 
            [uID], OldValue, NewValue, ValueDataType, [User]) 
            SELECT @Schema, @TableName, @CurrentCol, @TimeStamp, 
                CONVERT(UNIQUEIDENTIFIER, @CurrentUID), @OldValue, @NewValue, @DataType, @UpdateUserID  
    END
    -- **********************************************************************************************************

    SELECT @cnt = @cnt + 1
END

然后将 @Changes 的内容插入到永久表中（该表现在位于与该数据库中的其余表不同的磁盘卷上）。