2 Replies Latest reply: Feb 6, 2013 3:42 AM by beta32c RSS

    Handling duplicate element names

    beta32c
      Hi,

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

      <Admin>
      <Information>
      <Company>
      <CompanyName>Apple</CompanyName>
      <CompanyCode>APP</CompanyCode>
      </Company>
      <TNumber>1-1-123456</TNumber>
      <RefrenceNumber>DA1101</RefrenceNumber>
      </Information>
      <Lists>
      <ListItem>
      <PolicyNumber>A100</PolicyNumber>
      <ListType OWNER="true" HOLDER="true">
      <CoverageValue>100</CoverageValue>
      </ListItem>
      <ListItem>
      <PolicyNumber>A200</PolicyNumber>
      <ListType OWNER="false" HOLDER="false">
      <CoverageValue>200</CoverageValue>
      </ListItem>
      <ListItem>
      <PolicyNumber>A300</PolicyNumber>
      <ListType OWNER="true" HOLDER="true">
      <CoverageValue>300</CoverageValue>
      </ListItem>
      </Lists>
      </Admin>


      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
      from XML_SAMPLE,

      xmltable(
      xmlnamespaces(DEFAULT 'http://www.xxx.com/Pway/Workfile')
      '$XS/Admin' passing object_value as "XS"
      columns
      TNumber VARCHAR2(255) PATH '/Admin/Information/TNumber',
      Item_Fragment XMLTYPE PATH 'Lists/ListItem'
      ) m,

      xmltable(
      xmlnamespaces(DEFAULT 'http://www.xxx.com/Pway/Workfile')
      '$LI/ListItem' passing m.Item_Fragment as "LI"
      columns
      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
          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 'http://www.xxx.com/Pway/Workfile')
            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 'http://www.xxx.com/Pway/Workfile')
           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  ;
           
          TNUMBER            LISTTYPEHOLDER     LISTTYPEOWNER    COVERAGEVALUE
          ------------------ ------------------ ---------------- ---------------
          1-1-123456         true               true             100
          1-1-123456         false              false            200
          1-1-123456         true               true             300
           
          (tested on 11.2.0.3)
          • 2. Re: Handling duplicate element names
            beta32c
            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.