RE: XML query – SQLServerCentral

One Orange Chip

Points: 26944

January 22, 2013 at 8:52 am

DECLARE @x XML ='<MatchKeys>

<fuzzyKeys>

<key key1="MatchKey_PostOut" key2="MatchKey_Name1" />

<key key1="MatchKey_Name1" key2="MatchKey_PhoneticStreet" key3="MatchKey_PhoneticCompanyName"/>

<key key1="MatchKey_PostOut" key2="MatchKey_PostIn" />

</fuzzyKeys>

</MatchKeys>';

SELECT DENSE_RANK() OVER(ORDER BY x1.r1) AS MatchKey_Group,

ROW_NUMBER() OVER(PARTITION BY x1.r1 ORDER BY x2.r2) AS MatchKey_ID,

x2.r2.value('.','VARCHAR(30)') AS KeyColumn

FROM @x.nodes('/MatchKeys/fuzzyKeys/key') AS x1(r1)

CROSS APPLY x1.r1.nodes('@*') AS x2(r2);

____________________________________________________

Deja View - The strange feeling that somewhere, sometime you've optimised this query before

How to get the best help on a forum