XiYanSQL: A Multi-Stage Ensemble Text-to-SQL Framework

• XiYanSQL is a multi-generator ensemble framework designed to convert natural language questions into syntactically valid SQL queries across diverse databases.
• It employs M-Schema for compact schema representation along with multi-path retrieval and iterative column selection to boost query precision.
• Empirical studies and ablation results demonstrate that combining schema filtering, diverse candidate generation, and learned selection significantly enhances performance.

XiYanSQL, usually styled XiYan-SQL in the core papers, is a multi-generator ensemble framework for text-to-SQL (NL2SQL) that addresses a central weakness of modern LLM-based text-to-SQL systems: no single prompting or fine-tuned model is reliably best across all databases, SQL dialects, and query complexities. The framework combines schema filtering or schema linking, a multi-generator candidate-production stage, post-generation refinement, and a learned selector, with M-Schema as a schema representation designed to make database structure easier for LLMs to understand than raw DDL (Gao et al., 2024, Liu et al., 7 Jul 2025).

1. Problem setting and conceptual motivation

XiYan-SQL is positioned against two failure modes in contemporary text-to-SQL systems. Pure prompt-engineering approaches often rely on many sampled candidates and self-consistency, which can be expensive and still fail when the majority answer is wrong. Pure supervised fine-tuning can be more controllable, but smaller models may struggle to generalize to unseen schemas or complex reasoning. The framework is explicitly motivated by the observation that text-to-SQL systems must simultaneously understand the user question, map question mentions to database entities, reason over schema structure and joins, generate syntactically valid SQL in the correct dialect, and choose the best query among many plausible candidates (Gao et al., 2024).

The later full paper sharpens that position by arguing that neither prompt engineering alone nor single-model generation is enough for difficult benchmarks such as BIRD and Spider. In that formulation, XiYan-SQL is best understood as a three-stage ensemble system: a Schema Filter that reduces schema noise, Multiple SQL Generation that increases the chance that at least one candidate is correct, and SQL Selection that replaces raw majority voting with a trained selector (Liu et al., 7 Jul 2025).

2. System architecture and formal workflow

The preview paper summarizes the architecture as three main components: schema linking, candidate generation, and candidate selection, and describes the pipeline as a three-agent workflow in which M-Schema is the schema representation supplied to the LLMs. The later paper renames and formalizes the stages as Schema Filter, Multiple SQL Generation, and SQL Selection (Gao et al., 2024, Liu et al., 7 Jul 2025).

In the full formulation, the framework takes a natural-language question $Q$, optional evidence $E$, and schema $S$, and outputs a final SQL query $l^*$. The pipeline first produces multiple filtered schemas $\mathcal{S}=\{S_1,\dots,S_{p_s}\}$, then generates candidates across schemas and generators, and finally selects the best candidate. The number of candidate SQLs is defined as

$p_l = p_s \times p_m$

where $p_m$ is the number of generators. In the reported main setup, $p_s = 2$, $p_m = 5$, and therefore $p_l = 10$. The implementation details identify Qwen2.5-Coder-32B as the basis of the final fine-tuned generators, Qwen2.5-Coder-7B as the selection model, and one GPT-4o-based generator as the ICL component (Liu et al., 7 Jul 2025).

This organization turns the task from single-pass decoding into a staged decision process. The factual structure is important: XiYan-SQL is not described as a single decoder with a better prompt, but as an end-to-end pipeline in which schema reduction, candidate diversity, execution-driven revision, and learned selection are coupled.

3. M-Schema and schema filtering

One of XiYan-SQL’s main contributions is M-Schema, described in the preview paper as a semi-structured schema representation intended to be more informative than raw DDL and more compact than prior MAC-SQL-style representations. M-Schema explicitly encodes the hierarchy database $E$0 tables $E$1 columns using markers such as 【DB_ID】, # Table, and 【Foreign Keys】. For each table, it can include the table name and description; for each column, it includes the column name, data type, description, primary key marker, and example values; and foreign keys are listed explicitly because they are crucial for join reasoning. Relative to MAC-SQL Schema, M-Schema adds data types and primary key markings, simplifies the value-example format, and removes leading spaces to make the prompt cleaner and more compact (Gao et al., 2024).

The later XiYan-SQL paper expands the schema stage into a Schema Filter with two subparts: multi-path retrieval and iterative column selection. Keyword extraction is performed by an out-of-the-box LLM $E$2,

$E$3

and column retrieval uses

$E$4

