tostinni

Hi everybody,I recently discover the XMLTYPE Datatype in Oracle and found it very handy to manage XML document I have to deal with.So I built my little app to analyze and store them, but I found a problem dealing with big documents.As you may know, Oracle have a big drawback when inserting string larger than 4000 characters. To overcome this limitation, we need to use bind variables.So I looked for exemples over the web and couldn't find anything which will satisfy me.Regarding DataTypes available in ADO (MDAC 2.8), I found nothing approching Oracle's Clob nor XMLTYPE. In W3Schools ADO tutorial, it says that adLongVarChar seems to fit Clob.After many tests, I wasn't able to bind a string to insert it in an XMLTYPE column, nor in a CLOB :)It seems that MDAC/ADO doesn't support such DataTypes which is a very annoying problem.So I ended doing the following

SQL = "INSERT INTO long_tab (my_long) values (  ?  )"cmd.CommandText = SQLcmd.Parameters.Append cmd.CreateParameter("XML", adLongVarChar, adParamInput, 20000, xmlResponse)cmd.ExecuteSQL = "INSERT INTO clob_tab (my_clob) select to_lob(my_long) from long_tab  "cmd.CommandText = SQLcmd.ExecuteSQL = "INSERT INTO XML_TAB (my_XML)  select xmltype(my_clob) from clob_tab "cmd.CommandText = SQLcmd.Execute

Each X_TAB is a table with one column of X DataType (LONG_TAB -> LONG column )So what's done here, is that first I insert a binded variable into a Long column, which works fine using "adLongVarChar" from ADO, and then I made an insert converting this LONG into a CLOB and end by converting this CLOB into an XMLTYPE column.It seems that doing this in one query doesn't work.

INSERT INTO xml_tab (my_xml) SELECT XMLTYPE(TO_LOB(?)) FROM DUAL

Note: Oracle can only use TO_LOB function in the SELECT part of an INSERT...Well, maybe someone had struggle with this...PS: I'm already looking for another solution using OO4O.PPS: I successfuly made this with VB.NET, but I need to work with VB6

An application will access data that will reside in XML documents stored in an RDBMS. What are two vendor-independent choices for storing each document in the RDBMS?(Choose 2) A.In a table view. B.As a SOAP envelope. C.In a table's CLOB column. D.As RDBMS meta-data. E.Decomposed into various columns or tables.

Hi boen_robot,Thanks a lot for your reply, in fact I thought something was missing about root template, but didn't knew how to create it, you help me a lot.I understood the template you put for "peoples" but when I apply it to my doc, I didn't have any output...Is something missing ?Edit:If I change

<xsl:template match="/">  <xsl:apply-templates select="peoples"/></xsl:template>

By

<xsl:template match="/">  <xsl:apply-templates select="//peoples"/></xsl:template>

It works, I think the confusing part for me is that it's not nodename that select all node matching the name but //nodename.The only strange thing is that the first <xsl:template match="/"> should have taken all child of root element which I thought was people_list but it seems not to be root...

Hi Everybody,After reading the tutorial about XSLT, I went and tried to convert a little XML into a very simple HTML table but I'm stuck with something I don't understand.So here's the XML

<?xml version="1.0"?><people_list>  <quantity>2</quantity>  <peoples>	  <people>		  <name>John</name>		  <age>25</age>	  </people>	  	  <people>		  <name>Steve</name>		  <age>30</age>	  </people>  </peoples></people_list>

And here's the XSLT I wrote:

<?xml version="1.0" encoding="ISO-8859-1"?><xsl:stylesheet version="1.0"xmlns:xsl="http://www.w3.org/1999/XSL/Transform"><xsl:output	method="html"	indent="yes"/> 	<xsl:template match="peoples">  <table>	<tr>	  <td>Name</td>	  <td>Age</td>	</tr>	<xsl:for-each select="people">	  <tr>		<td><xsl:value-of select="name"/></td>		<td><xsl:value-of select="age"/></td>	  </tr>	</xsl:for-each>  </table></xsl:template></xsl:stylesheet>

When I realize the transformation, I get

2<table><tr><td>Name</td><td>Age</td></tr><tr><td>John</td><td>25</td></tr><tr><td>Steve</td><td>30</td></tr></table>

So everything is pretty as I wished excepted the "2" which came from the quantity element of my XML.I don't understand why it shows up and how can I exclud/avoid it ?I thought that putting a template that would apply to peoples will hide it but it doesn't work :)Any idea of what I'm doing wrong ?Thanks a lot for the help.