1 Reply Latest reply: Mar 16, 2012 3:15 PM by Jason_(A_Non) RSS

    Exclude elements in XML extraction

    922621
      I have an XML file with this basic structure
        <?xml version="1.0" encoding="UTF-8" ?> 
      - <generic-cv:generic-cv lang="en" dateTimeGenerated="2012-01-18 09:57:44" dateTimeSubmitted="2012-01-18 09:57:44" ccvIdentifier="139794" templateName="CIHR-Registration-Test" confirmationNumber="42" agencyUUID="640a3a65da1946cbb2e3da9ae9c27edf" internalResearcherUUID="1036d54aa4f2462c887558ef25053b30" xmlns:generic-cv="http://www.cihr-irsc.gc.ca/generic-cv/1.0.0">
      + <section id="2687e70e5d45487c93a8a02626543f64" label="Identification" primaryIndicator="false">
      - <field id="ee8beaea41f049d8bcfadfbfa89ac09e" label="Title">
        <lov id="00000000000000000000000000000319">Mrs.</lov> 
        </field>
      - <field id="5c6f17e8a67241e19667815a9e95d9d0" label="Family Name">
        <value type="String">Boucher</value> 
        </field>
      - <field id="98ad36fee26a4d6b8953ea764f4fed04" label="First Name">
        <value type="String">Lucie</value> 
        </field>
        </section>
      + <section id="53099556bdac4ac8813be6fd62a356b1" label="Research Specialization Keywords" primaryIndicator="false">
      - <field id="6351fbc0f45f4266b29f292fd521c67a" label="Research Specialization Keywords">
        <value type="String">Biology</value> 
        </field>
        </section>
      - <section id="0ac7dc1861544712bc2114ef2eaee8bd" label="Disciplines Trained In" primaryIndicator="false">
      - <field id="eb2fa5bb13ec4c8099969ae7c70d404d" label="Discipline Trained In">
      - <refTable refValueId="00000000000000000000075959090150" label="Research Discipline">
        <linkedWith label="Sector of Discipline" value="Arts and literature" /> 
        <linkedWith label="Field" value="Arts and Literature Studies" /> 
        <linkedWith label="Discipline" value="Musicology" /> 
        </refTable>
        </field>
        </section>
      The following code is extracting the data:
      SELECT x2.e_field, x1.e_section, NVL2(x2.value,x2.value,x2.lov), NULL, NULL
            FROM XMLTABLE (XMLNAMESPACES('http://www.cihr-irsc.gc.ca/generic-cv/1.0.0' AS "dc"), '/dc:generic-cv/section/section'
                  PASSING XMLTYPE(bfilename('CDRFILES', '#CCV#-42-139794-CIHR-Registration-Test.xml'),NLS_CHARSET_ID('AL32UTF8'))
                     COLUMNS e_section      VARCHAR2(32) PATH '@label'
                           , field_xml      XMLType      PATH 'field'
                            ) x1
                  , XMLTABLE('/field'
                     PASSING x1.field_xml
                     COLUMNS e_field        VARCHAR2(32) PATH '@label'
                           , value          VARCHAR2(50) PATH 'value'
                           , lov            VARCHAR2(50) PATH 'lov'
                           , reftable_xml   XMLType      PATH 'refTable'
                            ) x2
      But I want to exclude the <section> elements with <refTable> and <linkedWith> elements. I tired using NOT XMLEXISTS but I can't get it to work.
      Any advice?

      BANNER
      --------------------------------------------------------------------------------
      Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production
      PL/SQL Release 11.2.0.3.0 - Production
      CORE 11.2.0.3.0 Production
      TNS for 32-bit Windows: Version 11.2.0.3.0 - Production
      NLSRTL Version 11.2.0.3.0 - Production

      Karen
        • 1. Re: Exclude elements in XML extraction
          Jason_(A_Non)
          Thank you for using the code tag to format your XML and query.

          I'm not 100% sure what you are looking for since there is no expected output, nor is the XML snippet valid nor does it work with the provided query.

          Tip: Do not copy XML from a browser window as that includes - and + characters which are not part of the XML. Doe a View Page Source (whatever your browser calls it) or open the XML in Notepad or some other text editor.

          Here's a stab at what I think you are wanting. I simply embedded the XML in the query for ease of testing.
          SELECT x2.e_field, x1.e_section, NVL2(x2.value,x2.value,x2.lov), NULL, NULL
                FROM XMLTABLE (XMLNAMESPACES('http://www.cihr-irsc.gc.ca/generic-cv/1.0.0' AS "dc"), 
                      '/dc:generic-cv/section[not(field/refTable)]'
                      PASSING XMLTYPE('<?xml version="1.0" encoding="UTF-8"?>
          <dc:generic-cv lang="en" dateTimeGenerated="2012-01-18 09:57:44" dateTimeSubmitted="2012-01-18 09:57:44" ccvIdentifier="139794" templateName="CIHR-Registration-Test" confirmationNumber="42" agencyUUID="640a3a65da1946cbb2e3da9ae9c27edf" internalResearcherUUID="1036d54aa4f2462c887558ef25053b30" xmlns:dc="http://www.cihr-irsc.gc.ca/generic-cv/1.0.0">
             <section id="2687e70e5d45487c93a8a02626543f64" label="Identification" primaryIndicator="false">
                <field id="ee8beaea41f049d8bcfadfbfa89ac09e" label="Title">
                   <lov id="00000000000000000000000000000319">Mrs.</lov>
                </field>
                <field id="5c6f17e8a67241e19667815a9e95d9d0" label="Family Name">
                   <value type="String">Boucher</value>
                </field>
                <field id="98ad36fee26a4d6b8953ea764f4fed04" label="First Name">
                   <value type="String">Lucie</value>
                </field>
             </section>
             <section id="53099556bdac4ac8813be6fd62a356b1" label="Research Specialization Keywords" primaryIndicator="false">
                <field id="6351fbc0f45f4266b29f292fd521c67a" label="Research Specialization Keywords">
                   <value type="String">Biology</value>
                </field>
             </section>
             <section id="0ac7dc1861544712bc2114ef2eaee8bd" label="Disciplines Trained In" primaryIndicator="false">
                <field id="eb2fa5bb13ec4c8099969ae7c70d404d" label="Discipline Trained In">
                   <refTable refValueId="00000000000000000000075959090150" label="Research Discipline">
                      <linkedWith label="Sector of Discipline" value="Arts and literature"/>
                              <linkedWith label="Field" value="Arts and Literature Studies"/>
                              <linkedWith label="Discipline" value="Musicology"/>
                         </refTable>
                    </field>
               </section>
          </dc:generic-cv>')
                         COLUMNS e_section      VARCHAR2(32) PATH '@label'
                               , field_xml      XMLType      PATH 'field'
                                ) x1
                      , XMLTABLE('/field'
                         PASSING x1.field_xml
                         COLUMNS e_field        VARCHAR2(32) PATH '@label'
                               , value          VARCHAR2(50) PATH 'value'
                               , lov            VARCHAR2(50) PATH 'lov'
                               , reftable_xml   XMLType      PATH 'refTable'
                                ) x2
          I added the
          [not(field/refTable)]
          to your XPath to tell it to not return any section nodes that had a field/refTable child node.

          That produced the following 4 rows
          E_FIELD                          E_SECTION                        NVL2(X2.VALUE,X2.VALUE,X2.LOV) NULL NULL
          -------------------------------- -------------------------------- ------------------------------ ---- ----
          Title                            Identification                   Mrs.                                                    
          Family Name                      Identification                   Boucher                                                 
          First Name                       Identification                   Lucie                                                   
          Research Specialization Keywords Research Specialization Keywords Biology                                                 
           
          and the query returned 5 rows before I made that addition.