Entity–attribute–value model is usually used for this purpose.

I have already written about this at the time: SQL index for array property elements.

Here is a ready-made example (works even in last Caché):

Class dc.test Extends %Persistent
{

Property title As %VarString;

Property author As %VarString;

/// do ##class(dc.test).test()
ClassMethod test()
{
  &sql(truncate table dc.test)
  
  &sql(insert into dc.test(title,author)
    select 'For Whom the Bell Tolls','Hemmingway' union
    select 'The Invisible Man','Ellison' union
    select 'Tender is the Night','Fitzgerald')
  
  set provider=##class(%ZEN.Auxiliary.altJSONSQLProvider).%New(),
      provider.sql="select title,author from dc.test",
      provider.arrayName="items",
      provider.maxRows = 0,
      provider.%Format = "tw"
  
  do provider.%WriteJSONStreamFromSQL(.stream,,,,,provider)
  
  set json={}.%FromJSON(stream),
      json.results=json.items.%Size()

  write json.%ToJSON()
}

}

Result:

USER>do ##class(dc.test).test()
{"items":[{"title":"For Whom the Bell Tolls","author":"Hemmingway"},{"title":"The Invisible Man","author":"Ellison"},{"title":"Tender is the Night","author":"Fitzgerald"}],"results":3}

Google the JDBC Driver for Excel.
Here is a good option: HXTT Excel Pure Java JDBC (1.2, 2.0, 3.0, 4.0, 4.1, 4.2, 4.3) Drivers for MS Excel (XLS, XLSX)

Class dc.test [ Abstract ]
{

ClassMethod ValType(ByRef var) As %String
{
  s a(-1)="undefined",a(0)="empty",a(1)="string",a(2)="integer",a(3)="float",a(4)="double",a(5)="object",a(6)="list",a(7)="array",a(8)="bitstring"
  s r=$s('$d(var):-1, $isobject(var):5, $d(var)>9:7, var="":0, $lv(var):6, $l(var)>254:1, 1:$tr($a($lb(var),2),1245678,1122334))
  i r=1 {
    s s=##class(%Utility).FormatString(var)
    s:($f(s,"$c(")=4)||($f(s,"$zwc(")=6) r=8
  }
  q a(r)
}

/// d ##class(dc.test).test()
ClassMethod test()
{
  s old=$system.Process.Undefined(2)
  try{

    w ##class(%Utility).FormatString(undef)," => ",..ValType(.undef),!

    f i="","1",1,1.1,$double(1.1),##class(%ListOfDataTypes).%New(),$lb(),$factor(1),$c(7) {
      w ##class(%Utility).FormatString(i)," => ",..ValType(.i),!
    }
    
    s (i,i(1))=""
    w ##class(%Utility).FormatString(i)," => ",..ValType(.i),!

  }catch{
  }
  
  d $system.Process.Undefined(old)
}

}

Result:

USER>d ##class(dc.test).test()
"" => undefined
"" => empty
1 => string
1 => integer
1.1 => float
$double(1.1000000000000000888) => double
"1@%Library.ListOfDataTypes" => object
$lb() => list
$zwc(128,4)_$c(1,0,0,0)/*$bit(1)*/ => bitstring
$c(7) => bitstring
"" => array

InterSystems IRIS Community Edition Limitations
Also see %SYSTEM.License, e.g. method MaxConnections

It can be simpler:

Property HowManyRecords As %Integer [ Calculated, ReadOnly, Required, SqlComputeCode = {n r,SQLCODE &sql(select count(*) into :r from schema_package.table) s {*}=$s(SQLCODE:-1,1:r)}, SqlComputed ];

Are a couple of points:

1. The macro "ClassSQLTable" is defined in the method %Library.EnsembleMgr:UpgradeUTCIndices
2. I would prefer to use ##class(%DeepSee.Utils).%GetSQLTableName(pClass As %String, pVerifyExists=0)

Unfortunately, there are errors in the methods GenExportBinaryStream/GenImportBinaryStream in the class %JSON.Generator.

Steven, are you sure that wanted to answer namely me?

Or something like that:

