Блог

Мне было поручено создать service broker, использующий 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:. Разве это невозможно сделать? Я хотел бы иметь возможность выполнять это динамически, поскольку существует множество таблиц, в которых нам нужно «проверять» изменения.

Правка 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()'
  

Ваше выражение XQuery:

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

Он вызовет sql:variable() функцию расширения для каждого /Update/TableName элемента.

Если вы хотите выбрать TableName дочерний элемент с тем же именем, что и строковый результат вашей функции расширения, затем используйте:

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

Предыдущий ответ вообще не помог, но вот то, что я нашел, чтобы работать в этой ситуации. Триггер будет передаваться в виде 4 XML-строк. Первый содержит информацию о столбце, следующие два представляют собой 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 вставляется в постоянную таблицу (которая теперь находится на отдельном томе диска от остальных таблиц в этой базе данных).