@Enrico Parisi 
No, NVARCHAR(8000) this doesn't work either.

I am using ensemble 2018 and database is SQL Server Management Studio 20

Things have slightly changed from our side. 

• initially used JSON to serialize data and pass it as a parameter to an INSERT statement. 
• Recently, we switched to storing XML data instead of JSON, and I am now trying to store this XML using the stored procedure

set tQuery="{ CALL dbo.SavePatientDetails (?) }"

Set tSC = ..Adapter.ExecuteProcedure(.tResultSnapshots, .tOutputParms, tQuery,,xmlContent)

Problem:

• Parameterized Query: Works fine when using VARCHAR(8000) as the parameter type in the stored procedure.
• Fails when using NVARCHAR(MAX) as the parameter type for passing XML data. The issue appears only when using the NVARCHAR(MAX) type, and I am unable to insert the XML content.

Your input is greatly appreciated. Thanks in advance for your help!

There are no errors returned. An empty string is being inserted into the database field, even though the trace shows the JSON correctly as {"CHI":"2894","Forename":"Janis"}.

I re installed the ensemble again!

I was following these steps only, but no luck

@Julian Matthews 
I already tried using call activity with sync.I'm using a %SQL.StatementResult to loop through rows and make synchronous web service calls.

The problem I'm facing is that this loop works only for the first iteration. If I set the call to be asynchronous,it iterates through all the rows correctly.

Hi Julian,

This is the code activity from BPL.

<code xpos='200' ypos='750' >

<![CDATA[ set sc=..SendRequestSync("XXX Operation",context.XXDocument)]]>

</code>

Error:

xx.ContextResponseHandlers.1 Compiling routine xx.Thread1.1 ERROR: xx.Thread1.cls(S7+17) : MPP5376 : Method or Property 'SendRequestSync' does not exist in this class. TEXT: set sc=..SendRequestSync("XXX Operation",context.XXXDocument) Compiling routine XX.Thread1ChildThreads.1 Compiling routine XX.Thread1PendingResponses.1 Compiling routine XX.Thread1SyncResponses.1 Detected 1 errors during compilation in 1.160s.

@Ashok Kumar 
yes, this code works. But Im using a <sync> activity in conjuction with call activity inside a loop in BPL. The call activity is asynchronous and its completion is being waited upon by the sync activity, it might be preventing the loop from moving to the next iteration.

Thanks, that worked.@Mary George 

@Ashok Kumar 
thank you so much. I got it working!

@Ashok Kumar <assign property="context.sqlquery" value="&quot;select surname from Reports where Discipline = ?&quot;" action="set" xpos='200' ypos='550' />                                             <assign property="context.tResult" value="##Class(%SQL.Statement).%ExecDirect(,context.sqlquery,context.ReportDiscipline)" action="set" xpos='200' ypos='650' />. This is how my sql query looks like. This doesnt works with parameter in where condition. Any thoughts?

Hi @Ashok Kumar 
that was helpful, thank you. But when creating the tResult context variable, I could only see 

StatementColumn

StatementObject

StatementParameter these many types. Also, could you please advice on how to pass context variable in where condition of this sql query.

Many thanks,

I have a set of labreports saved in db.I need to retreive this data from db and also trace the value of each column. 

/// BPL to send results via Webservice

Class Result Extends Ens.BusinessProcessBPL

{

Storage Default

{

<Type>%Library.CacheStorage</Type>

}

/// BPL Definition

XData BPL [ XMLNamespace = "http://www.intersystems.com/bpl" ]

{

<process language='objectscript' request='Ens.Request' response='Ens.Response' height='2000' width='2000' >

<context>

<property name='Forename' type='%String' instantiate='0' >

<parameters>

<parameter name='MAXLEN'  value='50' />

</parameters>

</property>

<property name='Surname' type='%String' instantiate='0' >

<parameters>

<parameter name='MAXLEN'  value='50' />

</parameters>

</property>

<property name='ReportDiscipline' type='%String' instantiate='0' >

<parameters>

<parameter name='MAXLEN'  value='50' />

</parameters>

</property>

</context>

<sequence xend='200' yend='850' >

<trace name='TEMP trace element' value='"In business process "_request.StringValue' xpos='200' ypos='250' />

<assign property="context.ReportDiscipline" value="request.StringValue" action="set" xpos='200' ypos='350' />

<trace value='"ReportDiscipline value: "_context.ReportDiscipline' xpos='200' ypos='450' />

<sql xpos='200' ypos='550' >

<![CDATA[

SELECT Forename,Surname INTO :context.Forename,:context.Surname

FROM LabReports

WHERE Discipline =:context.ReportDiscipline]]>

</sql>

<trace value='"SQLCODE: "_SQLCODE' xpos='200' ypos='650' />

<trace value='"returned value: "_context.Forename' xpos='200' ypos='750' />

</sequence>

</process>

}

}

@Cristiano Silva 
this is how my BPL class looks like now. Now the sql code returns nothings though it shows correct value when parameter value is hardcoded. I wanted to retreive resultset and loop over it, to trace each of the values for now.