import [Link].
StringReader;
import [Link];
import [Link];
import [Link];
import [Link];
import [Link];
import [Link];
import [Link];
import [Link];
import [Link];
import [Link];
import [Link];
import [Link];
public class SparseColumns {
public static void main(String args[]) {
// Create a variable for the connection string.
String connectionUrl =
"jdbc:sqlserver://<server>:<port>;databaseName=AdventureWorks;user=<user>;password=
<password>";
try (Connection con = [Link](connectionUrl); Statement
stmt = [Link]()) {
createColdCallingTable(stmt);
// Determine the column set column
String columnSetColName = null;
String strCmd = "SELECT name FROM [Link] WHERE object_id=(SELECT
OBJECT_ID('ColdCalling')) AND is_column_set = 1";
try (ResultSet rs = [Link](strCmd)) {
if ([Link]()) {
columnSetColName = [Link](1);
[Link](columnSetColName + " is the column set
column!");
}
}
strCmd = "SELECT * FROM ColdCalling";
try (ResultSet rs = [Link](strCmd)) {
// Iterate through the result set
ResultSetMetaData rsmd = [Link]();
DocumentBuilderFactory dbf = [Link]();
DocumentBuilder db = [Link]();
InputSource is = new InputSource();
while ([Link]()) {
// Iterate through the columns
for (int i = 1; i <= [Link](); ++i) {
String name = [Link](i);
String value = [Link](i);
// If this is the column set column
if ([Link](columnSetColName)) {
[Link](name);
// Instead of printing the raw XML, parse it
if (value != null) {
// Add artificial root node "sparse" to ensure XML
is well formed
String xml = "<sparse>" + value + "</sparse>";
[Link](new StringReader(xml));
Document doc = [Link](is);
// Extract the NodeList from the artificial root
node that was added
NodeList list = [Link]();
Node root = [Link](0); // This is the <sparse>
node
NodeList sparseColumnList =
[Link](); // These are the xml column nodes
// Iterate through the XML document
for (int n = 0; n < [Link](); +
+n) {
Node sparseColumnNode =
[Link](n);
String columnName =
[Link]();
// Note that the column value is not in the
sparseColumNode, it is the value of
// the
// first child of it
Node sparseColumnValueNode =
[Link]();
String columnValue =
[Link]();
[Link]("\t" + columnName + "\t: " +
columnValue);
}
}
} else { // Just print the name + value of non-sparse
columns
[Link](name + "\t: " + value);
}
}
[Link]();// New line between rows
}
}
} catch (Exception e) {
[Link]();
}
}
private static void createColdCallingTable(Statement stmt) throws SQLException
{
[Link]("if exists (select * from [Link] where name =
'ColdCalling')" + "drop table ColdCalling");
String sql = "CREATE TABLE ColdCalling ( ID int IDENTITY(1,1) PRIMARY
KEY, [Date] date, [Time] time, PositiveFirstName nvarchar(50) SPARSE,
PositiveLastName nvarchar(50) SPARSE, SpecialPurposeColumns XML COLUMN_SET FOR
ALL_SPARSE_COLUMNS );";
[Link](sql);
sql = "INSERT ColdCalling ([Date], [Time]) VALUES ('10-13-09','[Link]')
";
[Link](sql);
sql = "INSERT ColdCalling ([Date], [Time], PositiveFirstName,
PositiveLastName) VALUES ('07-20-09','[Link]', 'AA', 'B') ";
[Link](sql);
sql = "INSERT ColdCalling ([Date], [Time], PositiveFirstName,
PositiveLastName) VALUES ('07-20-09','[Link]', 'CC', 'DD') ";
[Link](sql);
}
}