The method keeps the top-$E$5 columns per keyword. For values, it first uses edit distance to get top-$E$6 similar values, then tokenizes value text together with column metadata using a RoBERTa tokenizer, uses Locality Sensitive Hashing (LSH) to efficiently filter candidate value text, and finally applies cosine similarity with a threshold. Iterative selection then repeatedly prompts $E$7 to select columns,

$E$8

identifies primary and foreign keys,

$E$9

and constructs successive schemas

$S$0

This yields multiple schemas with different precision-recall balances rather than a single schema view (Liu et al., 7 Jul 2025).

The preview paper reports that using M-Schema improved performance over DDL Schema consistently across GPT-4o, DeepSeek, Claude 3.5 Sonnet, and Gemini 1.5 Pro, with an average gain over DDL of 2.03%. The full paper’s schema-filter analysis on BIRD dev reports that different iterations intentionally produce different trade-offs: XiYan-SQL schema $S$1 has $S$2, $S$3, $S$4, whereas XiYan-SQL schema $S$5 has $S$6, $S$7, $S$8 (Gao et al., 2024, Liu et al., 7 Jul 2025).

4. Candidate generation, refinement, and learned selection

The candidate-generation stage combines supervised fine-tuning and in-context learning. In the preview paper, the supervised strategy is explicitly two-stage and multi-task. The first stage, basic-syntax training, fine-tunes a pretrained model on tens of thousands of SQL-dialect-agnostic examples focused on basic and single-pattern SQL syntax. The second stage, generation-enhance training, adds multi-task data and syntactic preference data, including question-to-SQL, SQL-to-question, SQL-to-evidence, SQL discrimination, and regeneration based on execution feedback. The full paper gives the auxiliary-task perspective as Standard Text-to-SQL, Question inference task, Evidence inference task, and Self-refine task, and states that the question inference task is the most beneficial among the auxiliary tasks, though the tasks are complementary (Gao et al., 2024, Liu et al., 7 Jul 2025).

Diversity is produced not only by using multiple generators, but by training them with different SQL formats and generation preferences. The full paper describes this as multi-format SQL enhancement training, covering both writing pattern diversity and presentation/style diversity. One generator may prefer more complex or chunked SQL structures, while another may produce more standardized formatting. The preview paper presents the same goal as constructing multiple fine-tuned models with diverse preferences so the ensemble can cover diverse query forms and reasoning behaviors (Gao et al., 2024, Liu et al., 7 Jul 2025).

The ICL generator is designed to improve diversity while avoiding overemphasis on named entities. XiYan-SQL uses skeleton similarity: it identifies named entities in the user question using NLTK, masks entities of the same type with special tokens such as <country>, and may replace enumeration values by the corresponding column names. The modified questions are embedded and the top-$S$9 closest training examples are selected. When a question involves multiple tables, the selected SQL examples are restricted to multi-table queries, and up to 5 examples are used depending on similarity and table-count constraints. For Bird and Spider, where train and test databases do not overlap, the prompt includes the schema of the examples as well, but only the minimal necessary columns (Gao et al., 2024).

After generation, XiYan-SQL applies a refiner that takes schema-related context, the generated SQL, and execution results or error messages, and asks the model to produce a corrected version. This module can fix malformed syntax, missing joins, incorrect conditions, and other execution-time issues. The refinement can be iterative, so it is a second-pass generation step driven by observed failure signals rather than a simple grammar check (Gao et al., 2024).

The final stage is a learned selector. Instead of relying on self-consistency alone, XiYan-SQL executes the candidates, groups them by execution consistency,

$l^*$0

sorts clusters by cluster size, and sorts within clusters by generator quality. If one cluster dominates, all candidates are presented in cluster order; if no cluster dominates, the method keeps the shortest SQL in each cluster,

$l^*$1

and supplies the reorganized list to a fine-tuned selection model. The final prediction is written as

$l^*$2

The full paper states that LLMs are sensitive to order and that reorganization places the most promising candidates first; the preview paper states that the model is fine-tuned to discriminate among nuanced candidate queries rather than prompted in a purely zero-shot fashion (Gao et al., 2024, Liu et al., 7 Jul 2025).

5. Empirical performance and ablation evidence

The reported results span an earlier preview and a later full paper. The preview evaluates XiYan-SQL on Spider, Bird, SQL-Eval, and NL2GQL, whereas the later paper focuses on BIRD and Spider. The preview’s detailed description states that the Bird test set is not publicly available and therefore reports 72.23% on Bird development; the later paper reports 75.63% execution accuracy and 71.41% R-VES on BIRD test (Gao et al., 2024, Liu et al., 7 Jul 2025).

