((Application Express 4.1.1.00.23 11g)
So i have the following code:
with test_table as(
SELECT VM_REPORT_DATE,quantity,item from VM_CORE unpivot (
quantity fOR ITEM in (VM_HOSTS_NUM,VM_NUMBER,VM_PHYS_MEM,VM_VIRT_MEM,VM_CPU_COUNT,VM_TOTAL_DISK,VM_PROVISIONED_DISK)
) where VM_DCNAME='bc_production')
SELECT XMLSERIALIZE(CONTENT XMLELEMENT("SERIES",xmlAttributes(item as "name"),
XMLAGG(XMLElement("point",xmlAttributes(VM_REPORT_DATE as "name",quantity as "y")))
)) as THEDATA from test_table group by item;
Which successfully returns:
| THEDATA |
|---|
| <SERIES name="VM_CPU_COUNT"><point name="2013-10-29" y="1312"></point><point name="2013-10-23" y="1308"></point></SERIES> |
| <SERIES name="VM_HOSTS_NUM"><point name="2013-10-29" y="20"></point><point name="2013-10-23" y="22"></point></SERIES> |
| <SERIES name="VM_NUMBER"><point name="2013-10-29" y="617"></point><point name="2013-10-23" y="616"></point></SERIES> |
| <SERIES name="VM_PHYS_MEM"><point name="2013-10-29" y="4727.59"></point><point name="2013-10-23" y="5175.54"></point></SERIES> |
| <SERIES name="VM_PROVISIONED_DISK"><point name="2013-10-29" y="76307.65"></point><point name="2013-10-23" y="75848.3"></point></SERIES> |
| <SERIES name="VM_TOTAL_DISK"><point name="2013-10-29" y="95955"></point><point name="2013-10-23" y="93793.75"></point></SERIES> |
| <SERIES name="VM_VIRT_MEM"><point name="2013-10-29" y="3751.98"></point><point name="2013-10-23" y="3739.98"></point></SERIES> |
This is going to be the heart of needing to set an APEX variable to the above data with <DATA></DATA> tags added around it.
What I'm unsure how to do is return the above with a PLSQL variable of chart_series_data.
chart_series_data := '<data>'||chr(10);
chart_series_data := chart_series_data || "WHAT I RETURNED ABOVE";
chart_series_data := chart_series_data||chr(10)||'</data>';
Thanks
Rob