xquery loop over columns

Question

I have been tasked with creating a service broker using Xquery to handle tracking changes on a collection of tables. I have figured out how to pass the messages (xml of column names and the updated and deleted tables for the statements). The aim is to get the list of column names and then compare the like column for each updated/deleted row and not a change.

Here is a sample of the 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>

Generated by:

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

I have the following code:

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

The trouble I am having is dynamically setting the column name for this statement:

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

I have tried a few different variations using the sql:variable. Is it not possible to do this? I'd like to be able to do this dynamically as there are lots of tables we need to "audit" changes on.

Edit 1:

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

Yields this error (including the . in the () has a similar effect.

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

The previous answer didn't help at all but here is what I have found to work for this situation. The trigger will pass in 4 XML strings. The first contains the column information, the next two are the XML contents of the INSERTED and DELETED temporary tables, and the last is a Meta string (schema name, table name, updated by user, timestamp, etc).

Here is what the column XML code looks like:

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 is passed into the message queue.

This is the basis for the procedure that processes the queued messages:

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

The contents of @Changes is then inserted into the permanent table (which is now on a separate disk volume from the rest of the tables in that database).

Answer 2

Your XQuery expression:

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

It will call sql:variable() extension function for each /Update/TableName element.

If you want to select TableName's child with the same name as the string result of your extension function, then use:

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