Have you take a look on %FindDocuments in the DocDB

You can add sql from the add action and place your embedded sql query and get the values in host variable. Then set the sql return value to the field directly like below.

XData DTL [ XMLNamespace = "http://www.intersystems.com/dtl" ]
{
<transform sourceClass='EnsLib.HL7.Message' targetClass='EnsLib.HL7.Message' sourceDocType='2.3.1:ADT_A01' targetDocType='2.3.1:ADT_A01' create='new' language='objectscript' >
<assign value='source.{PV1:SetIDPV1}' property='target.{PV1:SetIDPV1}' action='set' disabled='1' />
<sql>
<![CDATA[ select count(id) INTO :value from Sample.Person]]></sql>
<assign value='value' property='target.{PV1:AdmissionType}' action='set' />
</transform>
}

Thank you @Manjunath Akar

May be you can try to take a clone of the object by %ConstructClone() I know it increments the reference count. However you can keep the old object for validation.

Thanks @Muhammad Waseem 

Thanks @Luis Angel Pérez Ramos 

Thanks a lot! 😀

Hello smythe,

There are couple of ways to import your file into stream and convert to Ens.StreamContianer

First you can create a %FileCharacterStream object and link your file in to it. Create a streamcontainer object and send the created stream as a first parameter in %OnNew to Ens.StreamContiner class.

ClassMethod StreamContainer()
{
    set file="dir\file" ; Your directory and file name(C:\Intersystems\new13.txt)
    set file = ##class(%FileCharacterStream).%New()
    do file.LinkToFile(file)
    set strmContainer=##class(Ens.StreamContainer).%New(file)
    zwrite strmContainer.Stream.Read($$$MaxLocalLength)
}

Second, This way it's almost similar way. But Instead of pushing directly into Ens.Streamcontainer.  You need to create a object for SDA. Import your file through  XMLImportSDAString method to prepare SDA object.  Now you have SDA object handy. So you can do any SDA stuffs with this object. Eventually it's an implicit step for validate your SDA file structure(Keep in mind the file structure should be exact XML format of SDA). Once the SDA object generated you can use Stream method generate stream from this SDA. Now you can convert to Ens.StreamContainer. Refer the below code to implement the above steps

ClassMethod FileToSDAToEnsContainer()
{
    set file = ##class(%FileCharacterStream).%New()
    do file.LinkToFile("C:\FHIRFILE\new13.txt")
    set SDA3Container=##class(HS.SDA3.Container).%New()
    do SDA3Container.XMLImportSDAString(file.Read($$$MaxLocalLength))
    zwrite SDA3Container
    do SDA3Container.ToQuickXMLStream(.stream)
    set strmContainer=##class(Ens.StreamContainer).%New(stream)
    zwrite strmContainer.Stream.Read()
}

I agree, it's a wonderful idea. Enable the auto save option periodically. It's better way to prevent the implementation lost. However, keep in mind to compile the code once all the implementation completed

Thank you @Robert Cemper  for spending time on the evaluation on the IntegrityLog-Web ,  CSPFileViewDownload and System-Task-REST and provided valuable feedback. 

Hello Scott, 

You have lot of object properties declared in your class definition. So, You should initialize the parameter to handle the unexpected fields like extending the %JSON.Adaptor in your class definitions. Otherwise it throw an error. So, Make sure the %JSONFIELDNAME was added if the JSON field name is different from your property and add parameter  %JSONIGNOREINVALIDFIELD to avoid unexpected field while loading JSON into object. Here is the article  about  the JSON adaptor

Parameter %JSONIGNOREINVALIDFIELD As BOOLEAN = 1

Yes, You can order your JSON structure by rearranging the property back and forth.

Hi Prashant,

You can get the values from %Metadata.columns.GetAt(i).ODBCType the ODBC type is referred the  datatype of the column. 12 for VARCHAR refer the ODBC type integer and datatype here

ClassMethod GetColumnType() As %String
{
    set statement = ##class(%SQL.Statement).%New()
    set tSC = statement.%PrepareClassQuery("Sample.Person","TestQuery")
    set result = statement.%Execute()
    #dim meta As %SQL.StatementMetadata
    set meta = result.%GetMetadata()
    write meta.columns.GetAt(1).ODBCType ;fetch first field
}

Hi @Gautam Rishi ,

You can use the  GetColumnType(columnnumber)  to get the datatype of the particular field in the result set object. This GetColumnType Method returns an integer values from 1 to 20.each number represent unique datatype( 10 represents VARCHAR ). If the type cannot be determined, 0 is returned.

For Dynamic SQL use %SQL.Statement It's preferable and suggested instead %Resultset. 

	set result = ##class(%ResultSet).%New("Class:Query")
	while result.Next() {
		for I=1:1:result.GetColumnCount() {
			write "Column Name: ",result.GetColumnName(I),!
			write "Datatype: ",result.GetColumnType(I),!
			
		}
	}

This is really useful when we working long strings. If we didn't declare any stream object  for the temp variable and pass to %ToJSON(temp). In that case, a %Stream.FileCharacter stream is created

The HS.SDA3.TimeStamp datatype class  accepts both YYYY-MM-DDT00:00:00Z and YYYY-MM-DDT00:00:00 values but truncates the additional date values if the length is more than 19 in some system methods such as IsValid, LogicalToXSD methods while performing exports. I have verified and This changes happened whenever it converts to XML. However the actual entire value is persist YYYY-MM-DDT00:00:00Z in the container object. Container object have capability to export both XML and JSON as a stream by using ToJSON() method. You can run the DTL programmatically to get the container object to verify. 

I used the DTL generated container object value for another DTL source to generate a FHIR discrete resource. It works. The BirthTime is same as the expected value

{
  "resourceType": "Patient",
  "birthDate": "1993-12-12T00:00:00Z"
}

Hello @Smythe Smythee 

There is no difference between ensemble and IRIS instance. In your case, the source.MemberDOB is an date( ex 01/01/2000) and the conversion is working perfectly. Can you check the input of the memberDOB before conversion and just take a quick look of the previous samples.

The same code is works in IRIS Interoperability DTL as well.

<assign value='$translate($ZDT($ZDTH(source.DOB),3)," ","T")_"Z"' property='target.{PID:DateTimeofBirth}' action='set' />