Reading data from XML column is not possible after closing the ResultSet

[hareesh-kp]

Driver version

12.7.1.jre11-preview

SQL Server version

Microsoft SQL Server 2019

Client Operating System

Windows 11 Pro x64

JAVA/JVM version

openjdk 17.0.12

Table schema

create table XmlTable (xmldata xml )
insert into XmlTable(xmldata) values ('<d><data>data 1</data></d>')

Problem description

While reading SQLXML after closing the ResultSet, the code throws error. It works if the ResultSet is not closed.

Expected behavior

Even after closing the ResultSet, it should be possible to read data. CLOB type works this way.

Actual behavior

Cannot get data and getting the error "This SQLXML object has been freed. It can no longer be accessed."

Error message/stack trace

com.microsoft.sqlserver.jdbc.SQLServerException: This SQLXML object has been freed. It can no longer be accessed.
    at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDriverError(SQLServerException.java:242)
    at com.microsoft.sqlserver.jdbc.SQLServerSQLXML.checkReadXML(SQLServerSQLXML.java:195)
    at com.microsoft.sqlserver.jdbc.SQLServerSQLXML.getBinaryStream(SQLServerSQLXML.java:219)

Any other details that can be helpful

The issue was originally noticed while using Spring SimpleJdbcCall. Then found this closed issue https://github.com/microsoft/mssql-jdbc/issues/673. It looks related.

Code to reproduce the error

import java.io.IOException;
import java.nio.charset.StandardCharsets;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.SQLXML;
import java.sql.Statement;

public class App {

    private static final String CONNECTION_STRING = "jdbc:sqlserver://<>;";
    private static final String USERNAME  = "<>";
    private static final String PASSWORD = "<>";

    public static void main(String[] args) {
        xmlTest();
    }

    static void xmlTest() {
        String selectSql = "select xmldata from XmlTable";
        try (Connection connection = DriverManager.getConnection(CONNECTION_STRING, USERNAME, PASSWORD);
                Statement statement = connection.createStatement();
                ResultSet rs = statement.executeQuery(selectSql)) {
            rs.next();
            System.out.println("Reading data: ");
            SQLXML xml = rs.getSQLXML("xmldata");

            // this works
            // System.out.println(new String(xml.getBinaryStream().readAllBytes(), StandardCharsets.UTF_16LE));

            rs.close();

            // this throws error
            System.out.println(new String(xml.getBinaryStream().readAllBytes(), StandardCharsets.UTF_16LE));

        } catch (SQLException | IOException e) {
            e.printStackTrace();
        } 
    }

}

[tkyc]

Looking into it.. Have you tried any other driver versions where this is working? From reading the history on this, starting from issue 673, it looks like a regression.

[hareesh-kp]

Issue can be replicated using driver version 12.6.1 jre11 with JDK 21. I haven't checked any other versions.

[tkyc]

Isn't a regression. Looks like this never worked at all. Added enhancement label for feature triage.

[lilgreenbird]

I don't think this is an enhancement, the error message clearly tells what the issue is. In your repro code, the error occurs only AFTER the resultset is closed? Please reference the object before closing. This is as designed. Please let us know if you have further questions otherwise we will be closing this issue.

[hareesh-kp]

Please see issue #673 ( for CLOB ) which has been fixed years ago. It mentions #567 which is a similar issue with BLOB. As per the comment https://github.com/Microsoft/mssql-jdbc/issues/567#issuecomment-354912301, it looks like the behavior doesn't reflect JDBC specification. Is SQLXML behavior expected to be different?

[lilgreenbird]

hi @hareesh-kp

Thank you for the info. SQLXML datatype is not a type that's covered in the JDBC specs and the behavior you mentioned is not currently supported. I will add this as an enhancement request it will be considered along with other feature requests when we do planning for the next semester.

[hareesh-kp]

Thank you.

[harawata]

FYI, JDBC 4.3 Specification - page 140

Note – The closing of a ResultSet object does not close the Blob, Clob, NClob or SQLXML objects created by the ResultSet. Blob, Clob, NClob and SQLXML objects remain valid for at least the duration of the transation [sic] in which they are created, unless their free method is invoked.