sql server - Get all XML nodes that contain a certain inner node in T-SQL -

i receiving xml string information many tables. problem in stead of having format

<root>   <candy>     <name>m-m</name>     <size>small</size>   </candy>   <candy>     <name>kitkat</name>     <size>medium</size>   </candy>   <fruit>     <description>banana</description>     <color>yellow</color>   </fruit>   <fruit>     <description>cherry</description>     <color>red</color>   </fruit> <root> 

what is

<root>   <candy>     <m-m>       <size>small</size>     </m-m>     <kitkat>       <size>medium</size>     <kitkat>   </candy>   <fruit>     <banana>       <color>yellow</color>     </banana>     <cherry>       <color>red</color>     </cherry>   </fruit> </root> 

i need insert rows candy in table candy , rows fruits in table fruit. if received first format i'd do

 select *    openxml (@hdoc, '/root/candy', 2) 

and i'd candy rows, guys other side of web service reluctant send way. so, before put fight, there way candy using second xml format? requesting nodes have inner node named size? i'm using sqlserver 2008 can use either openxml instruction or newer node functions, haven't learned how use yet. also, process speed more important memory, think...

you can try way :

declare @t xml = '<root>   <candy>     <m-m>       <size>small</size>     </m-m>     <kitkat>       <size>medium</size>     </kitkat>   </candy>   <fruit>     <banana>       <color>yellow</color>     </banana>     <cherry>       <color>red</color>     </cherry>   </fruit> </root>'  select candies.x.value('local-name(.)', 'varchar(100)') name        , candies.x.value('size[1]', 'varchar(100)') size @t.nodes('root/candy/*[size]') candies(x) 

sql fiddle

the xpath root/candy/*[size] means child nodes of <candy>, whatever it's name, having child <size>.