Results 1 to 8 of 8
  1. #1
    Join Date
    May 2008
    Posts
    4

    Question Unanswered: Error trying to insert into table using XML param.

    Hi all,

    I also have the same error, I am trying to do two things in my Stored Proc.

    1) - Insert a parent record.
    2) - Insert 1 or many records in the child table using the parent ID

    The child records are being passed as a XML param.

    I am also getting the
    'Subqueries are not allowed in this context. Only scalar expressions are allowed.'
    error when I try to create the procedure.

    Code:
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    -- =============================================
    -- Author:		pryder
    -- Create date: 
    -- Description:	
    -- =============================================
    CREATE PROCEDURE TestProc 
    	-- Add the parameters for the stored procedure here
    	@Name String ,
    	@bings XML 
    AS
    BEGIN transaction
    	-- SET NOCOUNT ON added to prevent extra result sets from
    	-- interfering with SELECT statements.
    	SET NOCOUNT ON;
    declare @NewID as int
    declare @err as int
        -- Insert statements for procedure here
    	INSERT INTO PARENT
        (Name)
        VALUES (@Name)
        SELECT @err = @@error
        if @err <> 0 
    begin
    rollback transaction
    return @err
    end
    
    
    SELECT SCOPE_IDENTITY = @NewID
    
    
    INSERT INTO Child 
    (ParentID,
    name)
    
    Values
    
    ((
    SELECT @NewID, ParamValues.ID.value('.','VARCHAR(MAX)')
    FROM @bings.nodes('bings/group') as ParamValues(ID)
    ))  
    
    
    SELECT @err = @@error
    if @err <> 0  begin rollback transaction return @err end
    
    
    commit transaction
    return @@error
    GO

  2. #2
    Join Date
    Apr 2008
    Posts
    4

  3. #3
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    this should get you out of the issues with the errors, but there is some questionable error handling in this thing. DISCALIMER: I did not fire up SQL Server to parse this.

    Code:
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    -- =============================================
    -- Author:		pryder
    -- Create date: 
    -- Description:	
    -- =============================================
    CREATE PROCEDURE TestProc 
    	-- Add the parameters for the stored procedure here
    	@Name String ,
    	@bings XML 
    AS
    BEGIN transaction
    	-- SET NOCOUNT ON added to prevent extra result sets from
    	-- interfering with SELECT statements.
    	SET NOCOUNT ON;
    declare @NewID as int
    declare @err as int
        -- Insert statements for procedure here
    	INSERT INTO PARENT
        (Name)
        VALUES (@Name)
    
        SELECT @NewID = SCOPE_IDENTITY,@err = @@Error
        if @err <> 0 
    begin
    rollback transaction
    return @err
    end
    
    INSERT INTO Child 
    (ParentID,
    name)
    SELECT @NewID, ParamValues.ID.value('.','VARCHAR(MAX)')
    FROM @bings.nodes('bings/group') as ParamValues(ID)
      
    SELECT @err = @@error
    
    if @err <> 0  begin rollback transaction return @err end
    
    commit transaction
    
    return @@error
    GO
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  4. #4
    Join Date
    May 2008
    Posts
    4
    I fully agree that the error handling could be better.. or even better work, but I cut and pasted this into a new sp, and still get the same error.

    Msg 170, Level 15, State 1, Procedure TestProc2, Line 34
    Line 34: Incorrect syntax near '.'.

    I've just tried OPENXML and am getting the same 'Subquery' error..

    I know I'm new, but this is driving me up the wall.. Don't know enough though to see where I am going wrong.

    Thanks for all the help though.

    Paul.

  5. #5
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    I fired up SQL and parsed mine successfully. If you need further assistance, please post some DDL and some sample data.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  6. #6
    Join Date
    May 2008
    Posts
    4
    Sorry that I duplicate the other post...
    I found that first and replied, then someone asked me to create a new thread...

    I did resist replying again it it though..

    Right DDL (I think) coming up.
    Attached Files Attached Files

  7. #7
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    this one compiles...

    Code:
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    -- =============================================
    -- Author:		pryder
    -- Create date: 
    -- Description:	
    -- =============================================
    CREATE PROCEDURE TestProc 
    	-- Add the parameters for the stored procedure here
    	@Name varchar(25) ,
    	@bings XML 
    AS
    BEGIN transaction
    	-- SET NOCOUNT ON added to prevent extra result sets from
    	-- interfering with SELECT statements.
    	SET NOCOUNT ON;
    declare @NewID as int
    declare @err as int
        -- Insert statements for procedure here
    	INSERT INTO PARENT
        (Name)
        VALUES (@Name)
    
        SELECT @NewID = SCOPE_IDENTITY(),@err = @@Error
        if @err <> 0 
    begin
    rollback transaction
    return @err
    end
    
    INSERT INTO Child 
    (ParentID,
    name)
    SELECT @NewID, ParamValues.ID.value('.','VARCHAR(MAX)')
    FROM @bings.nodes('bings/group') as ParamValues(ID)
      
    SELECT @err = @@error
    
    if @err <> 0  begin rollback transaction return @err end
    
    commit transaction
    
    return @@error
    GO
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  8. #8
    Join Date
    Jul 2007
    Posts
    50
    Right,

    Thanks for all your help guys, found out what my problem was..

    Although I had the 2005 version of the management tool, it was a SQLExpress 2000 backend...
    Installed 2005 and it all works.. Really sorry to waste your time like that.

    Cheers,

    Paul.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •