---
title: 'XiYanSQL: A Multi-Stage Ensemble Text-to-SQL Framework'
url: https://www.emergentmind.com/topics/xiyansql
type: topic
---

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

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 [2411.08599][2507.04701].

## 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 [2411.08599].

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 [2507.04701].

## 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** [2411.08599][2507.04701].

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 [2507.04701].

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 \(\rightarrow\) tables \(\rightarrow\) 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 [2411.08599].

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 \(\mathcal{M}_s\),
\[
K = f_{\mathcal{M}_s}(Q,E),
\]
and column retrieval uses
\[
\text{Score}(k_i, c_j) = \left\langle \mathbf{V}_{Q||E}, \mathbf{V}_{Tab({c_j})} \right\rangle \cdot \left\langle \mathbf{V}_{k_i}, \mathbf{V}_{c_j} \right\rangle .
\]
The method keeps the top-\(k\) columns per keyword. For values, it first uses **edit distance** to get top-\(k\) 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 \(\mathcal{M}_s\) to select columns,
\[
S^{slct}_i \gets f_{\mathcal{M}_s}(S^{rtrv}, Q, E),
\]
identifies primary and foreign keys,
\[
P_i \gets \text{PFKeyIdentifier}(S^{slct}_i),
\]
and constructs successive schemas
\[
S_i \gets \left( \bigcup_{k=1}^{i-1} S_k \right) \cup S^{slct}_i \cup P_i .
\]
This yields multiple schemas with different precision-recall balances rather than a single schema view [2507.04701].

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 \(P=74.89\%\), \(R_c=83.64\%\), \(R_v=91.31\%\), whereas **XiYan-SQL schema \(S_2\)** has \(P=54.90\%\), \(R_c=89.77\%\), \(R_v=93.63\%\) [2411.08599][2507.04701].

## 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 [2411.08599][2507.04701].

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 [2411.08599][2507.04701].

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-\(K\) 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 [2411.08599].

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 [2411.08599].

The final stage is a learned **selector**. Instead of relying on self-consistency alone, XiYan-SQL executes the candidates, groups them by **execution consistency**,
\[
\mathcal{C}=\{C_1,\ldots,C_m\} \gets \text{groupby}(L,R),
\]
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,
\[
\arg_{l\in C_i''}\min |l|,
\]
and supplies the reorganized list to a fine-tuned selection model. The final prediction is written as
\[
l^* \gets f_{\mathcal{M}_c}(Q, \mathcal{S}^{un}, E, L').
\]
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 [2411.08599][2507.04701].

## 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 [2411.08599][2507.04701].

| 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** [2411.08599].

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 \(s_3\) is best, reaching **72.29%** at **5** candidates and **73.34%** at **10** candidates. The candidate-generation analysis further reports that at \(p_l=10\), the upper bound reaches **82.2%**, indicating substantial headroom if selection is sufficiently strong [2507.04701].

## 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 [2507.10382].

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 [2507.10382].

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 [2411.08599][2507.04701].

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 [2411.08599][2507.04701].

Source: https://www.emergentmind.com/topics/xiyansql