title: DB2 UPDATE stored proc looping through INPUT XML
body: <p>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. </p>
  
  <p>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. </p>
  
  <p>I have the pseudo code for procedure, but not sure how it will look in DB2.</p>
  
  <p>The INPUT XML is of the FORMAT </p>
  
  <pre><code>&lt;Root&gt;
    &lt;Record&gt;
      &lt;a&gt;1234&lt;/a&gt;
      &lt;b&gt;1&lt;/b&gt;
      &lt;c&gt;2&lt;/c&gt;
      &lt;d&gt;A&lt;/d&gt;
      &lt;e&gt;B&lt;/e&gt;
      &lt;f&gt;C&lt;/f&gt;
    &lt;/Record&gt;
  &lt;Record&gt;
      &lt;a&gt;1235&lt;/a&gt;
      &lt;b&gt;1&lt;/b&gt;
      &lt;c&gt;2&lt;/c&gt;
      &lt;d&gt;A&lt;/d&gt;
      &lt;e&gt;B&lt;/e&gt;
      &lt;f&gt;C&lt;/f&gt;
    &lt;/Record&gt;
  &lt;/Root&gt;
  </code></pre>
  
  <p>The procedure will be similar to</p>
  
  <pre><code>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
  </code></pre>
  
  <p>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.</p>
  
  <p><strong>ANSWER</strong></p>
  
  <pre><code>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
  </code></pre>