Results 1 to 3 of 3
  1. #1
    Join Date
    Oct 2013
    Posts
    8

    Unanswered: Store Procedure Updating XML column with user input

    My code works if I would pre-define the changes i want within the query but I want the user to provide his own input so that he can change any specific XML column based on user input.

    My problem is it is giving me errors and it does not identify the variable NEWADD which I have declared. Here is my code:

    Code:
    CREATE PROCEDURE MNI_XML_STUD_ADD(  
                                           in IDNUMBER 	CHAR(8),
    					 		   
                                           in NEWADD VARCHAR(50))
    
    DYNAMIC RESULT SETS 1
    
    LANGUAGE SQL
    
    begin
    	
    	UPDATE XML_STUD
    	SET STUDINFO = xmlquery('copy $test := $STUDINFO modify do
    					     replace value of $test/Student/Address 
                                                 with NEWADD return $test')
            WHERE IDNO = IDNUMBER;
    	
    end
    I want that the user could input any world he want under the variable NEWADD, I have no problems with the variable IDNUMBER it is working fine my only issue the NEWADD.

    Is there any way to call a variable within an xmlquery? Thanks in advance.
    Last edited by intes2013; 10-04-13 at 08:01.

  2. #2
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    You should reference the variable in the XMLQUERY string similarly to other variables in it, $STUDENT and $test. Use the PASSING clause to define that variable.

    Code:
    ... xmlquery('copy $test := $STUDINFO modify do
    					     replace value of $test/Student/Address 
                                                 with $foo return $test'
                                           passing STUDINFO as "foo"
        )...
    Last edited by n_i; 10-04-13 at 08:29.
    ---
    "It does not work" is not a valid problem statement.

  3. #3
    Join Date
    Oct 2013
    Posts
    8
    Thanks alot. Now I can continue my project. appreciated.

Posting Permissions

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