We will discuss the procedure to create and initialize an SQLXML object. Import the java.sql package, and the javax.xml.stream package:

import java.sql.*;
import javax.xml.stream.*;

The Java representation of an XML document in a database table is the SQLXML object. Create an SQLXML object from the Connection object with the createSQLXML() method, as shown below:

SQLXML sqlXML=connection.createSQLXML();

An SQLXML object can be initialized using one of the setString(), setBinaryStream(), setCharacterStream(), or setResult() methods. An SQLXML object can be initiated using the setResult() method and the StAXResult class. Create an XMLStreamWriter object from a StAXResult object, as shown below:

StAXResult staxResult = sqlXML.setResult(StAXResult.class);
XMLStreamWriter xmlStreamWriter = staxResult.getXMLStreamWriter();

The SQLXML object becomes non-writable after the setResult()method is invoked. Add the start of an XML document with the writeStartDocument(String,String) method, as shown below:

xmlStreamWriter.writeStartDocument("UTF-8","1.0");

The encoding and version of the XML document is specified in the writeStartDocument method. Add the start of an element with the writeStartElement(String localName) method, as shown below:

xmlStreamWriter.writeStartElement("catalog");

Add the element attributes by using the writeAttribute(String localName, String value) method. Add an element of text by using the writeCharacters(String text) method. Each start element would have a corresponding end element tag. Add an end element by using the writeEndElement() method. The writeEndElement() method does not specify the element name as the writeStartElement(String) method:

xmlStreamWriter.writeEndElement();

Add end of the document by using the writeEndDocument() method:

xmlStreamWriter.writeEndDocument();

A SQLXML object can also be initiated using the SAXResult class. Create a SAXResult object using the setResult() method of the SQLXML interface. Subsequently, obtain the ContentHandler result using the getHandler() method:

SAXResult saxResult = sqlXML.setResult(SAXResult.class);
ContentHandler contentHandler= saxResult.getHandler();

Specify the start of an XML document using the startDocument() method:

contentHandler.startDocument();

Specify the start of an element using the startElement(String uri,String localName,String qName,Attributes atts) method in which the parameter uri specifies the element namespace, parameter localName specifies the element local name, parameter qName specifies the element qualified name and parameter atts of type Attributes specifies the element attributes. An Attributes object can be created using the org.xml.sax.helpers.AttributesImpl class, which implements the Attributes interface. An attribute can be added to the AttributesImpl object using the addAttribute(String uri, String localName, String qName, String type, String value) method:

AttributesImpl.AttributesImpl() attrs=new AttributesImpl();
attrs.addAttribute("","","journal","StringType","OracleMagazine");
contentHandler.startElement("","","catalog",attrs);

The end of an element is specified with the endElement(String uri,String localName,String qName) method. Also specify the end of the document with the endDocument() method:

contentHandler.endElement("","","catalog");
contentHandler.endDocument();

An SQLXML object can also be initiated using the setCharacterStream() method. Create a Writer object from the SQLXML object using the setCharacterStream() method. Create a BufferedReader object from an input XML file. Read from the BufferedReader, and output to the Writer object:

Writer writer= sqlXML.setCharacterStream();
BufferedReader bufferedReader = new BufferedReader(new FileReader(new File("C:/catalog.xml")));
String line= null;
while((line = bufferedReader.readLine() != null) {
writer.write(line);
}

The SQLXML object becomes non-writable after the setCharacterStream() method is invoked. An XML document can also be added to an SQLXML object with the setString() method, as shown below:

sqlXML.setString("xmlString");

The SQLXML object becomes non-writable after invoking the setString() method. If the setString(), setBinaryStream(), setCharacterStream(), or setResult() method is invoked on an SQLXML object that has been previously initiated, a SQLException is generated. If any of the setBinaryStream(), setCharacterStream(), or setResult() methods are invoked more than once, a SQLException is generated, and the previously returned InputStream, Writer, or Result object is not effected.

The SQLXML Java data type is stored in an XML document, just like any other Java data type. Create a database table with an XML type column. Run the SQL statement to create a database table, and obtain a Statement object from the Connection object, as shown below:

Statement stmt=connection.createStatement();

Create a database table, Catalog with an XML type column, as shown below:

stmt.executeUpdate("CREATE Table Catalog(CatalogId int, Catalog XML)");

Create a PreparedStatement object to add values to a database table, as shown in the following listing:

PreparedStatement statement=connection.prepareStatement("INSERT INTO CATALOG(catalogId, catalog) VALUES(?,?)");

Set the int value with the setInt() method and the SQLXML value with the setSQLXML() method, as shown below:

stmt.setInt(1, 1);
stmt.setSQLXML(2, sqlXML);

Update the database with the executeUpdate() method:

stmt.executeUpdate();

An XML database data type row is retrieved as an SQLXML Java data type. Create a PreparedStatement for a SELECT query, as shown below:

PreparedStatement stmt=connection.prepareStatement("SELECT * FROM CATALOG WHERE catalogId=?");

Specify the catalogId value for which an XML document is to be retrieved:

stmt.setInt(1, 1);

Obtain a result set with the executeQuery() method:

ResultSet rs=stmt.executeQuery();

Obtain the SQLXML object for the catalog column of type XML, as shown below:

SQLXML sqlXML=rs.getSQLXML("Catalog");

Output the XML document in the SQLXML object by using the getString() method:

System.out.println(sqlXML.getString());

The XMLStreamReader interface can be used to read an XML document with an event iterator. An XMLStreamReader object is obtained from a SQLXML object, as shown below:

InputStream binaryStream = sqlXML.getBinaryStream();
XMLInputFactory factory = XMLInputFactory.newInstance();
XMLStreamReader xmlStreamReader = factory.createXMLStreamReader(binaryStream);

The SQLXML object becomes non-readable after calling the getBinaryStream() method. The next event is obtained by using the next() method, as shown below:

while(xmlStreamReader.hasNext())
{
int parseEvent=xmlStreamReader.next();
}

The next() method returns an int value that corresponds to an XMLStreamConstants constant, which represents an event type. Some of the return values of the next() method are listed in following table:

If the return value is ELEMENT, then the local name, prefix, and namespace can be obtained by using the getLocalName(), getPrefix(), and getNamespaceURI() methods, as shown below:

System.out.println("Element Local Name: "+xmlStreamReader.getLocalName());
System.out.println("Element Prefix: "+xmlStreamReader.getPrefix());
System.out.println("Element Namespace:"+xmlStreamReader.getNamespaceURI());

The attribute count in an element is obtained by using the getAttributeCount() method. Iterate over the attributes and obtain the attribute local name by using the getAttributeLocalName() method, the attribute value with the getAttributeValue() method, the attribute prefix with the getAttributePrefix() method, and the attribute namespace with the getAttributeNamespace() method:

for(int i=0; i<xmlStreamReader.getAttributeCount();i++){
System.out.println("Attribute Prefix:"+xmlStreamReader.getAttributePrefix(i));
System.out.println("Attribute Namespace:"+xmlStreamReader.getAttributeNamespace(i));
System.out.println("Attribute Local Name:"+xmlStreamReader.getAttributeLocalName(i));
System.out.println("Attribute Value:"+xmlStreamReader.getAttributeValue(i));
}