SQLT OPENROWSET iterating through mutliple nodelists on a XML file

[Callan]

I have a XML file that is a atlas of \[code\]food\[/code\] data, I'm trying to retrieve all the data from the XML file. But I have no idea how I can iterate over a set of multiple nodes that are grouped together (\[code\]food-images\[/code\] and \[code\]portion-size\[/code\] for instance), in the same query.\[code\]<portion> <food-description>Rice</food-description> <food-images> <food-number file-type="jpg">1</food-number> <food-number file-type="jpg">2</food-number> </food-images> <portion-size> <size tag="1" unit="g">39</size> <size tag="2" unit="g">85</size> <size tag="3" unit="g">131</size> <size tag="4" unit="g">177</size> <size tag="5" unit="g">224</size> <size tag="6" unit="g">270</size> <size tag="7" unit="g">316</size> <size tag="8" unit="g">362</size> </portion-size> <size-increment eight-plus="408">46.14</size-increment></portion>\[/code\]So far I've tried..\[code\]DECLARE @xmlData XMLSET @xmlData = http://stackoverflow.com/questions/14598901/( SELECT * FROM OPENROWSET ( BULK'C:\food-portion-size-atlas-modified.xml', SINGLE_CLOB ) AS xmlData)SELECTref.value('size[1]', 'int') AS sizeref.value('./food-images[1]/food-number[1]', 'int') AS sizeFROM @xmlData.nodes('/fps-photo-atlas/portion/portion-size') xmlData(ref)\[/code\]But I've found I can only query the nodes at \[code\]'/fps-photo-atlas/portion/portion-size'\[/code\], I'm not able to also query information from \[code\]food-images\[/code\] without running a separate query.Is there anyway to get all the information from the XML file in the same query (such as searching \[code\]up\[/code\] from the \[code\]center\[/code\] of the current node, or are you limited to only \[code\]Breadth-first search\[/code\] in T-SQL Xpath)?Preferably I'd want something like\[code\]DECLARE @xmlData XMLSET @xmlData = http://stackoverflow.com/questions/14598901/( SELECT * FROM OPENROWSET ( BULK'C:\food-portion-size-atlas-modified.xml', SINGLE_CLOB ) AS xmlData)SELECTref.value('size[1]', 'int') AS size,ref.value('././food-images[1]/food-number[1]', 'int') AS foodnumberFROM @xmlData.nodes('/fps-photo-atlas/portion/portion-size') xmlData(ref)WHERE ref.value('././food-images[1]/food-number[1]', 'int') = 1\[/code\]Expect output:\[code\]size | foodnumber----------------------------------- 55 | 1 21 | 1 23 | 1\[/code\]