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:

Error Message

• Tom: @MikaelEriksson Thank you for the questions. Let me try to address them. 1. I am still getting the error as mentioned. It is highlighted by the SQL Server Management Studio intellisense with the given error text. 2. Updated the table 'schema'. 3. Updated the XML. 4. "My goal is to update the Notes table with the content of the XML passed as parameter." Hope this clears up some confusion.
• Tom: I have added an image to show what's on my screen. The part of the query hidden below the message is @notesToUpsert.nodes('/Notes/Note') as T(C)
• Tom: When I parse and execute the query, Command(s) completed successfully.

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.

• Mikael Eriksson: Your two values clauses is not the same. The second version goes from root in the entire XML and fetches the first Text node regardless of what context node you are at from the nodes() clause. Demonstration here..
• Michael Green: Hmmm .. a transcription error on my part, perhaps. I"m sure I was seeing different behavior in SSMS earlier.
• Tom: @MichaelGreen Thank you so much for your input. The link for the .nodes() method was very valuable. I have been searching for a page like that and was not able to find it so far. Your pointers were really helpful as well. Thanks again! Unfortunately, I cannot upvote your answer just yet because, I still don't have enough points for that. Will upvote as soon as I get enough points.
• Tom: I am going to mark this as an answer because, this answer helped me the most. It seems like the main issue was with the intellisense for SQL Management Studio 2008 R2.