How to update a table with a stored procedure with XML parameter in SQL 2008?

Question

I have the following Stored Procedure query. This query is incomplete. I need help completing this query.

Problem:
In this SP, I am receiving a well-formed XML string into the SP. My goal is to update those rows in the Notes table that have the matching NoteId for the Notes passed in XML.

For example: If the XML has two notes with NoteId = 1, 2 and the table has three notes with NoteId = 1, 2, 3, I would like to update the NoteText of the table for rows with NoteId = 1, 2. Please help me complete my stored procedure.

Input XML:

<Notes>   
  <Note>
    <NoteId>1</NoteId>
    <NoteText>Hello</NoteText>    
  </Note> 
  <Note>
    <NoteId>2</NoteId>
    <NoteText>World</NoteText>    
  </Note> 
</Notes>

Table before update:

NoteId | NoteText | IsDeleted   
1        Hell       0
2        Worl       0
3        Test       0   

Table after update needs to look like:

NoteId | NoteText | IsDeleted   
1        Hello      0
2        World      0
3        Test       0  

This is the Stored Procedure that I currently have:

ALTER PROCEDURE USP_Notes_UpsertNotes 
    (@notesToUpsert XML)
AS
BEGIN

    SET NOCOUNT ON;

        UPDATE Notes
        SET NoteText = n.note_text
        FROM (  select  T.C.value('(NoteId/text())[1]','varchar(500)') as note_id,
                        T.C.value('(NoteText/text())[1]','varchar(500)') as note_text **PROBLEM**
                from @notesToUpsert.nodes('/Notes/Note') as T(C) 
             )   as n

        WHERE n.note_id = Notes.NoteId AND Notes.IsDeleted = 0

END
GO

Error screenshot:

Answer 1

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 <Note> 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.