How to update a table with a stored procedure with XML parameter in SQL 2008?
The .nodes() method must start at the root of your xml.  It will then return one "row" per matching node for the SELECT clause to work on. It should look like this:

from @notesToUpsert.nodes('/Notes/Note') as note(col)


(BTW using "note" as an alias is confusing.  Perhaps T(c) would be easier to debug?)

Then the XQuery in the SELECT clause operates in the xml context of each "row" i.e. a &lt;Note&gt; element.  The query path will be:

 col.value('(./Text)[1]','varchar(500)')


,col.value('(/Notes/Note/Text)[1]','varchar(500)')

A handy debugging aid is to just dump the xml for each "row":

select
    col.query('.') as Debug
...




Edit: re-tested in light of Mikael's comments.  I couldn't reproduce my earlier output and have removed the offending line.