Writing to the database

To write to the database, apply a stored procedure and use the ExtractValue() function:

helper functions:
-- this function returns the expression that count the occurrence of the searchExpr

FUNCTION `createCountExpression`(searchExpr varchar(50)) RETURNS varchar(100) CHARSET latin1
BEGIN
    declare result varchar(100) default "";
    set result = concat("count(",searchExpr,")");
    return result;
END
 
-- this function create the extract expression to get the value of the paramter tag[idx]

FUNCTION `createExtractExpression`(searchExpression varchar(50), idx int, tag varchar(15)) RETURNS varchar(100) CHARSET latin1
BEGIN
    declare result varchar(100) default "";

    set result = concat(searchExpression, "[", idx, "]/", tag);
    return result;
END


 
-- this procedure stores the data of a MySalesOrder bod into the
-- database

PROCEDURE `StoreSalesOrder`(xmlData blob)
BEGIN

    declare totalRecords int;
    declare salesOrderExpr varchar(30);
    declare countExpr varchar(30);
    declare idx int default 1;
    declare extractExpr varchar(30);

    -- field variables
    declare id int;
    declare orderStatus varchar(10);
    declare lineNumber int;
    declare item varchar(45);
    declare quantity decimal(10,0);
    declare price decimal(10,2);

    declare prefId int default 0;

    -- Create the expression for the salesOrder tag
    set salesOrderExpr = concat("//", "MySalesOrder");

    -- Create the expressin to count the number of salesorders/lines
    set countExpr = createCountExpression(salesOrderExpr);

    set totalRecords = ExtractValue(xmlData, countExpr);


    while idx <= totalRecords do
        -- look for order id
        set extractExpr = createExtractExpression(salesOrderExpr, idx, "orderID");
        set id = ExtractValue(xmlData, extractExpr );

        -- look for order status
        set extractExpr = createExtractExpression(salesOrderExpr, idx, "orderStatus");
        set orderStatus = ExtractValue(xmlData, extractExpr );

        -- look for order line
        set extractExpr = createExtractExpression(salesOrderExpr, idx, "lineNumber");
        set lineNumber = ExtractValue(xmlData, extractExpr );

        -- look for item
        set extractExpr = createExtractExpression(salesOrderExpr, idx, "item");
        set item = ExtractValue(xmlData, extractExpr );

        -- look for quantity
        set extractExpr = createExtractExpression(salesOrderExpr, idx, "quantity");
        set quantity = ExtractValue(xmlData, extractExpr );

        -- look for price
        set extractExpr = createExtractExpression(salesOrderExpr, idx, "price");
        set price = ExtractValue(xmlData, extractExpr );

        -- insert data
        -- insert orders (header) if necessary
        if prefid <> id then
            insert into orders (orderNumber, orderStatus)
            values (id, orderStatus);
            call log(id);
            call log(orderStatus);
            set prefid = id;
        end if;