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.

Example of Partitioned View by Date

Let’s begin with the creation of the partitioned tables:


IF EXISTS (
        SELECT *
        FROM dbo.sysobjects
        WHERE NAME = 'PartitionedTable1' )
            DROP TABLE [dbo].[PartitionedTable1]
GO
IF EXISTS (
        SELECT *
        FROM dbo.sysobjects
        WHERE NAME = 'PartitionedTable2' )
            DROP TABLE [dbo].[PartitionedTable2]
GO

-- TABLE CREATION
CREATE TABLE [dbo].[PartitionedTable1](
    ID INT NOT NULL,
    Date DATETIME NOT NULL CHECK (Date BETWEEN '20090101' AND '20090131'), -- Partitioned Column Check !
    Field1 VARCHAR(25) NULL,
    Field2 VARCHAR(25) NULL,
    CONSTRAINT [PT1_PK] PRIMARY KEY CLUSTERED (ID,Date)
)
CREATE TABLE [dbo].[PartitionedTable2](
    ID INT NOT NULL,
    Date DATETIME NOT NULL CHECK (Date BETWEEN '20090201' AND '20090228'), -- Partitioned Column Check !
    Field1 VARCHAR(25) NULL,
    Field2 VARCHAR(25) NULL,
    CONSTRAINT [PT2_PK] PRIMARY KEY CLUSTERED (ID,Date)
)


Some Notes:
* Partitioned field (Date) should be part of the PK of the table.
* The CHECK constraint can only use these operators:BETWEEN, AND, OR, <, <=, >, >=, =.

Now, let’s create the view for our recently created partitioned tables:

-- VIEW
IF EXISTS (
    SELECT *
    FROM dbo.sysobjects
    WHERE id = OBJECT_ID(N'[dbo].[PartitionedView]') AND type in (N'V', N'PC'))
        DROP VIEW [dbo].[PartitionedView]
GO

CREATE VIEW [dbo].[PartitionedView]
AS
    SELECT *
    FROM [dbo].[PartitionedTable1]
    UNION ALL
    SELECT *
    FROM [dbo].[PartitionedTable2]


voilà! Let’s run a simple test:

-- TEST
INSERT INTO [dbo].[PartitionedView] (ID,Date,Field1,Field2) VALUES (1,'01/05/2009','Value1','Value2')
INSERT INTO [dbo].[PartitionedView] (ID,Date,Field1,Field2) VALUES (2,'02/10/2009','Value3','Value4')
INSERT INTO [dbo].[PartitionedView] (ID,Date,Field1,Field2) VALUES (3,'01/12/2009','Value5','Value6')
INSERT INTO [dbo].[PartitionedView] (ID,Date,Field1,Field2) VALUES (4,'03/12/2009','Value7','Value8') -- This one FAILS because the date in the new row is not considered by any of the conditions


and check our partitined tables.

SELECT * FROM [dbo].[PartitionedTable1]
SELECT * FROM [dbo].[PartitionedTable2]

You should get something like this:

Enjoy.