Java Database Connectivity (JDBC)

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.