[Deploying Sakai] Lessons on Oracle possible issue

[Steve Swinsburg]

That was an example from MySQL ;)
I was just illustrating that you can return separate fields from the one flattened structure. 

As for performance, at least in Oracle, XMLType isn’t as clumsy as you might think. I have XMLType queries that extract dozens of columns from an enormous XML DOM and it is very fast. You can see it in the explain plan. 

You can make your client be data storage agnostic by having the XML extraction done by the database, then all you see are regular columns. But it depends on what your needs are. Deserialising it yourself could be fine too, you could use the Simple XML framework annotations, or a JAXB style mapping which would make it pretty easy.

cheers,
Steve





On 2 May 2014, at 11:55 pm, Charles Hedrick <hedrick at rutgers.edu> wrote:

> You could do this in mysql as well. But unless it’s doing something very tricky, it would be parsing the xml file to a DOM for each column, and then doing a DOM search. I’d much rather retrieve the whole thing, parse it once on the client side and then pull individual values out. Of course that would require code in the client that knows it’s stored in XML. But that could be hidden in the lowest level of the code.
> 
> On May 2, 2014, at 9:43 AM, Steve Swinsburg <steve.swinsburg at gmail.com> wrote:
> 
>> SELECT
>>   EXTRACTVALUE(ac.xml, '/content/@title') assignment_title
>> , EXTRACTVALUE(ac.xml, '/content/@datecreated') date_created
>> , EXTRACTVALUE(ac.xml, '/*/properties/property[@name=''CHEF:creator'']/@value') assignment_creator
>> FROM assignment_content ac
>> 
> 

-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://collab.sakaiproject.org/pipermail/production/attachments/20140503/636efab1/attachment.html