Hi Experts
I've requirement to construct a valid XML instance using XMLConcat operator on multiple xmltypes. Then update XMLType column. So far, able to do this using PLSQL procedure.
PROCEDURE addAllBaselineObjects (
p_pguid IN VARCHAR2,
p_pversion IN VARCHAR2,
p_final_baseline_objects_table_type OUT basic_baseline_objects_table_type
) IS
xmlCnt XMLType;
BEGIN
dbms_output.put_line('Start addAllBaselineObjects '|| g_baseline_objects_repo_table_type.count);
FOR i in g_baseline_objects_repo_table_type.first..g_baseline_objects_repo_table_type.last LOOP
SELECT xmlConcat(xmlCnt,XMLELEMENT ("object",
XMLElement(
"meta",
XMLAttributes(g_baseline_objects_repo_table_type(i).guid AS "content", 'GUID' AS "name")
),
XMLElement(
"meta",
XMLAttributes(g_baseline_objects_repo_table_type(i).keys AS "content", 'key' AS "name")
)
))
INTO xmlCnt
FROM dual;
END LOOP;
UPDATE default_file_revision dfr
SET dfr.xml_content=
XMLQuery('copy $i := $p1 modify
(for $j in $i//xml/acms-publications
return (# ora:child-element-name object #)
{insert node $p2 into $j})
return $i'
PASSING dfr.xml_content AS "p1",
XMLElement("baseline",xmlCnt) AS "p2"
RETURNING CONTENT)
where dfr.file_revision_id = (
SELECT MAX(conref.file_revision_id)
FROM CONTENT_REFERENCES conref
WHERE conref.entry_name=p_pguid
AND conref.version=p_pversion
);
Now I need to do the same job in java. I did something below. Some how getting null values xmlConcat method.
It would be great if you can suggest any clue.
private void addBaselineInfo(List<BaselineAttributes> addBaselineList, String pubGuid , String pubVersion) {
String outParam = null;
XMLType xmlCont = null;
String xmlElement = null;
SQLXML xmlObject = null;
for(int i=1;i<3;i++) {
BaselineAttributes baselineAttributes = new BaselineAttributes();
baselineAttributes.guid = "GUID-"+i;
baselineAttributes.branchNumber = ""+i;
baselineAttributes.title = "Title"+i;
addBaselineList.add(baselineAttributes);
}
String addBaselineObjsQuery = "UPDATE default_file dfr\r\n"
+ " SET dfr.xml_content= \r\n"
+ " XMLQuery('copy $i := $p1 modify\r\n"
+ " (for $j in $i//xml/acms-pub\r\n"
+ " return (# ora:child-element-name object #)\r\n"
+ " {insert node $p2 into $j})\r\n"
+ " return $i'\r\n"
+ " PASSING dfr.xml_content AS \"p1\",\r\n"
+ " XMLElement(\"baseline\"," + xmlObject + ") AS \"p2\"\r\n"
+ " RETURNING CONTENT)\r\n"
+ " where dfr.file_revision_id = (\r\n"
+ " SELECT MAX(conref.file_id)\r\n"
+ " FROM CONTENT_REFERENCES conref\r\n"
+ " WHERE conref.entry_name='" + pubGuid + "'\r\n"
+ " AND conref.version='" + pubVersion+ "'\r\n"
+ " )";
for (Iterator iterator = addBaselineList.iterator(); iterator.hasNext();) {
BaselineAttributes baselineAttributes = (BaselineAttributes) iterator.next();
xmlElement = " XMLELEMENT (\"object\",\r\n"
+ " \r\n"
+ " XMLElement(\r\n"
+ " \"meta\", \r\n"
+ " XMLAttributes(" +baselineAttributes.guid+ " AS \"content\", 'GUID' AS \"name\")\r\n"
+ " ),\r\n"
+ " XMLElement(\r\n"
+ " \"meta\", \r\n"
+ " XMLAttributes(" + baselineAttributes.keys +" AS \"content\", 'key' AS \"name\")\r\n"
+ " )\r\n"
+ " )";
String xmlTypeConcat = " SELECT xmlConcat(" + xmlObject +", "+ xmlElement +")\r\n"
+ " AS xmlCnt\r\n"
+ " FROM dual \r\n";
try ( Connection dbConnection = AcmsGenericDAO.getInstance().getConnectionWithLabel("ABC");
PreparedStatement pstmt = dbConnection.prepareStatement(xmlTypeConcat );
ResultSet rs = pstmt.executeQuery()
){
logger.info("xmlTypeConcat : {}", xmlTypeConcat );
while (rs.next()) {
xmlCont = (XMLType)rs.getObject(1);
}
} catch (UniversalConnectionPoolException | SQLException exception) {
exception.printStackTrace();
logger.error(exception.getMessage(),exception);
}
}
Once I've XML Object ready, I can use the same in UPDATE query.
Thanks and regards,
Samba.