B4J Question mysql resultset to xml

[paddy12309]

So I'm most likely just being a bit thick today but i can't get my head around how to get this to work; The aim being to have an xml builder populated from a database.

'Handler class
Sub Class_Globals
    Private mreq As ServletRequest 'ignore
    Private mresp As ServletResponse 'ignore
'    Private sql1 As SQL
End Sub

Public Sub Initialize
    If Not(Main.sql1.IsInitialized) Then
        Log("ReInitialising SQL1")
        Main.sql1.Initialize2("com.mysql.jdbc.Driver", "jdbc:mysql://localhost/liteip","Collator","liteip1234")
    End If
End Sub

Sub Handle(req As ServletRequest, resp As ServletResponse)
    mreq = req
    mresp = resp
    resp.ContentType = "text/xml"
    'Subs.RespWriteStyle(resp)
   
    If Not(Main.sql1.IsInitialized) Then
        Log("SQL1 Failed")
        Return
    End If

    Dim RS As ResultSet = Main.sql1.ExecQuery("SELECT * FROM DeviceStatus INNER JOIN DeviceLive ON DeviceStatus.DeviceSN = DeviceLive.DeviceSN ")
    Dim Count As Int
    Count = 0
    Do While RS.NextRow
        Count = Count + 1
    Loop
    Count = Count * 2
    Count = Count + 8 
   
    'RS = Main.sql1.ExecQuery("SELECT * FROM DeviceStatus INNER JOIN DeviceLive ON DeviceStatus.DeviceSN = DeviceLive.DeviceSN ")
    Dim ALRS As ResultSet =  Main.sql1.ExecQuery("SELECT * FROM ((DeviceLive INNER JOIN AlarmTable ON DeviceLive.DeviceSN = AlarmTable.DeviceSN) INNER JOIN AlarmReason ON AlarmTable.ReasonIndex = AlarmReason.AlarmIndex) ORDER BY AlarmTable.DeviceSN, AlarmReason.Reason")
    Dim al As XMLBuilder
    al = al.create("CONTROLLER")
    al = al.e("ALARMLIST").attribute("COUNT", Count)
    al = al.e("ALARMITEM").attribute("REASON", "emergency faults > 0").attribute("TYPE", "1").up
    al = al.e("ALARMITEM").attribute("REASON", "emergency faults > 2").attribute("TYPE", "2").up
    al = al.e("ALARMITEM").attribute("REASON", "emergency faults > 5").attribute("TYPE", "3").up
    al = al.e("ALARMITEM").attribute("REASON", "emergency faults > 10").attribute("TYPE", "4").up
    al = al.e("ALARMITEM").attribute("REASON", "non-emergency faults > 0").attribute("TYPE", "5").up
    al = al.e("ALARMITEM").attribute("REASON", "non-emergency faults > 2").attribute("TYPE", "6").up
    al = al.e("ALARMITEM").attribute("REASON", "non-emergency faults > 5").attribute("TYPE", "7").up
    al = al.e("ALARMITEM").attribute("REASON", "non-emergency faults > 10").attribute("TYPE", "8").up
    Do While ALRS.NextRow
        Log("loop")
        al = al.e("ALARMITEM").attribute("REASON", Subs.SuffixSN(ALRS.GetString("Reason"),ALRS.GetInt("DeviceLive.DeviceSN"))).attribute("TYPE", ALRS.GetInt("AlarmReason.Type")).up
        al = al.e("ALARMITEM").attribute("REASON", Subs.SuffixSN(ALRS.GetString("Reason "),ALRS.GetInt("DeviceLive.DeviceSN"))).attribute("TYPE", ALRS.GetInt("AlarmReason.Type")).up
    Loop
    al = al.up

    Dim props As Map
    props.Initialize
    props.Put("standalone", "no")
    props.Put("{http://xml.apache.org/xslt}indent-amount", "2")
    props.Put("indent", "yes")
    'resp.Write("Hello World")
    'Log(xb.asString2(props))
    Log(Count)
    'Subs.AlarmsList
    Log("--alarmlist--")
    RS.Close
    resp.Write(al.asString2(props).Replace($"<?xml version="1.0" encoding="UTF-8" standalone="no"?>"$, $"<?xml version="1.0" ?>"$))
   
   
End Sub

this is my handler class, the top part of the XML is to be that for all instances, that in the loop should, for each device spit out a line of xml for each alarm reason in the alarmreason table therefore device 007077 should have multiple lines of xml (lampfault through unspecified fault) then device 007078 should have the same lines and so on.

MariaDB [liteip]> desc AlarmReason;
+------------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+------------+-------------+------+-----+---------+----------------+
| AlarmIndex | int(11) | NO | PRI | NULL | auto_increment |
| Reason | varchar(50) | YES | | NULL | |
| Type | int(11) | YES | | NULL | |
+------------+-------------+------+-----+---------+----------------+

MariaDB [liteip]> desc AlarmTable;
+-------------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------------+---------+------+-----+---------+-------+
| DeviceSN | int(11) | NO | PRI | NULL | |
| ReasonIndex | int(11) | YES | MUL | NULL | |
| Status | int(11) | YES | | NULL | |
+-------------+---------+------+-----+---------+-------+

MariaDB [liteip]> desc DeviceLive;
+--------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------------+-------------+------+-----+---------+-------+
| DeviceSN | int(11) | NO | PRI | NULL | |
| SensorActive | varchar(20) | YES | | NULL | |
| LampOn | varchar(20) | YES | | NULL | |
| EmStatus | int(11) | YES | | NULL | |
+--------------+-------------+------+-----+---------+-------+

MariaDB [liteip]> SELECT * FROM AlarmReason;
+------------+-------------------+------+
| AlarmIndex | Reason | Type |
+------------+-------------------+------+
| 1 | Lamp Fault | 1 |
| 2 | Charge Fault | 1 |
| 3 | Battery Fault | 1 |
| 4 | Unspecified Fault | 1 |
| 5 | Lamp Fault | 1 |
| 6 | Charge Fault | 1 |
| 7 | Battery Fault | 1 |
| 8 | Unspecified Fault | 1 |
+------------+-------------------+------+

I'm currently going mad trying to work out how to do it for each deviceSN in DeviceLive...

Thankyou in advance!