question

Here is one scenario. Suppose you modify the xml and then you want to update data in the underlying table based on changes in xml. Then you can treat xml as a normal rowset, meaning that you can join it with your underlying table based on the key column and update the table from that join. Here is a complete example:

-- the first part is simply a copy of your xml in your 
-- question declared as xml data type:

declare @xml xml;
set @xml = '
    <abc.ccc.BR_MTR_IMPORT 
    MTR_KEY="xxx-xxx-xxxx" 
    TRAN_DATE="2010-06-06T12:00:00"     
    VERSION="2" 
    FILE_ID="1660" 
    ROW_STATE="2.75" 
    PROCESS_STATE="1.34" 
    LST_MODIFIED_DT="2010-06-11T16:40:18.553" 
    LST_MODIFIED_USER="qwer" />
    <abc.ccc.BR_MTR_IMPORT 
    MTR_KEY="zzz-zzz-zzzz" 
    TRAN_DATE="2010-06-06T12:00:00" VERSION="3" 
    FILE_ID="1660" 
    ROW_STATE="2.75" 
    PROCESS_STATE="1.34" 
    LST_MODIFIED_DT="2010-06-11T16:43:19.360" 
    LST_MODIFIED_USER="qwer" />
    <abc.ccc.BR_MTR_IMPORT 
    MTR_KEY="nnn-nnn-nnnn" 
    TRAN_DATE="2010-06-06T12:00:00" 
    VERSION="4" 
    FILE_ID="1660" ROW_STATE="2.75" 
    PROCESS_STATE="1.34" 
    LST_MODIFIED_DT="2010-06-11T16:46:20.217" 
    LST_MODIFIED_USER="qwer" />
    <abc.ccc.BR_MTR_IMPORT 
    MTR_KEY="hhh-hhh-hhhh" 
    TRAN_DATE="2010-06-06T12:00:00" 
    VERSION="5" 
    FILE_ID="1660" 
    ROW_STATE="2.75" 
    PROCESS_STATE="1.34" 
    LST_MODIFIED_DT="2010-06-11T16:52:20.973" 
    LST_MODIFIED_USER="qwer" />
    <abc.ccc.BR_MTR_IMPORT 
    MTR_KEY="fff-fff-ffff" 
    TRAN_DATE="2010-06-06T12:00:00" 
    VERSION="1" 
    FILE_ID="1660" 
    ROW_STATE="2.75" 
    PROCESS_STATE="1.34" 
    LST_MODIFIED_DT="2010-06-11T16:37:17.450" 
    LST_MODIFIED_USER="qwer" />';

-- now if you want to select data from above xml you can do this:

    select 
        item.value('@MTR_KEY', 'varchar(50)') MTR_KEY,
        item.value('@TRAN_DATE', 'datetime') TRAN_DATE,
        item.value('@VERSION', 'varchar(50)') VERSION,
        item.value('@FILE_ID', 'int') [FILE_ID],
        item.value('@ROW_STATE', 'decimal(10, 2)') ROW_STATE,
        item.value('@PROCESS_STATE', 'decimal(10, 2)') PROCESS_STATE,
        item.value('@LST_MODIFIED_DT', 'datetime') LST_MODIFIED_DT,
        item.value('@LST_MODIFIED_USER', 'varchar(50)') LST_MODIFIED_USER
        from @xml.nodes('abc.ccc.BR_MTR_IMPORT') r(item);

-- and if you want to update your table from the xml above, you can do this:

with records
(
    MTR_KEY, TRAN_DATE, VERSION, [FILE_ID], ROW_STATE, 
    PROCESS_STATE, LST_MODIFIED_DT, LST_MODIFIED_USER
) as
(
    select 
        item.value('@MTR_KEY', 'varchar(50)') MTR_KEY,
        item.value('@TRAN_DATE', 'datetime') TRAN_DATE,
        item.value('@VERSION', 'varchar(50)') VERSION,
        item.value('@FILE_ID', 'int') [FILE_ID],
        item.value('@ROW_STATE', 'decimal(10, 2)') ROW_STATE,
        item.value('@PROCESS_STATE', 'decimal(10, 2)') PROCESS_STATE,
        item.value('@LST_MODIFIED_DT', 'datetime') LST_MODIFIED_DT,
        item.value('@LST_MODIFIED_USER', 'varchar(50)') LST_MODIFIED_USER
        from @xml.nodes('abc.ccc.BR_MTR_IMPORT') r(item)
)
    update t
    set 
        TRAN_DATE = records.TRAN_DAT,
        VERSION = records.VERSION,
        [FILE_ID] = records.[FILE_ID],
        ROW_STATE = records.ROW_STATE,
        PROCESS_STATE = records.PROCESS_STATE,
        LST_MODIFIED_DT = records.LST_MODIFIED_DT,
        LST_MODIFIED_USER = records.LST_MODIFIED_USER
        from abc.ccc.BR_MTR_IMPORT t inner join records
            on t.MTR_KEY = records.MTR_KEY;

Hope this helps

Oleg