Groovy递归解析XML多标签并插入Oracle数据库异常排查

Let's break down why your code isn't producing output or inserting records, and fix it step by step:

1. The Root Issue: XML Namespaces & Incorrect Node Path #

From the Salesforce SOAP response structure you shared, the XML uses namespaces (like soapenv for the envelope, and sf for Salesforce-specific nodes) which your current code isn't accounting for. XmlSlurper won't recognize nodes with namespaces unless you explicitly map them, and your node path is missing parent nodes like Body that wrap queryResponse.

Fix: Map Namespaces & Correct the Node Hierarchy #

First, declare the namespaces used in your XML when initializing XmlSlurper. Then adjust the traversal path to match the actual XML structure:

File SFDCResponseFile = new File("/drxe2o/admin/Integration/odi/reference_data/sfdc/copy_center/Sample.xml")
if (!SFDCResponseFile?.exists()) {
 throw new RuntimeException("No SFDC Query Response file ${SFDCResponseFile.absolutePath}")
}

def myCon = odiRef.getJDBCConnection("SRC")
// Disable auto-commit to control transaction
myCon.setAutoCommit(false)
def slurper = new XmlSlurper()
    .declareNamespace(
        soapenv: "http://schemas.xmlsoap.org/soap/envelope/",
        sf: "urn:partner.soap.sforce.com"
    )
    .parse(SFDCResponseFile)

// Correct path with namespaces and proper hierarchy
def records = slurper.soapenv:Body.sf:queryResponse.sf:result.sf:records
println "Found ${records.size()} records in XML" // Verify record count first

try {
    // Use PreparedStatement to avoid SQL injection and handle nulls safely
    def insertSql = """
        INSERT INTO ${odiRef.getSchemaNameDefaultPSchema("LS_ODI_WORK","D")}.RX_ODI_TAGVALUE_TEMP 
        (EVENT_CODE, EVENT_EDITION, ORDER_SUMMARY_NUMBER) 
        VALUES (?, ?, ?)
    """.trim()
    def pstmt = myCon.prepareStatement(insertSql)

    records.each { record ->
        // Extract values, handle empty nodes gracefully
        def eventCode = record.sf:Event_Code__c.text() ?: null
        def eventEdition = record.sf:Event_Edition__c.text() ?: null
        def orderSummaryNum = record.sf:Order_Summary_Number__c.text() ?: null

        pstmt.setString(1, eventCode)
        pstmt.setString(2, eventEdition)
        pstmt.setString(3, orderSummaryNum)
        
        def rowsAffected = pstmt.executeUpdate()
        println "Inserted $rowsAffected row for Event Code: $eventCode"
    }

    // Commit the transaction
    myCon.commit()
    println "All records inserted successfully"
} catch (Exception e) {
    // Rollback on error
    myCon.rollback()
    println "Error occurred: ${e.message}"
    e.printStackTrace()
} finally {
    // Clean up resources
    if (pstmt) pstmt.close()
    if (myCon) myCon.close()
}

2. Additional Fixes & Improvements #

• Transaction Management: ODI JDBC connections often don't auto-commit by default. Explicitly commit() after inserts, and rollback() on errors to avoid partial inserts.
• PreparedStatement: Replaced string concatenation with prepared statements to prevent SQL injection and handle null values correctly (your original code would break if any XML node was empty).
• Debug Output: Added println statements to verify how many records are found in the XML, and track each insert operation—this helps you confirm if the issue is with XML parsing or database insertion.
• Resource Cleanup: Added a finally block to close JDBC resources, preventing connection leaks.

3. How to Verify the Fix #

1. Run the modified code and check the console output:
   • If Found 0 records in XML prints, double-check the namespace URIs (they must match exactly what's in your XML's xmlns attributes) and node names (case-sensitive!).
   • If records are found but inserts fail, the error message in the catch block will tell you exactly what's wrong (e.g., missing columns, data type mismatches).

内容的提问来源于stack exchange，提问作者jetal