Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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>                                                                     
--------------------------------------------------------------------------------
share|improve this question

2 Answers 2

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.

share|improve this answer
    
I suggest you replace that link to w3fools with a link to IBM DB2 doc pages –  ypercube May 14 at 8:38
    
I put a IBM DB2 link, however, I left the w3schools for XML defining. IBM DOC does not gives those clues. –  AngocA May 14 at 8:52
    
I cannot judge this specific w3schools page but I have often found many mistakes in other pages of theirs (like in SQL ones.) Is there no official XML site that has info about this? –  ypercube May 14 at 9:48
    
Well, the official documentation comes from W3C: w3.org/TR/xmlschema-2 (Section 4.3.6 Facets>Whitespace) However this documentation is very complex, and difficult to solve a simple problem at an entry level. –  AngocA May 14 at 11:58

I think this can be done with a simple replace:

UPDATE {table} set {field} = REPLACE(REPLACE(REPLACE({field}, '   ', ''), '  ', ''), ' </', '</')

With the input:

<values>                                                                      
 <comment>test test test                               </comment>
 <rights></rights>                                                            
 <title>TEST MAN                                            </title>                                         
</values> 

Gives the result:

<values> 
 <comment>test test test</comment>
 <rights></rights>
 <title>TEST MAN</title>
</values>
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.