I am having trouble populating a nested XML structure using SQL in my Controller. The underlying database tables have the same structure with a one to many relationship between keys and access time. In the Dashboard I can see that the Key_Access_Time is retrieved and renamed to /mvc:eForm/mvc :D ata/sql_result[last()]/KEY_REQUEST_ACCESS_TIME, but when I try to move it into the XML location /mvc:eForm/mvc :D ata/sql_result[last()]/KEY_REQUEST_KEYS using the move action, Webmaker is unable to update the factbase.
I have also attempted using the Oracle dbms_xmlgen.getxml function in my controller. This works fine from an Oracle prompt outside of Webmaker, but returns an invalid character when run in a Webmaker controller.
Any input would be greatly appreciated.
Thanks
Chuck
The XML structure looks like this
<KEY_REQUEST_KEYS xmlns="">
<record>
<PROCID />
<LN />
<REQUEST_TYPE />
<LOCKBOX />
<KEY_TYPE />
<BUILDING />
<ROOM />
<KEY_CLASS />
<PIN_REQUIRED />
<APPROVED />
<EFFECTIVE_DATE />
<EXPIRATION_DATE />
<BUILDING_NAME_F />
<KEY_CLASS_F />
<KEY_REQUEST_ACCESS_TIME>
<record>
<PROCID />
<KEYLN />
<LN />
<SUNDAY />
<MONDAY />
<TUESDAY />
<WEDNESDAY />
<THURSDAY />
<FRIDAY />
<SATURDAY />
<START_TIME />
<END_TIME />
</record>
</KEY_REQUEST_ACCESS_TIME>
</record>
</KEY_REQUEST_KEYS>
I have also attempted using the Oracle dbms_xmlgen.getxml function in my controller. This works fine from an Oracle prompt outside of Webmaker, but returns an invalid character when run in a Webmaker controller.
Any input would be greatly appreciated.
Thanks
Chuck
The XML structure looks like this
<KEY_REQUEST_KEYS xmlns="">
<record>
<PROCID />
<LN />
<REQUEST_TYPE />
<LOCKBOX />
<KEY_TYPE />
<BUILDING />
<ROOM />
<KEY_CLASS />
<PIN_REQUIRED />
<APPROVED />
<EFFECTIVE_DATE />
<EXPIRATION_DATE />
<BUILDING_NAME_F />
<KEY_CLASS_F />
<KEY_REQUEST_ACCESS_TIME>
<record>
<PROCID />
<KEYLN />
<LN />
<SUNDAY />
<MONDAY />
<TUESDAY />
<WEDNESDAY />
<THURSDAY />
<FRIDAY />
<SATURDAY />
<START_TIME />
<END_TIME />
</record>
</KEY_REQUEST_ACCESS_TIME>
</record>
</KEY_REQUEST_KEYS>
RE: Populating a nested XML structure
From the info you have provided I am assuming that you are doing one SQL query to retrieve a single 'key' record which is giving you the KEY_REQUEST_KEYS structure. You are then trying to do a second SQL query to retrieve all the access times for this key, which will create the KEY_REQUEST_ACESS_TIME fragment that you want to place within the key details.
If this is the case, then I think you are not far off, but just need to adjust the 'to location' XPath for your move action. The 'unable to update factbase' error message generally means that the location you have asked the data to go to does not exist and so it can+?+?+?t carry out the move operation.
The easiest way to resolve this is to use the 'View Rules' link from the relevant point (just before the move) in the Debugger trace. This should show the actual data in the Rules screen, and you can just drag the KEY_REQUEST_KEYS element from the XML into the to location XPath box to make sure you have the correct XPath.
(For info, one thing that is likely wrong with your current XPath is that by this point you have done a second SQL query, and so the KEY_REQUEST_KEYS element wont be within the last sql_result element, and if you are using the default generated actions, it wont be within a sql_result element at all.)
Using the real data from the Debugger like this is generally a good way to check and update your XPaths as needed whenever you have errors with an action.
I hope this is useful.
If you are still having problems it would be great if you could provide some more details on your exact scenario, and I can try and help further.
Regards,
Gerard
RE: Populating a nested XML structure
I was able to get this to work, but instead of doing 2 separate SQL statements, I ended up writing a simple PL/SQL function that returns the XML. The function utilizes the Oracle function dbms_xmlgen.getxml and returns a CLOB of the XML that I then convert to properly formatted and nested XML in my controller.
I really appreciate your help.
Cheers
Chuck
Call to Oracle stored function in the controller
select getKeyRequestXML($procid) AS KEYXML from dual;
PL/SQL Function
CREATE OR REPLACE FUNCTION getKeyRequestXML(procid_in IN NUMBER)
RETURN CLOB IS
keyRequestXML CLOB := '';
BEGIN
SELECT dbms_xmlgen.getxml('select a.*,cursor(select * from key_request_access_time b where b.procid = a.procid and b.keyln = a.ln ) key_request_access_time from key_request_keys a where a.procid = '|| procid_in || '')
INTO keyRequestXML FROM dual;
RETURN keyRequestXML;
END;
/
RE: Populating a nested XML structure
I+?+?+?m glad you managed to get it working, and thanks for providing the details of your approach.
That Oracle function is not one I have used before so it is very useful to see a real example.
Thanks,
Gerard