Problem passing XML Parameter to SQL Server Stored Procedure

Question

I am trying to create a stored procedure that accepts XML data as an input parameter but cannot get it to compile. The Code:

CREATE PROCEDURE dbo.idn_UpdateUserApplications
(
    @AppIdList xml,
    @UserID nvarchar(256),
    @ModifiedBy nvarchar(256)
)

AS

BEGIN
    SET NOCOUNT ON

    INSERT INTO userapplication 
                (userid, 
                 modifiedby, 
                 modifiedon,
                 appid) 
    SELECT @UserID as userid, 
           @ModifiedBy As modifiedby, 
           Getdate() as modifiedon,
           paramvalues.id.VALUE('.', 'VARCHAR(20)') AS appid 
    FROM   @AppIdList.NODES('/Applications/id') AS paramvalues(ID)   
END

The Error: Msg 317, Level 16, State 1, Procedure idn_UpdateUserApplications, Line 13 Table-valued function 'NODES' cannot have a column alias.

I forgot to mention - this is SQL 2005 so I cannot use table valued parameters - darn !! — Jim Evans
– Jim Evans, Commented Jun 21, 2011 at 20:02

Mikael Eriksson · Accepted Answer · 2011-06-21 20:14:44Z

10

NODES and VALUE needs to be lower case nodes, value.

answered Jun 21, 2011 at 20:14

Mikael Eriksson

139k22 gold badges223 silver badges293 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Jim Evans Over a year ago

That was it - can't believe SQL Server is that lame (and I love SQL Server)

Collectives™ on Stack Overflow

Problem passing XML Parameter to SQL Server Stored Procedure

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related