This discussion is archived
1 Reply Latest reply: Mar 16, 2012 1:15 PM by Jason_(A_Non) RSS

Exclude elements in XML extraction

922621 Newbie
Currently Being Moderated
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) Expert
    Currently Being Moderated
    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.

Legend

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