[Building Sakai] Why use XML in database?

[Stephen Marquard]

If you’re doing ad-hoc queries with mysql you can use syntax like this:

SELECT ExtractValue(XML, '//assignment/@assignmentcontent') from ASSIGNMENT_ASSIGNMENT …

where the second parameter to ExtractValue is an XPath query (in this case to get the assignmentcontent attribute).

Regards
Stephen


---
Stephen Marquard, Learning Technologies Co-ordinator,
Centre for Innovation in Learning and Teaching (CILT)
University of Cape Town
http://www.cilt.uct.ac.za
stephen.marquard at uct.ac.za<mailto:stephen.marquard at uct.ac.za>
Phone: +27-21-650-5037 Cell: +27-83-500-5290

From: sakai-dev-bounces at collab.sakaiproject.org [mailto:sakai-dev-bounces at collab.sakaiproject.org] On Behalf Of Steve Swinsburg
Sent: 24 November 2014 12:02 PM
To: Adrian Fish
Cc: Gregory Guthrie; sakai-dev at collab.sakaiproject.org
Subject: Re: [Building Sakai] Why use XML in database?

Yes, it's a massive pain, it's a bunch of wasted processing overhead on every request, and if anyone is doing it in Sakai nowadays I'll have a word with them in a dark corner at the next conference.

That said though, I have written a bunch of SQL to extract the data out and do stuff with it so if you want some SQL or PL/SQL, let me know what you need and the SQL you currently have.
cheers,
Steve

On Mon, Nov 24, 2014 at 8:45 PM, Adrian Fish <adrian.r.fish at gmail.com<mailto:adrian.r.fish at gmail.com>> wrote:
It makes development iterations shorter as it minimises the need to modify database schemas in parallel with code changes. It's a pain,though, as you can't query the data as easily, as you describe. Doing that kind of thing is pretty much deprecated now, I'd say, but it takes a reasonable amount of effort to extract out into columns, so it doesn't happen.

That's my take on it, anyway.

Cheers,
Adrian.

On 21 November 2014 at 21:21, Gregory Guthrie <guthrie at mum.edu<mailto:guthrie at mum.edu>> wrote:
What is the reason that some entries in the database are encoded in XML, instead of basic fields in another table?

As one example; in the ASSIGNMENT_ASSIGNMENT table, to find by dates one has to do an SQL lookup, and then decode into the XML entry with the date encoded as strings in one of the XML fields.

The ASSIGNMENT_ID and CONTEXT fields are then replicated in the <assignment> field of the XML as values.

-------------------------------------------


_______________________________________________
sakai-dev mailing list
sakai-dev at collab.sakaiproject.org<mailto:sakai-dev at collab.sakaiproject.org>
http://collab.sakaiproject.org/mailman/listinfo/sakai-dev

TO UNSUBSCRIBE: send email to sakai-dev-unsubscribe at collab.sakaiproject.org<mailto:sakai-dev-unsubscribe at collab.sakaiproject.org> with a subject of "unsubscribe"


_______________________________________________
sakai-dev mailing list
sakai-dev at collab.sakaiproject.org<mailto:sakai-dev at collab.sakaiproject.org>
http://collab.sakaiproject.org/mailman/listinfo/sakai-dev

TO UNSUBSCRIBE: send email to sakai-dev-unsubscribe at collab.sakaiproject.org<mailto:sakai-dev-unsubscribe at collab.sakaiproject.org> with a subject of "unsubscribe"

________________________________
UNIVERSITY OF CAPE TOWN

This e-mail is subject to the UCT ICT policies and e-mail disclaimer published on our website at http://www.uct.ac.za/about/policies/emaildisclaimer/ or obtainable from +27 21 650 9111. This e-mail is intended only for the person(s) to whom it is addressed. If the e-mail has reached you in error, please notify the author. If you are not the intended recipient of the e-mail you may not use, disclose, copy, redirect or print the content. If this e-mail is not related to the business of UCT it is sent by the sender in the sender's individual capacity.
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://collab.sakaiproject.org/pipermail/sakai-dev/attachments/20141124/44e8fa63/attachment.html