[Building Sakai] Why use XML in database?

[Gregory Guthrie]

Steve (and all) – thanks;
I think I found out how to do it using Xpath queries in mySql which I hadn’t been forced into using before.

But it forced me into fiddling with the strings from the values, instead of SQL field values directly.

I wanted to list all assignments; and maybe shouldn’t admit it here but also to time-shift them for a new offering of a course. :-)

This is probably non-optimal , but works.

If you have any better examples, including the updates to the XML strings, that would be helpful!

# --------------------------------------------------------------------
#  List Assignments::  for a course (site)
#  (Note: includes all assignments, not just those in gradebook)
#
set @course = "CS505 -Winter 2012";
# --------------------------------------------------------------------
# -- uses some UDF functions: Only defined once (during development?!)
#
#  long date to Short (YYYMMDDttttttttt) -> mm/dd/yyyy
drop   function if exists strDate;
Create Function strDate (myDate CHAR(17)) Returns CHAR(10)
    Return if (myDate = "", mydate,
        concat( substring(myDate,5,2), "/",
        substring(myDate,7,2), "/",
        substring(myDate,1,4)));
# --------------------------------------------------------------------
SELECT
#     Xml,
      title as "Course",
      @adate := ExtractValue(XML,'/assignment /@opendate') as "OpenDate",
      strDate(@adate) as OpenDate,
      ExtractValue(XML,'/assignment /@title')     as title
#    ExtractValue(XML,'/assignment /@duedate')      as duedate
#    ExtractValue(XML,'/assignment /@closedate')    as closedate
#    ExtractValue(XML,'/assignment /@dropdeaddate') as dropdeaddate
FROM    SAKAI_SITE     site,
        ASSIGNMENT_ASSIGNMENT
where site.title         = @course                 # Find this course (site)
  and context=site.Site_Id
order by openDate;


-------------------------------------------
From: Steve Swinsburg [mailto:steve.swinsburg at gmail.com] 
Sent: Monday, November 24, 2014 4:02 AM
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