Recursive XSLT to filter nodes

We all know that one can surely do a lot of cool stuff with XSL but still sometimes you'll come across some small piece of code which will impress you with its simplicity and yet effectiveness. Here is my favorite piece of  XSLT code:

 
<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
  <xsl:output indent="yes"/>
  <xsl:template match="node()|@*">
    <xsl:copy>
      <xsl:apply-templates select="node()|@*"/>
    </xsl:copy>
  </xsl:template>
</xsl:stylesheet>

All it does it create a copy of input XML. But it can be modified to do a lot of neat stuff. Here are a few examples:

----------------------------- Example-1: Convert attributes to fields -----------------------------

Source XML:

<root att1="att1">
  <child1  att2="att2">
    <child2  name="field_name1" value="field_value1"/>
    <child2  name="field_name2" value="field_value2"/>
  </child1>
</root>

Lets say my target application does not like attributes and it wants data as XML nodes. So the desired output is:

<?xml version="1.0" encoding="UTF-8"?>

<root att1="att1">
    <child1 att2="att2">
        <field_name1>field_value1</field_name1>
        <field_name2>field_value2</field_name2>
    </child1>
</root>

Here is the XSL code:

<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
  <xsl:output indent="yes"/>

  <xsl:template match="node()|@*">
    <xsl:copy>
      <xsl:apply-templates select="node()|@*"/>
    </xsl:copy>
  </xsl:template>

  <xsl:template match="child2">
    <xsl:element name="{@name}">
      <xsl:value-of select="@value"/>
    </xsl:element>
  </xsl:template>

</xsl:stylesheet>

----------------------------- Example-2 Filter Specific Data -----------------------------

Source XML:

<?xml version="1.0" encoding="UTF-8"?>
<root>
  <node>
    <data1>null</data1>
    <data2>data</data2>
    <data3/>
    <data4>null</data4>
    <data5></data5>
  </node>
</root>

Desired Output is:

<?xml version="1.0" encoding="UTF-8"?>
<root>
  <node>
    <data2>data</data2>
    <data3/>
    <data5/>
  </node>
</root>

This is very common in integration scenarios. Application-A gives out data will no value as 'null' values but Application-B takes this 'null' as a string. So best way forward is to filter out these 'null' nodes.

Here is the XSL code:

<?xml version="1.0" encoding="UTF-8"?>
<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
  <xsl:output indent="yes"/>

  <xsl:template match="node()|@*">
          <xsl:if test=". !='null'">
            <xsl:copy>
              <xsl:apply-templates select="node()|@*"/>
            </xsl:copy>
          </xsl:if>
  </xsl:template>


</xsl:stylesheet>

Awesome isn't it? Other way would be to put a check on each node for value 'null' before it is sent to Application-B, which would be just painfully annoying and plainly a bad approach.


----------------------------- Example-3: Update Selective Nodes -----------------------------

How about the other way around. So now we have some empty nodes and we want their value set as 'null'

Source XML:

<root att1="a1">
  <child att2="a2">
    <data att3="a3"></data>
    <data att3="a3">data</data>
    <data att3="a3"/>
  </child>
</root>

Desired Output:

<?xml version="1.0" encoding="UTF-8"?>
<root att1="a1">
    <child att2="a2">
        <data>null</data>
        <data att3="a3">data</data>
        <data>null</data>
    </child>
</root>

Here is the XSL code:

<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
  <xsl:output indent="yes"/>

  <xsl:template match="node()|@*">
          <xsl:choose>
          <xsl:when test="string-length(.) = 0">
            <xsl:element name="{name()}">
                <xsl:text disable-output-escaping="no">null</xsl:text>
            </xsl:element>
          </xsl:when>
          <xsl:otherwise>
            <xsl:copy>
              <xsl:apply-templates select="node()|@*"/>
            </xsl:copy>
          </xsl:otherwise>
        </xsl:choose>
  </xsl:template>
</xsl:stylesheet>

These are just a few examples but we can use this XSL in many ways to reformat/restructure a XML. There is always a possibility for a better approach. So guys if you know anything better then do update me, either in comments or just shoot me a mail.

32 bit, 64 bit confusions: ODBC connection

Often I have seen people get confused in an mixed 32 bit, 64 bit environment. Mostly people will have 64 bit OS like Windows 7, 64 bit with 32 bit applications like MS office 2007. Like recently I found suddenly a simple JDBC code (using JDBC-ODBC bridge) which was reading Excel file, started failing in a new environment. Just listing down things to keep in mind:

- Check if you have 32 bit Java or 64 bit Java?
- Check if you have 32 bit ODBC drivers or 64 bit ODBC drivers.
- Finally check if you are using JDBC connection URL for the version of ODBC drivers you have.

So, in 32 bit environment:
run C:\windows\sysWOW64\odbcad32.exe and see if the drivers are present for excel or not.
If not then install AccessDatabaseEngine.exe from Microsoft Download Center.

Now the connection URL in the JDBC code will be:
jdbc:odbc:Driver={Microsoft Excel Driver (*.xls)};DBQ=<Path to .xls file>

And in a 64 bit environment:
Run C:\windows\system32\odbcad32.exe and see if the drivers are present.
If not then install AccessDatabaseEngine_x64.exe from the link mentioned above.

Now the connection URL will be:
jdbc:odbc:DRIVER={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)};DBQ=<Path to .xls file>

Sample code:

Class.forName( "sun.jdbc.odbc.JdbcOdbcDriver" );

//using DSN-less connection in 32 bit env
con = DriverManager.getConnection( "jdbc:odbc:Driver={Microsoft Excel Driver (*.xls)};DBQ=C:/Abhinay/test.xls")

//using DSN-less connection in 64 bit env

//con = DriverManager.getConnection( "jdbc:odbc:DRIVER={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)};DBQ=C:/Abhinay/test.xls");

stmnt = con.createStatement();
String query = "select * from [Sheet1$];";
ResultSet rs = stmnt.executeQuery( query );