3

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

3
  • @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
    Jan 16, 2015 at 15:34
  • 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
    Jan 16, 2015 at 16:40
  • When I parse and execute the query, Command(s) completed successfully.
    – Tom
    Jan 16, 2015 at 16:46

1 Answer 1

2

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.

4
  • 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.. Jan 16, 2015 at 6:32
  • Hmmm .. a transcription error on my part, perhaps. I"m sure I was seeing different behavior in SSMS earlier. Jan 16, 2015 at 9:49
  • @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
    Jan 16, 2015 at 16:06
  • 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.
    – Tom
    Jan 16, 2015 at 17:25

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.