| title | description | author | ms.author | ms.date | ms.service | ms.subservice | ms.topic |
|---|---|---|---|---|---|---|---|
SQLXML data type sample |
This JDBC Driver for SQL Server sample application demonstrates how to store, retrieve, and parse XML data from a database with the **SQLXML** Java data type. |
David-Engel |
v-davidengel |
04/20/2021 |
sql |
connectivity |
conceptual |
[!INCLUDEDriver_JDBC_Download]
This [!INCLUDEjdbcNoVersion] sample application demonstrates how to store XML data in a relational database, how to retrieve XML data from a database, and how to parse XML data with the SQLXML Java data type.
The code examples in this section use a Simple API for XML (SAX) parser. The SAX is a publicly developed standard for the events-based parsing of XML documents. It also provides an application programming interface for working with XML data. Applications can use any other XML parser as well, such as the Document Object Model (DOM) or the Streaming API for XML (StAX), or so on.
The Document Object Model (DOM) provides a programmatic representation of XML documents, fragments, nodes, or node-sets. It also provides an application programming interface for working with XML data. Similarly, the Streaming API for XML (StAX) is a Java-based API for pull-parsing XML.
Important
In order to use the SAX parser API, you must import the standard SAX implementation from the javax.xml package.
The code file for this sample is named SqlXmlDataType.java, and it can be found in the following location:
\<installation directory>\sqljdbc_<version>\<language>\samples\datatypesTo run this sample application, you must set the classpath to include the sqljdbc4.jar file. If the classpath is missing an entry for sqljdbc4.jar, the sample application throws the "Class not found" exception. For more information about how to set the classpath, see Using the JDBC Driver.
You also need access to the [!INCLUDEssSampleDBnormal] sample database to run this sample application.
In the following example, the sample code makes a connection to the [!INCLUDEssSampleDBnormal] database and then calls the createSampleTables method.
The createSampleTables method drops the test tables, TestTable1, and TestTable2, if they exist. Then, it inserts two rows into TestTable1.
Also, the code sample includes the following three methods and one other class, which is named ExampleContentHandler.
The ExampleContentHandler class implements a custom content handler, which defines methods for parser events.
The showGetters method demonstrates how to parse the data in the SQLXML object by using the SAX, ContentHandler, and XMLReader. First, the code sample creates an instance of a custom content handler, which is ExampleContentHandler. Next, it creates and executes an SQL statement that returns a set of data from TestTable1. Then, the code example gets a SAX parser and parses the XML data.
The showSetters method demonstrates how to set the xml column by using the SAX, ContentHandler, and ResultSet. First, it creates an empty SQLXML object by using the createSQLXML method of the Connection class. Then, it gets an instance of a content handler to write the data into the SQLXML object. Next, the code example writes the data to TestTable1. Finally, the sample code iterates through the rows of data that are in the result set, and uses the getSQLXML method to read the XML data.
The showTransformer method demonstrates how to get XML data from one table and insert that XML data into another table by using the SAX and the Transformer. First, it retrieves the source SQLXML object from the TestTable1. Then, it creates an empty destination SQLXML object by using the createSQLXML method of the Connection class. Next, it updates the destination SQLXML object and writes the XML data to TestTable2. Finally, the sample code iterates through the rows of data that are in the result set, and uses the getSQLXML method to read the XML data in TestTable2.
:::code language="java" source="codesnippet/Java/sqlxml-data-type-sample_1.java":::