peter larsen (3/18/2009)
I found the solution.Sometimes it helps, when you write your problem down on 'paper'.
SELECT
T.c.value('../../@Id[1]','int') AS PropertyId,
T.c.value('(CountryCode/text())[1]', 'varchar(256)') as CountryCode,
T.c.value('(Number/text())[1]', 'varchar(256)') as PhoneNumber
FROM @x.nodes('//Property/PhoneList/Phone') T(c)
One caveat is that the ".." parent node axis step can hurt performance. If you think about it, the XML processor has to back up to find the parent node, which can be time consuming depending on how complex your XML is. It might be more efficient to use a CROSS APPLY to extract child nodes from the //Property nodes. Then again, if your XML data is small in size the performance may not be a factor anyway.