How to split the xml data?

[bharathappriyan]

Hi,

Could you please provide your suggestions?

I have table column with varchar(4000). It has data as below:

Record 1 has one xml.
Record 2 have two xmls.
Record 3 has one xml.
Record 5 has six xmls.

Requirement:

If the column has <CommentHeader> only xmls, then parse the data from commentBOdy and concatenate.
If the column has <CommentHistory> only xms, then ignore.
If the column has <CommentHeader> & <CommentHistory> xmls, then parse the commentBOdy data from <CommentHeader> xml only. Ignore the <CommentHistory>

Expected Output:
Record1: null - No data in the commentBody
Record2: Data2|Data2_1
Record3:
Record5: Data5

Record1:
<CommentHeader>Header1<CommentDate>2013-01-27-14.10.56.000000
</CommentDate><StageNbr>10</StageNbr></CommentHeader><CommentBody></CommentBody>


Record2:
<CommentHeader>Header2 <CommentDate>2013-01-29-08.57.55.000000
</CommentDate><StageNbr>10</StageNbr></CommentHeader><CommentBody>Data2</CommentBody>

<CommentHeader>Header2 <CommentDate>2013-01-28-15.49.57.000000
</CommentDate><StageNbr>10</StageNbr></CommentHeader><CommentBody>Data2_1</CommentBody>

Record3:

<CommentHistory><CommentHeader>Header3 <CommentDate>2013-01-29-07.57.35.000000
</CommentDate></CommentHeader><CommentBody><a Data3</a></CommentBody></CommentHistory>

Record5:
<CommentHeader>Header5 <CommentDate>2013-08-19-12.58.27.000000
</CommentDate><StageNbr>10</StageNbr></CommentHeader><CommentBody>Data5</CommentBody>

<CommentHistory><CommentHeader>Header5 <CommentDate>2013-08-19-12.57.06.000000
</CommentDate></CommentHeader><CommentBody><a Data5</a></CommentBody>

<CommentHeader>Header5 <CommentDate>2013-08-19-12.50.10.000000
</CommentDate></CommentHeader><CommentBody><a Data5</a></CommentBody>

<CommentHeader>Header5 <CommentDate>2013-08-13-09.18.26.000000
</CommentDate></CommentHeader><CommentBody><a Data5</a></CommentBody>

<CommentHeader>Header5 <CommentDate>2013-08-13-09.16.56.000000
</CommentDate></CommentHeader><CommentBody><a Data5</a></CommentBody>

<CommentHeader>Header5 <CommentDate>2013-08-13-09.15.46.000000
</CommentDate></CommentHeader><CommentBody><a Data5</a></CommentBody>
</CommentHistory>

I am using DataStage 8.5 Server Jobs, OS: Windows

Thanks,
Bharathappriyan