Search code examples
xmlstored-procedurescursordb2batch-processing

DB2 UPDATE stored proc looping through INPUT XML

I'm new to DB2 and stored procedures. I want to write a stored procedure in DB2 which will update the table. The procedure has an XML document as its input parameter.

I need to loop through each record of XML, pick some nodes and update corresponding rows in table. This stored procedure will be used to batch update the table.

I have the pseudo code for procedure, but not sure how it will look in DB2.

The INPUT XML is of the FORMAT 

<Root>
  <Record>
    <a>1234</a>
    <b>1</b>
    <c>2</c>
    <d>A</d>
    <e>B</e>
    <f>C</f>
  </Record>
<Record>
    <a>1235</a>
    <b>1</b>
    <c>2</c>
    <d>A</d>
    <e>B</e>
    <f>C</f>
  </Record>
</Root>

The procedure will be similar to

CREATE PROCEDURE UPDATE_BATCH (IN INDOC XML)
P1: BEGIN

FOR rec AS rec CURSOR FOR(
SELECT 
Record.XMLQUERY('//Record/a/text()') AS A, 
Record.XMLQUERY('//Record/b/text()') AS B,
Record.XMLQUERY('//Record/c/text()') AS C,
Record.XMLQUERY('//Record/d/text()') AS D,
Record.XMLQUERY('//Record/e/text()') AS E,
Record.XMLQUERY('//Record/f/text()') AS F
FROM
TABLE (INDOC)Record--Not Sure how to construct table from input xml
)
DO
UPDATE XYZ.TEMP_TABLE
SET ACOL=Record.A,
BCOL=Record.B,
CCOL=Record.C,
DCOL=Record.D,
ECOL=Record.E
WHERE
FCOL=Record.F;
END FOR;
END P1

Please help me to create above procedure. I'm not able to get correct syntax for ForEach, XML node handling, CURSOR and LOOPING in DB2.

ANSWER

CREATE PROCEDURE UPDATE_BATCH(IN DOC XML)
BEGIN
MERGE INTO XYZ.TEMP_TABLE AS T
USING (SELECT X.* FROM 
XMLTABLE('$d/Root/Record' passing DOC as "d"
COLUMNS 
        "A" VARCHAR(10) PATH 'a',
        "B" VARCHAR(10) PATH 'b',
        "C" VARCHAR(10) PATH 'c',
        "D" VARCHAR(10) PATH 'd',
        "E" VARCHAR(10) PATH 'e',
        "F" VARCHAR(10) PATH 'f' 
) AS X) AS XT
ON T.FCOL=XT."F"
WHEN MATCHED THEN
UPDATE
SET
T.ACOL=XT."A",
T.BCOL=XT."B",
T.COL=XT."C"
END
Solution

  • Db2 has decent support for XML data, so you have a few options on how to shred XML and apply it to one or more tables.

    The function you're looking for is XMLTABLE. It is by far the most flexible way to shred XML into a single tabular result set.

    When your incoming XML document contains data that needs to go to multiple tables, the DECOMPOSE XML DOCUMENT statement is powerful, but it requires a pre-registered XML Schema Document (XSD) containing special annotations that specify the relational target for each input XML node. Moreover, the DECOMPOSE XML DOCUMENT statement can only write to permanent tables and not to declared global temporary tables (DGTTs) which are often used for staging and adjusting data before it gets written to its final destination.

    As for using a cursor to apply the shredded data, you could replace the entire loop with a single MERGE statement that uses the result set from XMLTABLE as the input expression. There are lots of examples showing how to use Db2's MERGE statement for "upsert" processing, in which each incoming row may be applied by either an INSERT or an UPDATE depending on whether the specified key already exists in the destination table.