Programming with SQLXML
This section describes how to use the Microsoft JDBC Driver for SQL Server API methods to store and retrieve an XML document in and from a relational database with SQLXML objects.
This section also contains information about the types of SQLXML objects and provides a list of important guidelines and limitations when using SQLXML objects.
Reading and writing XML data with SQLXML objects
The following list describes how to use the Microsoft JDBC Driver for SQL Server API methods to read and write XML data with SQLXML objects:
To create a SQLXML object, use the createSQLXML method of the SQLServerConnection class. Note that this method creates a SQLXML object without any data. To add xml data to SQLXML object, call one of the following methods that are specified in the SQLXML interface: setResult, setCharacterStream, setBinaryStream, or setString.
To retrieve the SQLXML object itself, use the getSQLXML methods of the SQLServerResultSet class or the SQLServerCallableStatement class.
To retrieve the xml data from a SQLXML object, use one of the following methods that are specified in the SQLXML interface: getSource, getCharacterStream, getBinaryStream, or getString.
To update the xml data in a SQLXML object, use the updateSQLXML method of the SQLServerResultSet class.
To store a SQLXML object in a database table column of type xml, use the setSQLXML methods of the SQLServerPreparedStatement class or the SQLServerCallableStatement class.
The example code in SQLXML data type sample demonstrates how to perform these common API tasks.
Readable and writable SQLXML objects
The following table lists which types of SQLXML objects are supported by the setter, getter, and updater methods provided by the JDBC API. The columns in the table refer to the following:
The Method Name column lists the supported getter, setter, and updater methods in the JDBC API.
The Getter SQLXML Object column represents a SQLXML object, which is created by either the getSQLXML method of the SQLServerCallableStatement class or the getSQLXML method of the SQLServerResultSet class.
The Setter SQLXML Object column represents a SQLXML object, which is created by the createSQLXML method of the SQLServerConnection class. Note that the setter methods below accept only a SQLXML object created by the createSQLXML method.
| Method Name | Getter SQLXML Object (Readable) |
Setter SQLXML Object (Writable) |
|---|---|---|
| CallableStatement.setSQLXML() | Not Supported | Supported |
| CallableStatement.setObject() | Not Supported | Supported |
| PreparedStatement.setSQLXML() | Not Supported | Supported |
| PreparedStatement.setObject() | Not Supported | Supported |
| ResultSet.updateSQLXML() | Not Supported | Supported |
| ResultSet.updateObject() | Not Supported | Supported |
| ResultSet.getSQLXML() | Supported | Not Supported |
| CallableStatement.getSQLXML() | Supported | Not Supported |
As shown in the table above, the setter SQLXML methods will not work with the readable SQLXML objects; similarly, the getter methods will not work with the writable SQLXML objects.
If the application invokes the setObject method by specifying a scale or a length parameter with a SQLXML object, the scale or length parameter is ignored.
Guidelines and limitations when using SQLXML objects
Applications can use SQLXML objects to read and write the XML data from and to the database. The following list provides information about specific limitations and guidance when using SQLXML objects:
A SQLXML object can be valid only for the duration of the transaction in which it was created.
A SQLXML object received from a getter method can only be used to read data.
A SQLXML object created by the connection object can only be used to write data.
The application can invoke only one getter method on a readable SQLXML object to read data. After the getter method is invoked, all other getter or setter methods on the same SQLXML object fail.
The application can invoke only the free method on the SQLXML object after it is read or written to. However, it is still possible to process the returned stream or source as long as the underlying column or parameter is active. If the underlying column or parameter becomes inactive, the stream or source associated with the SQLXML object will be closed. If the underlying column or parameter is no longer valid, the underlying data will not be available for the Stream, Simple API for XML (SAX), and Streaming API for XML (StAX) getters.
The application can invoke only one setter method on a writable SQLXML object. After the setter method is invoked, all other setter or getter methods on the same SQLXML object fail.
To set data on the SQLXML object, the application must use the appropriate setter method and the functions in the returned object.
The getSQLXML methods of the SQLServerCallableStatement class and the SQLServerResultSet class returns null data if the underlying column is null.
The setter objects can be valid through the connection they are created within.
Applications are not allowed to set a null value by using the setter methods provided by the SQLXML interface. The applications can set an empty string ("") by using the setter methods provided in the SQLXML interface. To set a null value, the applications should call one of the following:
The setNull methods of the SQLServerCallableStatement class and SQLServerPreparedStatement class.
The setObject methods of the SQLServerCallableStatement class and SQLServerPreparedStatement class.
The setSQLXML methods of the SQLServerCallableStatement class and SQLServerPreparedStatement class with a null parameter value.
When working with XML documents, we recommend using Simple API for XML (SAX) and Streaming API for XML (StAX) parsers instead of Document Object Model (DOM) parsers for performance reasons.
XML parsers cannot handle empty values. However, SQL Server allows applications to retrieve and store empty values from and to database columns of the XML data type. That means that when parsing the XML data, if the underlying value is empty, an exception is thrown by the parser. For DOM outputs, the JDBC driver catches that exception and throws an error. For SAX and Stax outputs, the error comes from the parser directly.
Adaptive buffering and SQLXML support
The binary and character streams returned by the SQLXML object obey the adaptive or full buffering modes. On the other hand, if the XML parsers are not streams, they will not obey the adaptive or full settings. For more information about adaptive buffering, see Using Adaptive Buffering.