xquery equivalent of tsql where clause within XML nodes

[sohot]

I'm trying to shred XML files that have an unusual layout, because items are not arranged with conventional elements and attributes, but instead in a kind of "list within the xml" format: \[code\]<SOAP-ENV:Envelope xmlns:SOAP-ENV="http://schemas.xmlsoap.org/soap/envelope/"> <SOAP-ENV:Body> <ns:Lists xmlns:ns="urn:Listing"> <ns:items> <ns:listRef>List1</ns:listRef> <ns:name>Test list items</ns:name> <ns:extendedFields> <ns:extendedField> <ns:name>Fruit</ns:name> <ns:data>Apple</ns:data> </ns:extendedField> <ns:extendedField> <ns:name>Vegetable</ns:name> <ns:data /> </ns:extendedField> <ns:extendedField> <ns:name>Sweet</ns:name> <ns:data>Crunchie</ns:data> </ns:extendedField> </ns:extendedFields> </ns:items></ns:Lists></SOAP-ENV:Body></SOAP-ENV:Envelope>\[/code\]I've tried variations of the XQuery below, but am not getting it right. What I need to do is the Xpath equivalent of "select ns:data from x where ns:name = 'Fruit'" for example. In the past I was able to do this using singleton numbering to point to the location of the bit that I wanted on the tree, but in this case the number of extendedFields is variable, and the sequence changes, so the only way to get to the node you want will be to specify the name/value pair that you want. \[code\]DECLARE @x xml SET @x = ( SELECT xml_data_column FROM dbo.Table WHERE xmlFileName = 'D:\XML_list.xml'); ;WITH XMLNAMESPACES('urn:Listing' AS ns) SELECT t.c.value('../ns:listRef[1]', 'varchar(20)') listRef , t.c.value('../ns:name[1]', 'varchar(20)') name , t.c.value('/.', 'varchar(30)') ext1b, t.c.value('ns:extendedField[1]/ns:name[1]', 'varchar(30)') name1, t.c.value('ns:extendedField[1]/ns:data[1]', 'varchar(30)') data1, t.c.value('ns:extendedField[2]/ns:name[1]', 'varchar(30)') name2, t.c.value('ns:extendedField[2]/ns:data[1]', 'varchar(30)') data2, t.c.value('ns:extendedField[3]/ns:name[1]', 'varchar(30)') name3, t.c.value('ns:extendedField[3]/ns:data[1]', 'varchar(30)') data3FROM @x.nodes('//ns:Lists/ns:items/ns:extendedFields[1]') AS t(c)\[/code\]So if I wanted to see what fruit item this person opted for, then I would want the output to look like: \[code\]listRef, name, Fruit List1, Test list items, Apple\[/code\]