WSO2 6.5.0中XML元素迭代方法及CSV转XML插入多表求助

Hey there! Let's tackle your two questions about WSO2 EI 6.5.0 one by one, with practical, actionable examples you can use directly.

Absolutely, WSO2 EI 6.5.0 offers straightforward ways to iterate through XML elements. Here are the two most common approaches:

Using the Foreach Mediator #

The Foreach mediator is the standard tool for iterative XML processing. You define an XPath expression to target the elements you want to loop through, then execute a sequence for each element.

Example configuration:

<foreach expression="//orders/order" id="orderIterator">
    <sequence>
        <!-- Extract data from the current order element -->
        <property name="orderId" expression="//order/id" scope="default" type="STRING"/>
        <property name="customerName" expression="//order/customer/name" scope="default" type="STRING"/>
        
        <!-- Add your custom processing here (e.g., call an API, transform data) -->
        <log level="custom">
            <property name="Processing Order ID" expression="$ctx:orderId"/>
            <property name="Customer" expression="$ctx:customerName"/>
        </log>
    </sequence>
</foreach>

• The expression attribute uses XPath to select all <order> elements under <orders>.
• Each iteration processes one <order> element, and you can access its child nodes with relative XPath expressions.

Using a Script Mediator #

For more flexibility (like complex conditional logic), use a Script mediator with JavaScript or Java to traverse XML nodes.

JavaScript example:

<script language="js">
    // Fetch the full XML payload
    var payload = mc.getPayloadXML();
    // Select all <product> elements in the payload
    var products = payload.getElementsByTagName("product");
    
    for (var i = 0; i < products.length; i++) {
        var product = products[i];
        var productId = product.getElementsByTagName("id")[0].textContent;
        var productName = product.getElementsByTagName("name")[0].textContent;
        
        // Store values in properties for downstream processing
        mc.setProperty("currentProductId", productId);
        mc.setProperty("currentProductName", productName);
        
        // Log or process the data
        mc.getLog().info("Processing product: " + productName);
    }
</script>

Let's break this into step-by-step actions. We'll assume your CSV includes a column like sheet_name to track which original Excel worksheet each row came from (we'll also cover a workaround if this column doesn't exist).

Step 1: Read the CSV File #

Use a File Inbound Endpoint to listen for incoming CSV files and trigger your processing sequence:

<inboundEndpoint name="CSVFileInbound" onError="fault" protocol="file" sequence="CSVProcessingSequence" suspend="false">
    <parameters>
        <parameter name="interval">1000</parameter>
        <parameter name="transport.vfs.ActionAfterProcess">MOVE</parameter>
        <parameter name="transport.vfs.FileURI">file:///path/to/your/csv/input</parameter>
        <parameter name="transport.vfs.MoveAfterProcess">file:///path/to/processed/csv</parameter>
        <parameter name="transport.vfs.FileNamePattern">*.csv</parameter>
        <parameter name="transport.vfs.ContentType">text/csv</parameter>
    </parameters>
</inboundEndpoint>

Step 2: Convert CSV to Initial XML #

Use the Data Mapper mediator to convert your CSV into XML. Map each CSV column (including sheet_name) to XML elements. Your output might look like this:

<csv_data>
    <csv_row>
        <sheet_name>Customers</sheet_name>
        <id>1</id>
        <name>John Doe</name>
        <email>john@example.com</email>
    </csv_row>
    <csv_row>
        <sheet_name>Orders</sheet_name>
        <order_id>101</order_id>
        <customer_id>1</customer_id>
        <total>99.99</total>
    </csv_row>
</csv_data>

Step 3: Group Rows by Worksheet #

Use Foreach and Aggregate mediators to group rows by sheet_name so you can process each worksheet's data as a batch:

<!-- Iterate over unique sheet names -->
<foreach expression="//csv_row[not(sheet_name=preceding-sibling::csv_row/sheet_name)]" id="sheetGroupIterator">
    <sequence>
        <!-- Store the current sheet name in a property -->
        <property name="currentSheet" expression="//sheet_name" scope="default" type="STRING"/>
        
        <!-- Aggregate all rows belonging to this sheet -->
        <aggregate>
            <correlateOn expression="//sheet_name = get-property('currentSheet')"/>
            <completeCondition>
                <messageCount max="-1" min="-1"/>
            </completeCondition>
            <!-- Wrap aggregated rows and send to processing sequence -->
            <onComplete expression="//csv_row" enclosingElement="sheet_records" sequence="SheetDataProcessingSequence"/>
        </aggregate>
    </sequence>
</foreach>

Step 4: Convert Grouped Data to Target XML #

In the SheetDataProcessingSequence, use another Data Mapper to transform the grouped rows into the XML structure your database expects. For a Customers sheet, this might look like:

<customers>
    <customer>
        <id>1</id>
        <name>John Doe</name>
        <email>john@example.com</email>
    </customer>
    <!-- Additional customer rows -->
</customers>

Step 5: Insert into the Corresponding Database Table #

Use the DB Report mediator to insert the XML data into the table matching the currentSheet property. Ensure you've configured a database data source first.

<dbreport>
    <connection>
        <pool>
            <dsName>YourDatabaseDataSource</dsName>
        </pool>
    </connection>
    <statement>
        <!-- Dynamically use the sheet name as the table name -->
        <sql><![CDATA[INSERT INTO ${ctx:currentSheet} (id, name, email) VALUES (?, ?, ?)]]></sql>
        <parameter expression="//customer/id" type="INT"/>
        <parameter expression="//customer/name" type="VARCHAR"/>
        <parameter expression="//customer/email" type="VARCHAR"/>
    </statement>
</dbreport>

If Your CSV Doesn't Include a sheet_name Column #

If you named each CSV file with the sheet name (e.g., Customers.csv, Orders.csv), extract the sheet name from the filename using a property:

<property name="currentSheet" expression="fn:replace(get-property('transport.vfs.FileName'), '.csv', '')" scope="default" type="STRING"/>

Then use this property to group and insert data as above.

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