Thank you Jeffrey for the suggestion. As you said there is a max length limit for RawContent property. I modified the code as below;

    Set pID = pReq.getId()
    
    Set tMessage = ##class(%Stream.GlobalCharacter).%New()
    Set tHL7 = ##class(EnsLib.HL7.Message).%OpenId(pID)
    Do tHL7.OutputToLibraryStream(.tMessage)

    set arrParam(1)= tMessage.Read()

Resolution/workaround  - https://community.intersystems.com/post/sql-outbound-adapter-execute-pro... 

Resolution - $$$SqlLongVarchar type of the input parameter was causing the issue as we need to pass the HL7 message as a %Stream.GlobalCharacter and  LOB (larger object). But still not sure why it was affecting the output value. $$$SqlLongVarchar is the proper type to define the nvarchar(max) sql type. 

I change the code as below for the input and input/output parameter; .tOutParams is returning the stored procedure output value as expected.

Set arrParam = 2
                                                               
                                                   //Defining the input parameter
               set arrParam(1)= pReq.RawContent
               set arrParam(1,"SqlType") = 12
               set arrParam(1,"CType") = $$$SqlWChar
               set arrParam(1,"IOType") = 1
               set arrParam(1,"Prec") = 8000
               set arrParam(1,"SqlTypeName") = "TEXT"

                 //Defining the output parameter
                 set arrParam(2)=""
                set arrParam(2,"SqlType") = $$$SqlVarchar
               set arrParam(2,"IOType") = 2
               set arrParam(2,"SqlTypeName") = "TEXT
             
                  //Stored procedure with the parameter definition
                  Set tQuery= "{call dbo.sp_InsertHL7(?,?)}"
                                  
               Set tSC = ..Adapter.ExecuteProcedureParmArray(,.tOutParams,tQuery,"ib",.arrParam)