InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comFri, 24 May 2013 20:53:35 GMT20http://www.sqlservercentral.com/Forums/Topic1410042-392-1.aspxMaybe there's a middlin' easy alternative:[code="sql"]DECLARE @xmlData 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>';;WITH XML_Unveiled AS ( SELECT MatchKey_Group=ROW_NUMBER() OVER (ORDER BY (SELECT NULL)), x.ref.value('@key1[1]', 'varchar(50)') AS Key1, x.ref.value('@key2[1]', 'varchar(50)') AS Key2, x.ref.value('@key3[1]', 'varchar(50)') AS Key3 FROM @xmlData.nodes('/MatchKeys/fuzzyKeys/key') AS x( ref ))SELECT MatchKey_Group, MatchKey_ID, KeyColumnFROM XML_UnveiledCROSS APPLY ( VALUES (1, Key1),(2,Key2),(3,Key3)) a (MatchKey_ID, KeyColumn)WHERE KeyColumn IS NOT NULL ORDER BY MatchKey_Group, MatchKey_ID[/code]See the first link in my signature if you haven't seen the CROSS APPLY VALUES approach to UNPIVOT before.Tue, 22 Jan 2013 21:30:27 GMTdwain.chttp://www.sqlservercentral.com/Forums/Topic1410042-392-1.aspxThanks guys. Much appreciated!Although I find Greg's solution easier to understand which makes me wonder, should I re-format my XML?!BTW, I've no idea how Mark's solution works lol, I'm going to have spend some time to work it out.Once again, thanks for your help!Tue, 22 Jan 2013 09:16:29 GMTAbu Dinahttp://www.sqlservercentral.com/Forums/Topic1410042-392-1.aspx[quote][b]Greg Snidow[/b]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.[/quote]Okay, I see now. Abu does not have the ID columns in the XML, so you are creating them?Abu, I think you can ignore my previous post.Tue, 22 Jan 2013 09:04:34 GMTGreg Snidowhttp://www.sqlservercentral.com/Forums/Topic1410042-392-1.aspxWell, looks like Mark beat me to it. Anyhow, this is kind of (I think) the same thing.[code]DECLARE @xmlData XMLSET @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 KeyColumnFROM @xmlData.nodes('/MatchKeys/fuzzyKeys/key') AS x( ref )[/code]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.Tue, 22 Jan 2013 09:01:03 GMTGreg Snidowhttp://www.sqlservercentral.com/Forums/Topic1410042-392-1.aspx[code="sql"]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 KeyColumnFROM @x.nodes('/MatchKeys/fuzzyKeys/key') AS x1(r1)CROSS APPLY x1.r1.nodes('@*') AS x2(r2);[/code]Tue, 22 Jan 2013 08:52:25 GMTMark-101232http://www.sqlservercentral.com/Forums/Topic1410042-392-1.aspxI have the following basic XML document:[code="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>[/code]The data is stored in an XML SQL variable but now I would like to put it into a table so the data looks like this:[code="sql"]with cteMatchKeys (MatchKey_Group, MatchKey_ID, KeyColumn)as(select 1, 1, 'MatchKey_PostOut' union allselect 1, 2, 'MatchKey_Name1' union allselect 2, 1, 'MatchKey_Name1' union allselect 2, 2, 'MatchKey_PhoneticStreet' union allselect 2, 3, 'MatchKey_PhoneticCompanyName' union allselect 3, 1, 'MatchKey_PostOut' union allselect 3, 2, 'MatchKey_PostIn')select * from cteMatchKeys[/code]Any ideas?Tue, 22 Jan 2013 07:43:15 GMTAbu Dina