Add new xml node in specific location

Question

I have a table with a xml column.
I need to add a new node to the xml but it needs to go into a specific location.

Here is an example. Not our actual data but the structure is similar.
I want to add the following between Milk and Lettuce.
<Product>
<Category>Vegetable</Category>
<Name>Carrots</Name>
</Product>
Using SQL, how would I insert this. I've tried using the .modify method but can't get the syntax correct.

<Table>
   <Product>
      <Category>Dairy</Category>
      <Name>Milk</Name>
   </Product>
   <Product>
      <Category>Vegetable</Category>
      <Name>Lettuce</Name>
   </Product>
</Table>

Answer 1

DECLARE @x xml = '<Table>
   <Product>
      <Category>Dairy</Category>
      <Name>Milk</Name>
   </Product>
   <Product>
      <Category>Vegetable</Category>
      <Name>Lettuce</Name>
   </Product>
</Table>'

SET @x.modify('insert  <Product><Category>Vegetable</Category><Name>Carrots</Name></Product> after  (/Table/Product[Name="Milk"])[1]')
SELECT @x