XML PARSING

mallik4u · New User Joined: 17 Sep 2008 Posts: 75 Location: bangalore

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

GuyC · Posted: Fri Feb 27, 2015 5:33 pm

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(),",")'

mallik4u · New User Joined: 17 Sep 2008 Posts: 75 Location: bangalore

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?

GuyC · Posted: Wed Apr 01, 2015 2:12 pm

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

mallik4u · New User Joined: 17 Sep 2008 Posts: 75 Location: bangalore

Thanks Guyc.
It helps.