Results 1 to 5 of 5
  1. #1
    Join Date
    Jan 2003
    Posts
    67

    Unanswered: Passing XML to a stored proc and returning the results in XML

    I can do this with T-SQL but I have little experence with PL/SQL so I'm not sure this can even be done.

    I would like to pass an XML document to a stored procedure and return the results in XML format (returning XML is less importent). Can this be done with Oracle? If so are there any totorials or examples that explain this process?

    Thanks for your thoughts.

  2. #2
    Join Date
    Oct 2002
    Location
    Cape Town, South Africa
    Posts
    253
    Yes it is possible. Here is a procedure that uses xml in and out. Its not too neat, but I think you will get the idea.
    Code:
    	function fnc_validateallocation (istrXmlData in varchar2) return clob is
    
    		qryCtx					  dbms_xmlquery.ctxHandle;
    		clbResult				  clob;
    		strSql					  varchar2(2000);
    		strBucketNo				varchar2(12);
    		numMps					  number(1) := null;
    		numRunId					number(10) := null;
    		document					sys.xmltype;
    		element					  sys.xmltype;
    		recMps					  tbl_mps%rowtype;
    		recRun					  tbl_runs%rowtype;
    		invalid_doc				exception;
    
      begin
    
    		if istrXmlData is null then
    			null;
    		end if;
    
    		document := sys.xmltype.createxml(istrXmlData);
    
    		-- check if valid bucket xml document
    		if (document.existsnode('/data') = 0) then
    			raise invalid_doc;
    		end if;
    
    		-- extract bucket no
    		element := document.extract('/data/datam[@id="bucket"]/@val');
    		if (element is not null) then
    			strBucketNo := element.getstringval();
    		end if;
    
    		-- extract mps no
    		element := document.extract('/data/datam[@id="mps"]/@val');
    		if (element is not null) then
    			numMps := element.getnumberval();
    		end if;
    
    
    		if (numMps is null) then
    			recRun := pck_runs.getrun(strBucketNo);
    
    			if (recRun.Run_State_Id in (mppmsbucketstateplanned, mppmsbucketstatefrozen)) then
    				clbResult := '<?xml version="1.0"?><success><datam>curry on</datam></success>';
    				return clbResult;
    
    			else
    				clbResult := '<?xml version="1.0"?><error><datam>This operation is only available for Planned Buckets</datam></error>';
    				return clbResult;
    
    			end if;
    
    		else
    			recMps := pck_mps.getMps(strBucketNo, numMps);
    
    			if (recMps.Mps_State_Id = mppmsmpsstateplanned) then
    				clbResult := pck_get.fnc_getxmlmps(istrXmlData);
    				return clbResult;
    
    			else
    				clbResult := '<?xml version="1.0"?><error><datam>This operation is only available for Planned MPS</datam></error>';
    				return clbResult;
    
    			end if;
    
    		end if;
    
    	end fnc_validateallocation;

  3. #3
    Join Date
    Jan 2003
    Posts
    67

    Thanks, is it supported...

    ... in Oracle 8i. I did find an Oracle site where the talk about XML processing and Oracle but only reference 9i.

    http://otn.oracle.com/sample_code/te...204/index.html

    Thanks

  4. #4
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    goto http://asktom.oracle.com
    and do a keyword search on "XML"
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  5. #5
    Join Date
    Oct 2002
    Location
    Cape Town, South Africa
    Posts
    253
    The last I used this, they did not have xml for Oracle 8i. We had to go to 9i.

Posting Permissions

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