How about using a UNION? select col.value('(ADDRESS/PHONES/TYPE)[1]', 'VARCHAR(40)' ) AS TYPEOFPHONE, col.value('(ADDRESS/PHONES/NUMBER)[1]', 'VARCHAR(40)' ) AS NUMBER FROM @xMLVAR.nodes('/RECORDS/RECORD') AS Feed(Col) where col.value('(CUSTOMERINFO/ID)[1]', 'tinyint') = 1 UNION select col.value('(ADDRESS/PHONES/TYPE)[2]', 'VARCHAR(40)' ) AS TYPEOFPHONE, col.value('(ADDRESS/PHONES/NUMBER)[2]', 'VARCHAR(40)' ) AS NUMBER FROM @xMLVAR.nodes('/RECORDS/RECORD') AS Feed(Col) where col.value('(CUSTOMERINFO/ID)[1]', 'tinyint') = 1
Thank you Jon M , I appricate your quick response. I am going to use this query on a much larger xml where some customers may have 1, 2 , 3 or more phones. Any way to return results for all the typeofphone and number for <PHONES> section thx conan
