Stored Procedure values not insert XML node

Question

Stored procedure:

ALTER proc [dbo].[Equip_SaveTeamTaskAssigineeDetails] (
@saveDataXML xml)
as 
begin

    Declare @claimid int,
            @createdate datetime,
            @duedate datetime,
            @startdate datetime,
            @assignmentid int,
            @assignedto varchar(50),
            @piority varchar(50),
            @comment varchar(4000)



    select  @claimid = node.value('claimid[1]','int'),
            @createdate = node.value('createdate[1]','datetime'),
            @duedate = node.value('duedate[1]','datetime'),
            @startdate = node.value('startdate[1]','datetime'),
            @assignmentid = node.value('assignmentid[1]','int'),
            @assignedto = node.value('assignedto[1]','varchar(50)'),
            @piority= node.value('piority[1]','varchar(50)'),
            @comment = node.value('comment[1]','varchar(4000)')


        from @saveDataXML.nodes('/TeamTaskDetails/Task') nodes(node)  

IF (@claimid = 0)
        BEGIN
        INSERT INTO  [dbo].[TeamTaskAssiginment]
               ([claimid],
                [createdate],
                [duedate],
                [startdate],
                [assignedid],
                [assiginedto],
                [priority],
                [comment])




               VALUES
                (@claimid,
            @createdate,
            @duedate,
            @startdate ,
            @assignmentid ,
            @assignedto ,
            @piority ,
            @comment
                )

        --SET @claimid = SCOPE_IDENTITY()
        END
    ELSE
        BEGIN
            Update [dbo].[TeamTaskAssiginment] set


            claimid=@claimid ,
                createdate=@createdate,
                duedate=@duedate,
                startdate=@startdate,
                assignedid=@assignedto,
                assiginedto=@assignedto,
                priority=@piority,
                comment=@comment

        END

Execute Stored Procedure 

Exec Equip_SaveTeamTaskAssigineeDetails @saveDataXML =
<TeamTaskDetails>
<Task claimid="1" createdate="2000-12-16" duedate="2000-12-16" startdate="2000-12-16" assignmentid="1" assignedto="test"  piority="HIGH" comment="test"  />
</TeamTaskDetails>

If I executed Stored Procedure its showing 0 Rows Affected values not inserting

Please help to resolve the issue.

Answer 1

You're querying the xml wrong, to get the attribute values you need to query it like so, replacing claimid[1]:

SELECT  @claimid = node.value('@claimid', 'int') ,....

Full sample:

DECLARE @saveDataXML AS XML = 
    '<TeamTaskDetails>
        <Task claimid="1" createdate="2000-12-16" duedate="2000-12-16" 
              startdate="2000-12-16" assignmentid="1" assignedto="test"  
              piority="HIGH" comment="test"  />
    </TeamTaskDetails>'

DECLARE @claimid INT ,
    @createdate DATETIME ,
    @duedate DATETIME ,
    @startdate DATETIME ,
    @assignmentid INT ,
    @assignedto VARCHAR(50) ,
    @piority VARCHAR(50) ,
    @comment VARCHAR(4000)



SELECT  @claimid = node.value('@claimid', 'int') ,
        @createdate = node.value('@createdate', 'datetime') ,
        @duedate = node.value('@duedate', 'datetime') ,
        @startdate = node.value('@startdate', 'datetime') ,
        @assignmentid = node.value('@assignmentid', 'int') ,
        @assignedto = node.value('@assignedto', 'varchar(50)') ,
        @piority = node.value('@piority', 'varchar(50)') ,
        @comment = node.value('@comment', 'varchar(4000)')
FROM    @saveDataXML.nodes('/TeamTaskDetails/Task') nodes ( node )  


SELECT  claimid = @claimid ,
        createdate = @createdate ,
        duedate = @duedate ,
        startdate = @startdate ,
        assignedid = @assignedto ,
        assiginedto = @assignedto ,
        priority = @piority ,
        comment = @comment

Ouput:

claimid createdate  duedate     startdate   assignedid....
1       2000-12-16  2000-12-16  2000-12-16  test...

Also, as I commented, the insert isn't hit because your current select is returning nulls, so IF (@claimid = 0) won't return true. And your update query will update every row unless you specify a where clause.

Answer 2

There no END for the BEGIN Statement. Please try this code

ALTER proc [dbo].[Equip_SaveTeamTaskAssigineeDetails] (
@saveDataXML xml)
AS
BEGIN

    Declare @claimid int,
            @createdate datetime,
            @duedate datetime,
            @startdate datetime,
            @assignmentid int,
            @assignedto varchar(50),
            @piority varchar(50),
            @comment varchar(4000)



    select  @claimid = node.value('claimid[1]','int'),
            @createdate = node.value('createdate[1]','datetime'),
            @duedate = node.value('duedate[1]','datetime'),
            @startdate = node.value('startdate[1]','datetime'),
            @assignmentid = node.value('assignmentid[1]','int'),
            @assignedto = node.value('assignedto[1]','varchar(50)'),
            @piority= node.value('piority[1]','varchar(50)'),
            @comment = node.value('comment[1]','varchar(4000)')


        from @saveDataXML.nodes('/TeamTaskDetails/Task') nodes(node)  

    IF (@claimid = 0) BEGIN
        INSERT INTO  [dbo].[TeamTaskAssiginment]
               ([claimid],
                [createdate],
                [duedate],
                [startdate],
                [assignedid],
                [assiginedto],
                [priority],
                [comment])




               VALUES
                (@claimid,
            @createdate,
            @duedate,
            @startdate ,
            @assignmentid ,
            @assignedto ,
            @piority ,
            @comment
                )

        --SET @claimid = SCOPE_IDENTITY()
     END ELSE BEGIN
            Update [dbo].[TeamTaskAssiginment] set


            claimid=@claimid ,
                createdate=@createdate,
                duedate=@duedate,
                startdate=@startdate,
                assignedid=@assignedto,
                assiginedto=@assignedto,
                priority=@piority,
                comment=@comment

      END
END


GO

Also check the XML code. It should be in a single inverted comma :

--  Execute Stored Procedure    
Exec Equip_SaveTeamTaskAssigineeDetails @saveDataXML =
'<TeamTaskDetails>
<Task claimid="1" createdate="2000-12-16" duedate="2000-12-16" startdate="2000-12-16" assignmentid="1" assignedto="test"  piority="HIGH" comment="test"  />
</TeamTaskDetails>'