This discussion is archived
2 Replies Latest reply: Feb 6, 2013 1:42 AM by beta32c RSS

Handling duplicate element names

beta32c Newbie
Currently Being Moderated

I have been stuck with duplicate element name in my XML. Below is the skeleton of it...

<ListType OWNER="true" HOLDER="true">
<ListType OWNER="false" HOLDER="false">
<ListType OWNER="true" HOLDER="true">

The final Output list of Columns I will need are TNumber, ListTypeOwner,ListTypeHolder,CoverageValue

I loaded few XMLs into my XMLType table XML_SAMPLE

Below is the query i use... But every time I try i get no records. I do a sample query without reading the Tnumber I am able to read the values for the 2 XMLs. But when I do a extract for TNumber and the elements inside the ListItem I am not getting back anything.

Below is my query

select m.TNumber,l.ListTypeHolder,l.ListTypeOwner,l.CoverageValue

xmlnamespaces(DEFAULT '')
'$XS/Admin' passing object_value as "XS"
TNumber VARCHAR2(255) PATH '/Admin/Information/TNumber',
Item_Fragment XMLTYPE PATH 'Lists/ListItem'
) m,

xmlnamespaces(DEFAULT '')
'$LI/ListItem' passing m.Item_Fragment as "LI"
ListTypeHolder VARCHAR(255) PATH 'ListType/@HOLDER',
ListTypeOwner VARCHAR(255) PATH 'ListType/@OWNER',
CoverageValue VARCHAR(255) PATH 'CoverageValue'
) l
where l.ListTypeOwner="false";

I know its something to do the way the root tag or something. But i have tried out my options Please correct where I am making a mistake
  • 1. Re: Handling duplicate element names
    odie_63 Guru
    Currently Being Moderated
    Works for me, after correcting a few sytntax errors and wellformedness issue :
    SQL> select m.TNumber,l.ListTypeHolder,l.ListTypeOwner,l.CoverageValue
      2  from XML_SAMPLE
      3     , xmltable(
      4         xmlnamespaces(DEFAULT '')
      5       , '$XS/Admin' passing object_value as "XS"
      6         columns
      7           TNumber VARCHAR2(255) PATH 'Information/TNumber',
      8           Item_Fragment XMLTYPE PATH 'Lists/ListItem'
      9       ) m
     10     , xmltable(
     11         xmlnamespaces(DEFAULT '')
     12       , '$LI/ListItem' passing m.Item_Fragment as "LI"
     13         columns
     14           ListTypeHolder VARCHAR(255) PATH 'ListType/@HOLDER',
     15           ListTypeOwner VARCHAR(255) PATH 'ListType/@OWNER',
     16           CoverageValue VARCHAR(255) PATH 'CoverageValue'
     17       ) l
     18  --where l.ListTypeOwner = 'false'
     19  ;
    ------------------ ------------------ ---------------- ---------------
    1-1-123456         true               true             100
    1-1-123456         false              false            200
    1-1-123456         true               true             300
    (tested on
  • 2. Re: Handling duplicate element names
    beta32c Newbie
    Currently Being Moderated
    Thanks. I was looking at this peice of code and i knew i had made some mistake in the tags. Thanks I corrected it and it works.


  • Correct Answers - 10 points
  • Helpful Answers - 5 points