In a table I have a column called column_xml defined as CLOB(1000000). The fields in the column contain XML formatted information. My problem is that there are lots of white spaces in these fields that I need to get remove if there are more than 3 whitespaces in a row.
This is what the output looks like now
column_xml
--------------------------------------------------------------------------------
<values>
<comment>test test test </comment>
<rights></rights>
<title>TEST MAN </title>
</values>
--------------------------------------------------------------------------------
How can I remove all the white spaces before </comment> and </title> so it looks like this?
column_xml
--------------------------------------------------------------------------------
<values>
<comment>test test test</comment>
<rights></rights>
<title>TEST MAN</title>
</values>
--------------------------------------------------------------------------------
Best Answer
If you define your XML document in a XML column, you can control how the whitespaces are stored.
For XML parsing, you can take a look a this page: http://pic.dhe.ibm.com/infocenter/db2luw/v10r5/topic/com.ibm.db2.luw.xml.doc/doc/c0023533.html
To define the XML Schema, and how the whitespaces should be treated: http://www.w3schools.com/schema/schema_facets.asp
However, you are using a LOB and you are not exploiting the hybrid motor of DB2.