Jump to content

tostinni

Members
  • Posts

    4
  • Joined

  • Last visited

tostinni's Achievements

Newbie

Newbie (1/7)

0

Reputation

  1. 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
  2. tostinni

    XML & Database

    Is the "vendor-independent" constraint a mandatory one ?Because I'm just starting working with Oracle XMLTYPE and I think it's a near perfect solution to handle this.Also, CLOB is an Oracle DataType...
  3. 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...
  4. 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.
×
×
  • Create New...