c# - Oracle 11.2 - insert html into column

j420exe1
j420exe1 used Ask the Experts™
on
c# - Oracle 11.2 - store html in column.

What is the recommended column type when storing html?

html files > 100K

Snapshot example html (obvisouly not 100K):
<html>
  <body>
    <table width='100%'>
      <tr>
        <td colspan='2' align='center'><b>Legend:</b>
          <font style='background-color: yellow' color='black'>added
          </font>&nbsp;&nbsp;
          <font style='background-color: red' color='black'>removed
          </font>&nbsp;&nbsp;
          <font style='background-color: lightgreen' color='black'>changed
          </font>&nbsp;&nbsp;
          <font style='background-color: red' color='blue'>moved from
          </font>&nbsp;&nbsp;
          <font style='background-color: yellow' color='blue'>moved to
          </font>&nbsp;&nbsp;
          <font style='background-color: white' color='#AAAAAA'>ignored
          </font></td>
      </tr>
      <tr><td><b> File Name : 20120507T094230.xml</b></td>
      </tr>
      <tr>
        <td style="padding-left: 10pt;">
          <font style="background-color: white" color="black">&lt;Envelope
          </font>
          <font style="background-color: white" color="black">xmlns:p1="http://www.w3.org/2001/XMLSchema-instance"
          </font><br />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
          <font style="background-color: white" color="black">xmlns="http://schemas.xmlsoap.org/soap/envelope/"
          </font>
          <font style="background-color: white" color="black">&gt;
          </font></td>
        <td style="padding-left: 10pt;">
          <font style="background-color: white" color="black">&lt;Envelope
          </font>
          <font style="background-color: white" color="black">xmlns:p1="http://www.w3.org/2001/XMLSchema-instance"
          </font><br />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
          <font style="background-color: white" color="black">xmlns="http://schemas.xmlsoap.org/soap/envelope/"
          </font>
          <font style="background-color: white" color="black">&gt;
          </font></td>
      </tr>
      <tr>
        <td style="padding-left: 25pt;">
          <font style="background-color: white" color="black">&lt;Body
          </font>
          <font style="background-color: white" color="black">&gt;
          </font></td>
        <td style="padding-left: 25pt;">
          <font style="background-color: white" color="black">&lt;Body
          </font>
          <font style="background-color: white" color="black">&gt;
          </font></td>
      </tr>        
    </table>
  </body>
</html>

Can I store this in XMLType?  I can view the contents of XMLTYPE using SQLDeveloper vs Clob\Blob.

Thoughts?

Thanks in advance.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Most Valuable Expert 2012
Distinguished Expert 2018

Commented:
>>What is the recommended column type when storing html?

CLOB or XMLTYPE.

>> I can view the contents of XMLTYPE using SQLDeveloper

Yes.  an XMLTYPE is baically a CLOB with some extra stuff added.  You can easiy convery it to a CLOB with the .getclobval() method.

Author

Commented:
If I use XMLType do I have to wrap the <html> markup in <xml> tags?
Most Valuable Expert 2012
Distinguished Expert 2018

Commented:
>>If I use XMLType do I have to wrap the <html> markup in <xml> tags?

No.

You will need to ensure the HTML is XHTML compliant.  If you can have 'bad' html, then use a CLOB.

A lot also depends on what you will be doing with the html once you store it.
Success in ‘20 With a Profitable Pricing Strategy

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Author

Commented:
1) How can I convert my basic html (example markup in org post) to be XHTML compliant?

2) If I am storing CLOB...is it true that I have to insert the record then update the CLOB column inside a transaction?  I am using ODP.net with OracleParameters.

3) We have a asp.net page that displays the data from the db.  If the entity contains xml or html then we will allow the user to click a link to display it in another browser (popup).
Most Valuable Expert 2012
Distinguished Expert 2018

Commented:
1:  Follow XHTML standards?

http://www.w3.org/TR/xhtml1/

You can check validity at:
http://validator.w3.org/

It is basically making sure your HTML fits a certain model and has no minor flaws that browsers compensate for.

for example, every browser I know of will allow <br> and <hr>.  These are not proper XML syntax.  You need to close the tags:  <br /> <hr />.

2:  "is it true that I have to insert the record then update the CLOB column inside a transaction?"

I do not know what this means.  ODP.Net has a CLOB data type.  You should just be able to insert it like you would any other parameter.

The only gotcha I know about is out output.  You cannot do a simple param.value.  You need to do (param.value).value to see the CLOB.

3:  What you do with the info once you retrieve it is up to you.  Should be a simple asp:hyperlink or similar object with distinct target.  This should cause a new window to open.

Author

Commented:
The DBA gave us type BLOB.  We are storing html in the column.  Any pros\cons of CLOB vs BLOB?

We are using asp.net dynamic data with EF 4 to display the data with basic CRUD options.  Trying to figure out how to create\modify a custom entity type to display a new html page with the user clicks in the column with the html data.

Author

Commented:
I asked the dynamic data comment in another topic:

asp.net - dynamic data site - display blob data
Most Valuable Expert 2012
Distinguished Expert 2018
Commented:
>>Any pros\cons of CLOB vs BLOB?

Night and day difference.  CLOBs are a LOT like a VARCHAR2.  BLOBs are a pain in the a$$ to work with.

Do you need the ability to store binary data?  If not, make the DBA change it to a CLOB.

Author

Commented:
Change request submitted to get CLOB.
Most Valuable Expert 2012
Distinguished Expert 2018

Commented:
>>Change request submitted to get CLOB.

Well worth the trouble....  You will thank me later!
Most Valuable Expert 2012
Distinguished Expert 2018

Commented:
j420exe1,

Why the "B" grade?  This is a penality.  The question originally asked was:

"What is the recommended column type when storing html?"
and
"Can I store this in XMLType?"


How better could I have answered this to receive an "A" grade?

Please review:
http://www.experts-exchange.com/help/viewHelpPage.jsp?helpPageID=26

A B grade should be given if the solutions require you to do a good amount of extra work to resolve your problem.
Most Valuable Expert 2012
Distinguished Expert 2018

Commented:
j420exe1,

I would still like to know what I could have done to receive an "A" grade.

Author

Commented:
Oh sorry...didn't know that a B was a "penalty".

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial