Not able to fetch execution plan by setting `SET SHOWPLAN_XML ON` using mssql

I want to fetch the estimated execution plan of a query using the MSSQL package using Nodejs. As per the document https://learn.microsoft.com/en-us/sql/t-sql/statements/set-showplan-xml-transact-sql?view=sql-server-ver16 If I set the SHOWPLAN_XML to ON before the query execution it will return the execution plan for that query and all other consecutive queries until the SHOWPLAN_XML is set back to OFF for that particular session.

I tried to add the same thing, unfortunately I am not able to retrieve the XML execution plan for the query even after setting the SHOWPLAN_XML as ON.

Expected behaviour:

Running query:

Query 1: SET SHOWPLAN_XML ON Query 2: select top(1) UUID from users where UUID is not null; Query 3: SET SHOWPLAN_XML OFF

Should return the execution XML plan for the SELECT query, but instead returns the actual result for Select Query.

Configuration:

Here is the SAMPLE code file which will show multiple options I tried to make this work but wasn't successful. Can someone please check this and let me know what I am missing here.

const sql = require("mssql");

const config = {
    user: '<username>',
    password: '<password>',
    server: '<server>',
    database: '<database>',
    pool: {
        max: 100,
        min: 1,
        idleTimeoutMillis: 30000
    }
};

const pool = new sql.ConnectionPool(config, err => {    
    if (err) {
        console.error(err);
        return;
    }
});

let request;
async function runQuery(query) {
    try {
        return await request.query(query);
    } catch (err) {
        console.error(err);
    }
}

// Option 1 to run all 3 statements in single query with GO statement to allow the SET SHOWPLAN_XML to be executed in different instance as mentioned in the document.
// Desired OUTPUT is that the query will not be executed and the result will print the XML execution plan,
// but instead the query returns an error "Incorrect syntax near 'GO'."
// async function getData() {
//     const result = await runQuery(`SET SHOWPLAN_XML ON;
//         GO
//         select top(1) UUID from users where UUID is not null;
//         GO
//         SET SHOWPLAN_XML OFF;`);
//     console.log("🚀 ~ getData ~ result", result);
// }

// Option 2 to run all 3 statements in single query.
// Desired OUTPUT is that the query will not be executed and the result will print the XML execution plan,
// but instead the query returns an error "The SET SHOWPLAN statements must be the only statements in the batch".
// async function getData() {

//     const result = await runQuery(`SET SHOWPLAN_XML ON;
//         select top(1) UUID from users where UUID is not null;
//         SET SHOWPLAN_XML OFF;`);
//     console.log("🚀 ~ getData ~ result", result);

// }

// Option 3 to run all 3 queries individually back to back.
// First set the SHOWPLAN_XML as ON, then run the desired query for which the XML plan is required and then set the SHOWPLAN_XML as OFF.
// Desired OUTPUT is that the query will not be executed and the result will print the XML execution plan, but instead the query is executed and returns the result.
async function getData() {
    const sessionId = await runQuery(`select @@SPID`);
    console.log("🚀 ~ getData ~ sessionId", sessionId)

    await runQuery(`SET SHOWPLAN_XML ON`);
    const result = await runQuery(`select top(1) UUID from users where UUID is not null;`);
    console.log("🚀 ~ getData ~ result", result);

    await runQuery(`SET SHOWPLAN_XML OFF`);

    const sessionId1 = await runQuery(`select @@SPID`);
    console.log("🚀 ~ getData ~ sessionId1", sessionId1);
}

(async () => {
    await pool.connect();
    request = pool.request();

    getData();
})();

Software versions

NodeJS: 14+
node-mssql: 8.1.0
SQL Server: Microsoft SQL Azure (RTM) - 12.0.2000.8

tediousjs / node-mssql