Context	Benchmark or task	Reported result
Preview paper	Spider test EX	89.65%
Preview paper	Bird development EX	72.23%
Preview paper	SQL-Eval EX	69.86%
Preview paper	NL2GQL EX	41.20%
Full paper	BIRD test EX	75.63%
Full paper	Spider test EX	89.65%
Full paper	BIRD test R-VES	71.41%

The ablations clarify which components carry the gain. In the preview paper’s Bird analysis, removing the fine-tuned generator drops execution accuracy by 2.91 points, removing the ICL generator drops it by 1.31, removing the refiner drops it by 0.55, and removing the selection model drops it by 2.74. In that setting, the all system reaches 71.58% with three candidates and 72.23% with five candidates. The preview also reports a schema-linking ablation in which using all tables, columns, and examples without linking yields precision 10.14%, recall 100%, and EX 57.95%, while schema linking raises precision to 74.74%, keeps recall at 95.47%, and raises EX to 60.10. The M-Schema ablation shows GPT-4o moving from 55.67 with DDL to 57.30 with MAC-SQL to 57.95 with M-Schema; Claude 3.5 Sonnet from 49.74 to 50.26 to 51.04; Gemini 1.5 Pro from 49.22 to 52.41 to 52.15; and DeepSeek from 53.52 to 55.28 to 55.15 (Gao et al., 2024).

The later paper provides a fuller end-to-end ablation on BIRD dev. Full XiYan-SQL achieves 73.34; removing the Schema Filter yields 72.10, a drop of 1.24; using only one SFT generator yields 69.30, a drop of 4.04; and using only majority voting yields 70.21, a drop of 3.13. For auxiliary-task training on Qwen2.5-Coder-32B, a plain SFT generator reaches 64.67, while adding question inference + evidence inference + self-refine raises it to 66.88. The selection analysis shows that candidate reorganization strategy $l^*$3 is best, reaching 72.29% at 5 candidates and 73.34% at 10 candidates. The candidate-generation analysis further reports that at $l^*$4, the upper bound reaches 82.2%, indicating substantial headroom if selection is sufficiently strong (Liu et al., 7 Jul 2025).

6. External adoption, limitations, and technical significance

XiYanSQL has also been used as an external component in a schema-level RAG system for urban mobility simulation and analysis. In that work, XiYanSQL is described as an external, open-source schema-aware SQL generation model from the XiYanSQL project that the authors adopted because it ranks highly on BirdSQL and exposes a public API. The RAG pipeline converts the relational schema into M-Schema, embeds schema descriptions with all-MiniLM-L6-v2, stores them in a local Chroma vector database, retrieves the top-3 most relevant schema documents by cosine similarity, and concatenates them into the prompt context for SQL generation. XiYanSQL is compared against GPT-4, GPT-4o, Gemini 1.5 Pro, and Gemini 2.0 Flash, and achieves execution accuracy 0.81 on system-operator queries and 0.98 on user queries (Ding et al., 14 Jul 2025).

That deployment also exposes failure modes. The error analysis partitions mistakes into Query Structure Differences (QSD), Query Logic Errors (QLE), Result Precision Errors (RPE), and Result Granularity Errors (RGE). For XiYanSQL on system-operator queries, QSD accounts for 47% of its total errors. The same paper emphasizes that the comparison is among LLM backends within the same schema-level RAG pipeline, not an ablation against a non-RAG baseline, and it explicitly cautions that LLM outputs should be treated as decision-support tools rather than definitive answers (Ding et al., 14 Jul 2025).

The core XiYan-SQL papers likewise identify practical limits. The full paper notes that diversity is still limited even with multiple fine-tuned models, that the framework is resource intensive, that training Qwen2.5-Coder-32B for generation takes about 45 GPU hours and approximately 180M tokens, that the selector takes about 15 GPU hours and approximately 50M tokens, and that inference latency for the full pipeline with 10 candidates is about 40.5 seconds on BIRD dev. The preview paper states that Bird remains difficult and that many top-performing systems are prompt-engineering-heavy, while also showing that smaller or fine-tuned models can still be highly competitive when training is carefully designed (Gao et al., 2024, Liu et al., 7 Jul 2025).

Taken together, the published record presents XiYan-SQL as a structured attempt to turn text-to-SQL into a robust multi-stage decision problem: represent the schema better, generate multiple diverse candidates, refine them using feedback, and then train a selector to choose the best query. This suggests that its strongest contribution lies less in a single model architecture than in the coupling of schema-aware reduction, controlled diversity, execution-driven repair, and order-aware learned selection across relational and non-relational settings (Gao et al., 2024, Liu et al., 7 Jul 2025).