All Forums General
Sasi474 1 post Joined 10/14
28 Jul 2016
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.

 

You must sign in to leave a comment.