Well, looks like Mark beat me to it. Anyhow, this is kind of (I think) the same thing.

DECLARE @xmlData XML

SET @xmlData = ('

<MatchKeys>

<fuzzyKeys>

<key key1="1" key2="1" key3="MatchKey_PostOut" />

</fuzzyKeys>

<fuzzyKeys>

<key key1="1" key2="2" key3="MatchKey_Name1" />

</fuzzyKeys>

<fuzzyKeys>

<key key1="2" key2="1" key3="MatchKey_Name1" />

</fuzzyKeys>

<fuzzyKeys>

<key key1="2" key2="2" key3="MatchKey_PhoneticStreet" />

</fuzzyKeys>

<fuzzyKeys>

<key key1="2" key2="3" key3="MatchKey_PhoneticCompanyName" />

</fuzzyKeys>

<fuzzyKeys>

<key key1="3" key2="1" key3="MatchKey_PostOut" />

</fuzzyKeys>

<fuzzyKeys>

<key key1="3" key2="2" key3="MatchKey_PostIn" />

</fuzzyKeys>

</MatchKeys>

')

SELECT

x.ref.value('@key1[1]', 'int') AS MatchKey_Group,

x.ref.value('@key2[1]', 'int') AS MatchKey_ID,

x.ref.value('@key3[1]', 'varchar(50)') AS KeyColumn

FROM @xmlData.nodes('/MatchKeys/fuzzyKeys/key') AS x( ref )

Mark, what is the purpose of the DENSE_RANK(), ROW_NUMBER(), and the CROSS APPLY? This is the fist time I've messed with XML, so it may be something simple.