Selecting XML data (converting xml data to rows and columns)

General

Selecting XML data (converting xml data to rows and columns)

Hi,

Trying to extract all the colums from below table 

CREATE SET TABLE A,NO FALLBACK ,

     NO BEFORE JOURNAL,

     NO AFTER JOURNAL,

     CHECKSUM = DEFAULT,

     DEFAULT MERGEBLOCKRATIO

     (

      RQST_NUM INTEGER,

      ACTVTY_LOG XML)

PRIMARY INDEX ( RQST_NUM );

Query:

SELECT SRC.RQST_NUM, X.*

FROM (SELECT RQST_NUM, 

ACTVTY_LOG  

FROM A

) AS SRC, 

XMLTABLE (

'/variable/item'

PASSING SRC.ACTVTY_LOG

COLUMNS

"comments" VARCHAR(10000)PATH 'comments',

"dateTime" VARCHAR(10000) PATH 'dateTime', 

"action" VARCHAR(10) PATH 'action'

) AS X

Sample XML data:

For Rqst_num 1:

'<?xml version="1.0" encoding="UTF-8"?>

<variable type="ActivityDetails[]">

  <item type="ActivityDetails">

    <comments type="String"><![CDATA[11111]]></comments>

    <dateTime type="Date"><![CDATA[2016/07/14 13:43:35.669 CDT]]></dateTime>

    <action type="String"><![CDATA[Submit]]></action>

    <incomplianceComments type="String" />

  </item>

  <item type="ActivityDetails">

    <comments type="String"><![CDATA[Testing]]></comments>

    <dateTime type="Date"><![CDATA[2016/07/14 13:44:33.742 CDT]]></dateTime>

    <action type="String"><![CDATA[Approve]]></action>

    <incomplianceComments type="String" />

  </item>

</variable>'

For Rqst_num 2:

'<?xml version="1.0" encoding="UTF-8"?>

<variable type="ActivityDetails[]">

  <item type="ActivityDetails">

    <comments type="String"><![CDATA[11111]]></comments>

    <dateTime type="Date"><![CDATA[2015/07/14 13:43:35.669 CDT]]></dateTime>

    <action type="String"><![CDATA[pending]]></action>

    <incomplianceComments type="String" />

  </item>

  <item type="ActivityDetails">

    <comments type="String"><![CDATA[Testing]]></comments>

    <dateTime type="Date"><![CDATA[2015/07/14 13:44:33.742 CDT]]></dateTime>

    <action type="String"><![CDATA[pending]]></action>

    <incomplianceComments type="String" />

  </item>

</variable>'

Expected result is to get two rows for each rqst_num. But instead I am getting 8 rows(cross join is happening) in the result set.

Can anybody help with this issue.