Class dc.test Extends %Persistent
{

Property Name As %String;

Property Age As %Numeric;

Property City As %String;

Property Phone As %String;

/// d ##class(dc.test).Test()
ClassMethod Test()
{
    do ..%KillExtent()
    
    // This is your Data-Object...

    ;set data="",data=[],data=..%New()

    set data=[
      {"Name":"Joe", "Age":44, "City":"Boston", "Phone":"1-234-4567"},
      {"Name":"Ron", "Age":48, "City":"Dallas", "Phone":"1-234-5678"},
      {"Name":"Eve", "Age":40, "City":"Miami",  "Phone":"1-234-4567"},
      ($listbuild("Tommy", 50, "New York", "1-345-6789")),
      ($listbuild("Alexa", 35, "Portland", "1-567-8901"))
    ]

    // Now insert all the above data into your table...
    &sql(
      INSERT INTO dc.test(Name,Age,City,Phone)
      SELECT $list(row,1),$list(row,2),$list(row,3),$list(row,4) 
      FROM dc.DataProvider(:data)
    )
    
    zwrite ^dc.testD

    // Now update your table...
    set data=[($listbuild("Vitaliy", 35, "Moscow", "1-999-1234"))]

    &sql(
      UPDATE dc.test t
      SET t.Name=obj.Name
      FROM (SELECT $list(row,1) Name,$list(row,2) Age  FROM dc.DataProvider(:data)) obj
      WHERE t.Age=obj.Age
    )
    
    zwrite ^dc.testD
}

Query NewQuery1(data) As %Query(ROWSPEC = "row:%List") [ SqlName = DataProvider, SqlProc ]
{
}

ClassMethod NewQuery1Execute(
  ByRef qHandle As %Binary,
  data) As %Status
{
  if $IsObject(data),data.%IsA("%Library.DynamicArray"),data.%Size() {

    set qHandle=data.%Size()+1

    for i=1:1:data.%Size() {
      set r=data.%Get(i-1)

      if $IsObject(r) {
        set it=r.%GetIterator(),row=""
        while it.%GetNext(,.val) { set row=row_$listbuild(val) } 

      } else {
        set row=r
      }
      set qHandle(qHandle-i)=$listbuild(row)
    }

  }

  quit $$$OK
}

ClassMethod NewQuery1Fetch(
  ByRef qHandle As %Binary,
  ByRef Row As %List,
  ByRef AtEnd As %Integer = 0) As %Status [ PlaceAfter = NewQuery1Execute ]
{
  if qHandle {
    set Row=qHandle($increment(qHandle,-1))
  } else {
    set AtEnd=1
  }

  quit $$$OK
}

ClassMethod NewQuery1Close(ByRef qHandle As %Binary) As %Status [ PlaceAfter = NewQuery1Execute ]
{
  quit $$$OK
}

Storage Default
{
<Data name="testDefaultData">
<Value name="1">
<Value>%%CLASSNAME</Value>
</Value>
<Value name="2">
<Value>Name</Value>
</Value>
<Value name="3">
<Value>Age</Value>
</Value>
<Value name="4">
<Value>City</Value>
</Value>
<Value name="5">
<Value>Phone</Value>
</Value>
</Data>
<DataLocation>^dc.testD</DataLocation>
<DefaultData>testDefaultData</DefaultData>
<IdLocation>^dc.testD</IdLocation>
<IndexLocation>^dc.testI</IndexLocation>
<StreamLocation>^dc.testS</StreamLocation>
<Type>%Storage.Persistent</Type>
}

}

Result:

USER>d ##class(dc.test).Test()
^dc.testD=5
^dc.testD(1)=$lb("","Joe",44,"Boston","1-234-4567")
^dc.testD(2)=$lb("","Ron",48,"Dallas","1-234-5678")
^dc.testD(3)=$lb("","Eve",40,"Miami","1-234-4567")
^dc.testD(4)=$lb("","Tommy",50,"New York","1-345-6789")
^dc.testD(5)=$lb("","Alexa",35,"Portland","1-567-8901")
^dc.testD=5
^dc.testD(1)=$lb("","Joe",44,"Boston","1-234-4567")
^dc.testD(2)=$lb("","Ron",48,"Dallas","1-234-5678")
^dc.testD(3)=$lb("","Eve",40,"Miami","1-234-4567")
^dc.testD(4)=$lb("","Tommy",50,"New York","1-345-6789")
^dc.testD(5)=$lb("","Vitaliy",35,"Portland","1-567-8901")

With your permission I will add some useful links on the topic:

• Here can find much more discussion of this project/idea: Declarative development in Caché
• Another implementation of Declarative ObjectScript: Looking for the equivalent of first-class functions in the Caché DBMS
• Discussion of this idea/approach on a Russian-language forum (link to machine translation): A declarative approach to the development

Using my simple example, when the method receive a name param or a age param to build the where clause. I really don't know how to solve it using query class.

Query FilterBy(
  Name As %String = "",
  Age As %Integer = "") As %SQLQuery(CONTAINID = 1, SELECTMODE = "RUNTIME") [ SqlName = SP_Sample_Filter_By, SqlProc ]
{
SELECT TOP 5 ID, Name, Age, SSN FROM Sample.Person
WHERE 
(nvl(:Name,'')='' or Name %STARTSWITH :Name)
AND
(nvl(:Age,'')='' or Age >= :Age)
}

Run Examples:
select * from Sample.SP_Sample_Filter_By(,47)
select * from Sample.SP_Sample_Filter_By('',47)

select * from Sample.SP_Sample_Filter_By('s',47)

select * from Sample.SP_Sample_Filter_By('s')
select * from Sample.SP_Sample_Filter_By('s','')