Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups Profile Log in to check your private messages Log in
 

 

XML PARSING

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
mallik4u

New User


Joined: 17 Sep 2008
Posts: 75
Location: bangalore

PostPosted: Thu Feb 26, 2015 9:27 pm    Post subject: XML PARSING
Reply with quote

hi,

I am trying to PARSE the XML similar to the following. I am able to PARSE it when i have only 1 <container> tag under <Containers>. But when it is more than 1, I am getting SQLCODE -16003. Any help will be greatly appreciated.

<Main>
<Containers>
<container>1</container>
<container>1</container>
</Containers>
<Items>
<Item name="abc"> I1 </Item>
<Item name="abc"> I2 </Item>
<Item name="abc"> I3 </Item>
</Items>
</Main>

SQL I am using:
SELECT X.*
FROM XMLTABLE ('$XMLINPUT/Main/Items/Item'
PASSING XMLPARSE(DOCUMENT
(TRIM('<Main>
<Containers>
<container>1</container>
<container>1</container>
</Containers>
<Items>
<Item name="abc"> I1 </Item>
<Item name="abc"> I2 </Item>
<Item name="abc"> I3 </Item>
</Items>
</Main>'))) as XMLINPUT
COLUMNS
item char(10) PATH '.',
ctr char(10) PATH '../../Containers/container'
) AS X

Thanks,
Mallik
Back to top
View user's profile Send private message

GuyC

Senior Member


Joined: 11 Aug 2009
Posts: 1278
Location: Belgium

PostPosted: Fri Feb 27, 2015 5:33 pm    Post subject:
Reply with quote

http://www.ibm.com/developerworks/data/library/techarticle/dm-0708nicola/#Handling_multiple_values_per_cell

which container do you want for each Item ?
first : use PATH '../../Containers/container[1]'
all : use PATH 'fn:string-join(../../Containers/container/text(),",")'
Back to top
View user's profile Send private message
mallik4u

New User


Joined: 17 Sep 2008
Posts: 75
Location: bangalore

PostPosted: Thu Mar 26, 2015 4:33 pm    Post subject:
Reply with quote

Thank you GuyC.
My requirement is to get all the details of <container> and <Item>. Both of them can be repeated.
Could you please let me know how we can both the tags?
Back to top
View user's profile Send private message
GuyC

Senior Member


Joined: 11 Aug 2009
Posts: 1278
Location: Belgium

PostPosted: Wed Apr 01, 2015 2:12 pm    Post subject:
Reply with quote

there is no relationship between container and item, so that would be 2 tables/2 selects

Code:
with myxml(doc) as (
select XMLPARSE(DOCUMENT
   (TRIM('<Main>
     <Containers>
       <container>1</container>
       <container>2</container>
     </Containers>
     <Items>
       <Item name="abc"> I1 </Item>
       <Item name="abc"> I2 </Item>
       <Item name="abc"> I3 </Item>
     </Items>
   </Main>'))) as myxmldoc
 from sysibm.sysdummy1
)
SELECT 'Item', X.*
  FROM myxml
     , XMLTABLE ('$XMLINPUT/Main/Items/Item'
        PASSING doc as XMLINPUT
          COLUMNS item char(10) PATH '.'
       ) AS X
union all
SELECT 'ctr', y.*
  FROM myxml
    , XMLTABLE ('$XMLINPUT/Main/Containers/container'
        PASSING doc as XMLINPUT
         COLUMNS ctr char(10) PATH '.'
        ) AS y
Back to top
View user's profile Send private message
mallik4u

New User


Joined: 17 Sep 2008
Posts: 75
Location: bangalore

PostPosted: Wed Apr 01, 2015 2:16 pm    Post subject: Reply to: XML PARSING
Reply with quote

Thanks Guyc.
It helps.
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2 All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts Parsing and Listing a Subset of Recor... Charles Wolters IBM Tools 3 Fri Feb 05, 2016 10:39 pm
No new posts Parsing JCL SET var=value statements prino CLIST & REXX 0 Sat Dec 26, 2015 4:41 am
No new posts XML Parsing Issue JnanaR COBOL Programming 8 Sat Jul 19, 2014 12:23 pm
No new posts XML-CODE 000798818 while parsing XML ... dsivapradeep COBOL Programming 16 Wed Mar 20, 2013 12:41 pm
No new posts Parsing of XML file into flat file th... ibmbansal08 DFSORT/ICETOOL 5 Mon Oct 08, 2012 7:23 pm


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us