Simple XML parser + SQL Generation

Problem: Quick way to parse an XML stream from a website and generate a SQL script to populate a MSSQL table.

Solution:

import java.io.InputStream;
import java.net.URL;

import javax.xml.parsers.DocumentBuilder;
import javax.xml.parsers.DocumentBuilderFactory;

import org.w3c.dom.Document;
import org.w3c.dom.Element;
import org.w3c.dom.Node;
import org.w3c.dom.NodeList;

public class XMLReader {

public static void main(String argv[]) {

try {
URL url = new URL("http://www.treasurydirect.gov/xml/CPI_20090814.xml");
InputStream is = url.openStream();

DocumentBuilderFactory dbf = DocumentBuilderFactory.newInstance();
DocumentBuilder db = dbf.newDocumentBuilder();
Document doc = db.parse(is);
doc.getDocumentElement().normalize();
NodeList dailyIndexes = doc.getElementsByTagName("DailyIndexRatio");

/*
* <DailyIndexRatio>
*   <CUSIP>912810FD5</CUSIP>
*   <IssueDate>1998-04-15</IssueDate>
*   <Date>2009-09-01</Date>
*   <RefCPI>215.69300</RefCPI>
*   <IndexRatio>1.33358</IndexRatio>
* </DailyIndexRatio>
*/

for (int s = 0; s < dailyIndexes.getLength(); s++) {
    Node currentIndexNode = dailyIndexes.item(s);
    if (currentIndexNode.getNodeType() == Node.ELEMENT_NODE) {
    // DailyIndexRatio Element
    Element currentIndexElement = (Element) currentIndexNode;

    // List of CUSIP Nodes
    NodeList cusipNodeList = currentIndexElement.getElementsByTagName("CUSIP");
    // CUSIP Element (1st one)
    Element cusipElmnt = (Element) cusipNodeList.item(0);
    // List of child nodes for CUSIP Element
    NodeList cusipChildNodes = cusipElmnt.getChildNodes();
    // Get the value of the 1st node (Text of CUSIP Element)
    //((Node) cusipChildNodes.item(0)).getNodeValue()

    NodeList dateNodeList = currentIndexElement.getElementsByTagName("Date");
    Element dateElmnt = (Element) dateNodeList.item(0);
    NodeList dateChildNodes = dateElmnt.getChildNodes();

    NodeList indexRatioNodeList = currentIndexElement.getElementsByTagName("IndexRatio");
    Element indexRatioElmnt = (Element) indexRatioNodeList.item(0);
    NodeList indexRatioChildNodes = indexRatioElmnt.getChildNodes();

    // SQL SCRIPT GENERATION
    System.out.print("INSERT INTO [CPIData] (Date, CUSIP, CPI) VALUES ('"
    + ((Node) dateChildNodes.item(0)).getNodeValue() + "','");
    System.out.print(((Node) cusipChildNodes.item(0)).getNodeValue() + "',");
    System.out.println(((Node) indexRatioChildNodes.item(0)).getNodeValue() + ")");
    }

}
} catch (Exception e) {
    e.printStackTrace();
}
}
}

Basically, I just needed the Consumer Price Indexes (CPI) that needs to be applied to a particular security (TIPS).
This simple java app returns (console) the SQL script that we need to execute in order to have the CPI’s stored in the DB.

Note: This code has been tested using Eclipse 3.3 and JDK 6.