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
How to split the xml data?
Post questions here relative to DataStage Server Edition for such areas as Server job design, DS Basic, Routines, Job Sequences, etc.
Moderators: chulett, rschirm, roy
-
- Participant
- Posts: 47
- Joined: Fri Sep 23, 2005 6:01 pm
Return to “IBM<sup>®</sup> Infosphere DataStage Server Edition”
Jump to
- Moderators' Choice
- ↳ Editor's BLOG Corner
- ↳ Ask the Experts! - Dads and Grads
- ↳ DSXchange Testimonials
- ↳ Cognos (IBM BI)
- FAQs
- ↳ FAQs
- ↳ FAQ Discussion
- DataStage
- ↳ General
- ↳ IBM<sup>®</sup> Infosphere DataStage Server Edition
- ↳ IBM<sup>®</sup> DataStage Enterprise Edition (Formerly Parallel Extender/PX)
- ↳ Archive of DataStage Users@Oliver.com
- IBM<sup>®</sup>Infosphere Products<sup></sup>
- ↳ Business Glossary
- Suggestions
- ↳ Site/Forum
- ↳ Enhancement Wish List
- Consulting
- ↳ Talent
- ↳ Looking for Talent
- Support
- ↳ Parameter Manager
- ↳ Compile All Plus
- Usergroup Forums
- ↳ Usergroup Central Forum
- ↳ Heartland Usergroup Forum
- The Written Word
- ↳ Articles, White Papers and Tips and Tricks
- ↳ Product Documentation
- Third Party Applications
- ↳ Third Party Applications
- Product Derivatives
- ↳ Functions
- ↳ Routines
- ↳ Jobs
- ↳ Logs
- Tools
- ↳ Tools Forum
- Category
- ↳ Infosphere Master Data Management
- ↳ Data Quality Best Practices
- ↳ IBM QualityStage
- ↳ Information Analyzer (formerly ProfileStage)
- ↳ IBM<sup>®</sup> SOA Editions (Formerly RTI Services)
- ↳ IBM<sup>®</sup> DataStage TX
- ↳ BI
- ↳ Data Integration