Stack Exchange
Execute Stored Procedure with input xml and retrieving output xml delphi xe
I have this procedure create in delphi, but i cant get the values from a XML Parameters in the stored procedure.
I want to send the XML to the database and retrieve the response in other xml that the stored procedure retrieve to me...
Delphi Code.  
procedure TForm1.process(Sender: TObject);
    var txt,st:TBytesStream;
        txt_s:TBlobData;
        field:TBlobField;

    begin
      txt:=TBytesStream.Create;
      Memo1.Lines.SaveToStream(txt);
      ADOStoredProc1.Parameters.Items[0].LoadFromStream(txt,ftwidestring); //i think its working
      ADOStoredProc1.ExecProc;
      TBlobField(ADOStoredProc1.Parameters.Items[1]).SaveToStream(st); //not working
      Memo2.Lines.LoadFromStream(st);
      //Memo2.Lines.LoadFromStream(txt_s);
      Label1.Caption:=IntToStr(Memo1.Lines.Count);
      Label2.Caption:=IntToStr(Memo2.Lines.Count);
    end;

Stored procedure
ALTER PROCEDURE [dbo].[SP_Processa_Dados]
    @XMLParamIN XML,
    @XMLParamOUT XML OUTPUT
AS
BEGIN
    SET NOCOUNT ON;
    SET @XMLParamOUT= @XMLParamIN;
    SELECT 1 AS COLA,'b' as COLB;
END


Why are you passing the XML as binary? Use an xml datatype, or a varchar if Delphi can't cope with xml.

@podiluska i change sp to retrieve xml. the problem is the same :(

Side note: you should not use the sp_ prefix for your stored procedures. Microsoft has reserved that prefix for its own use (see Naming Stored Procedures), and you do run the risk of a name clash sometime in the future. It's also bad for your stored procedure performance. It's best to just simply avoid sp_ and use something else as a prefix - or no prefix at all!

@marc_s yup i know that. this is a test procedure :)

no need to treat XML parameters as binary, just use string...

@whosrdaddy i try it but only accept ntext with sql procedure parameter and not xml... dont know if is delphi bug or bad usage of ADOStoredProc. Using base64 encode only save half of the data...

What Delphi version are you using?

@whosrdaddy delphi xe

https://forums.embarcadero.com/thread.jspa?threadID=105806&tstart=0 Answer from Embarcadero Forum
