# How to: use Recordset variable and to\_json function for populating a tree node
In order to generate json data from database, record set variable and to\_json function can be used as shown in the example below,
Record set must ;
* have at least 3 columns: "id", "text", "parent".
* If actual table column name is different from "id", "text" and "parent", column alias can be used as showed above.
Record set can optionally have;
* "children" column.\
As stated in jsTree documentation, recorset's column value must be either "true" or "false". True means the node has child(s) node, false means node is a leaf node and cannot be expanded.
### Example:
**SQLX File:**
**Form File:**
```
HT/SQL SHOWCASE (LOCATIONS TREE VIEW)
|-|
|-|
|-|
|-|
COUNTRY ID
COUNTRY NAME
REGION ID
PROVINCE ID
PROVINCE NAME
COUNTRY ID
COUNTY ID
COUNTY NAME
PROVINCE ID
```
**SQLX File:**
```
void page.load() {
populate_tree('cbTree.data');
}
void provinces.post_delete() {
refresh_tree_node('cbTree.data', :Session.selectedNodeParentId);
}
void provinces.post_insert() {
refresh_tree_node('cbTree.data', :Session.selectedNodeId);
}
void provinces.post_update() {
refresh_tree_node('cbTree.data', :Session.selectedNodeParentId);
}
void countries.post_delete() {
refresh_tree_node('cbTree.data', :Session.selectedNodeParentId);
}
void countries.post_insert() {
refresh_tree_node('cbTree.data', :Session.selectedNodeId);
}
void countries.post_update() {
refresh_tree_node('cbTree.data', :Session.selectedNodeParentId);
}
void counties.post_delete() {
refresh_tree_node('cbTree.data', :Session.selectedNodeParentId);
}
void counties.post_insert() {
refresh_tree_node('cbTree.data', :Session.selectedNodeId);
}
void counties.post_update() {
refresh_tree_node('cbTree.data', :Session.selectedNodeParentId);
}
void cbTree.data.tree_node_selected(char node_id, char parent_id, char node_type) {
:Session.selectedNodeId = node_id;
:Session.selectedNodeParentId = parent_id;
/* message('node_id : ' || node_id || '. node_type : ' || node_type); */
if (node_id == 'no_region') then
show_page('locations_grid.frmx');
end if;
hide_block('countries');
hide_block('provinces');
hide_block('counties');
if (node_type == 'R') then
refresh_block('countries', 'region_id = :node_id');
show_block('countries');
end if;
if (node_type == 'C') then
refresh_block('provinces', 'country_id = :node_id');
show_block('provinces');
end if;
if (node_type == 'P') then
refresh_block('counties', 'province_id = :node_id');
show_block('counties');
end if;
}
char cbTree.data.tree_node_expanded(char node_id, char node_type) {
char json;
/* message('node_id : ' || node_id || '. node_type : ' || node_type); */
if (node_id == '#') then
rs = select
region_id as id,
region_name as text,
'#' as parent,
'R' as type,
case (select count(*) from htsql.countries c where c.region_id = r.region_id)
when 0 then
'false'
else
'true'
end children
from
htsql.regions r;
if (rs) then
/* */
json = to_json(rs);
else
json = '[ { "id" : "no_region", "parent" : "#", "text" : "Click here to add new region" } ]';
end if;
end if;
if (node_type == 'R') then
rs = select
country_id as id,
country_name as text,
cc.region_id as parent,
'C' as type,
case (select count(*) from htsql.countries c where c.region_id = cc.region_id)
when 0 then
'false'
else
'true'
end children
from
htsql.countries cc
where
cc.region_id = :node_id;
json = to_json(rs);
end if;
if (node_type == 'C') then
rs = select
PROVINCE_ID as id,
PROVINCE_name as text,
country_id as parent,
'P' as type,
case (select count(*) from htsql.counties c where c.province_id = cc.province_id)
when 0 then
'false'
else
'true'
end children
from
htsql.provinces cc
where
cc.country_id = :node_id;
json = to_json(rs);
end if;
if (node_type == 'P') then
rs = select
county_id as id,
county_name as text,
province_id as parent,
'false' as children
from
htsql.counties c
where
c.province_id = :node_id;
json = to_json(rs);
end if;
return json;
}
```
### See also:
For detail on json data format :
---
# Agent Instructions: Querying This Documentation
If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question.
Perform an HTTP GET request on the current page URL with the `ask` query parameter:
```
GET https://muradkarakas.gitbook.io/sodium_documentation/how-to/how-to-use-recordset-variable-and-to_json-function-for-populating-a-tree-node.md?ask=
```
The question should be specific, self-contained, and written in natural language.
The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.
Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.