8 Replies Latest reply: Apr 13, 2012 3:44 AM by Stew Ashton RSS

    how to display the NULL records by multiple XMLTABLE()

    923578
      First, let me provide the testing data of INSERT and CREATE TABLE sql:
      create table xml_t2 (xml_raw XMLTYPE);
      insert sql, a bit long.....just copy and paste then run it will be OK!
      the system warns: Your message exceeds the maximum length of 30000 characters.
      So, I place it on the mediafire shariing space
      http://www.mediafire.com/?npmot6lgl86dgli
      Testing SQL:
      SELECT t1.years, t1.months,t1.days,t1.hours,t1.value,t2.value
                      
              FROM xml_t2,
              XMLTABLE ('$d/cdata/F1' passing xml_raw as "d" COLUMNS years integer path '//year', months varchar(3) path '//month', days varchar(2) path '//day', hours varchar(2) path '//hour', mins varchar(2) path '//minute', value float path '//value') t1 ,
              XMLTABLE ('$d/cdata/F2' passing xml_raw as "d" COLUMNS years integer path '//year', months varchar(3) path '//month', days varchar(2) path '//day', hours varchar(2) path '//hour', mins varchar(2) path '//minute', value float path '//value') t2
      The condition for now is....In the xml data, I have deleted the month 'Jan' with <F2>tag which is in 2010.
      That mean the output of XMLTABLE t2 will not contain the 'Jan' records but it is still remain in XMLTABLE t1.

      The problem is that when I use the above SQL to show the t1.value, it will not display the t1.value in 2010 Jan.....it just skipped 'Jan' and becomes 'Feb'...
      what i would prefer to display is something like this:
      YEARS MONTHS DAYS HOURS t1_value t2_value
      ----- ------ ---- ----- -------- --------
      2009     Jan    01   01       8       8
      2009     Jan    01   02       8     580
      2009     Jan    02   01     580     580
      2009     Jan    02   02     580     580
      2009     Feb    01   01     440     440
      .....................
      .....................
      2010     Jan    01   01     627     NULL
      2010     Jan    01   02     627     NULL
      2010     Jan    02   01     367     NULL
      2010     Jan    02   02     367     NULL
      2010     Feb    01   01     367     849
      2010     Feb    01   02     849     849
      ....................
      ..............
      So...the main problem is how to display back the record in JAN in 2010 in t1 XMLTABLE and assign null to the t2 XMLTABLE since it doen't contains this months record.

      THANKS for help!!
        • 1. Re: how to display the NULL records by multiple XMLTABLE()
          Stew Ashton
          SELECT t1.years, t1.months,t1.days,t1.hours,t1.value,t1.value2             
          FROM xml_t2,
          XMLTABLE ('$d/cdata' PASSING XML_RAW AS "d" COLUMNS 
          YEARS INTEGER PATH 'F1//year', 
          MONTHS VARCHAR(3) PATH 'F1//month', 
          DAYS VARCHAR(2) PATH 'F1//day', 
          HOURS VARCHAR(2) PATH 'F1//hour', 
          MINS VARCHAR(2) PATH 'F1//minute', 
          VALUE FLOAT PATH 'F1//value', 
          value2 float path 'F2//value') t1
          I feel sorry for you having to deal with such horrible XML.
          • 2. Re: how to display the NULL records by multiple XMLTABLE()
            923578
            thanks for your reply~~
            Can I do something with using more than 1 XMLTABLE() and display the same result that you have provided??
            Thanks~~~
            • 3. Re: how to display the NULL records by multiple XMLTABLE()
              923578
              hi,
              This solution is not work if I have created the index for the table.........
              It is neccssary to create an index since I have a large amount data in a table....
              So....how to solve this issue or any other ways to do it if I have created the index.......
              Thanks :)

              The index:
              create index xml_t2index on xml_t2(xml_raw)
              indextype IS xdb.xmlindex
              parameters ('PATH TABLE XML_T2_PATH_TABLE')
              • 4. Re: how to display the NULL records by multiple XMLTABLE()
                Stew Ashton
                The following code produces the desired result on version 11.2.0.2, and it does index range scans on the index.
                SELECT t1.years, t1.months,t1.days,t1.hours,t1.value,t1.value2             
                FROM xml_t2,
                XMLTABLE ('$d/cdata' PASSING XML_RAW AS "d" COLUMNS 
                YEARS INTEGER PATH 'F1/name/year', 
                MONTHS VARCHAR(3) PATH 'F1/name/month', 
                DAYS VARCHAR(2) PATH 'F1/name/day', 
                HOURS VARCHAR(2) PATH 'F1/name/hour', 
                MINS VARCHAR(2) PATH 'F1/name/minute', 
                VALUE FLOAT PATH 'F1/name/value', 
                VALUE2 FLOAT PATH 'F2/name/value') T1;
                If you think any of my answers are "helpful" or "correct" please mark them so.

                One XMLTABLE should generally perform better than two.
                • 5. Re: how to display the NULL records by multiple XMLTABLE()
                  923578
                  Yes, Sure!!
                  It runs prefectly~~Thanks a lot for your help :)

                  In addition, I would like to know the reason why can't use relative path...
                  • 6. Re: how to display the NULL records by multiple XMLTABLE()
                    odie_63
                    Hi,

                    What's your database version?

                    Are you using binary XML storage? (This is the default in 11.2.0.2)
                    If not you have to explicitely specify it :
                    create table xml_t2 (xml_raw XMLTYPE)
                    xmltype column xml_raw store as securefile binary xml;
                    Along with object-relational, it's the best storage strategy to use in conjunction with XMLIndex.

                    You should also use a structured XML index instead, it's more appropriate than the unstructured index you're currently using :
                    create index xml_t2_sxi on xml_t2 (xml_raw)
                    indextype is xdb.xmlindex
                    parameters (q'#
                    XMLTABLE xml_t2_xt '/cdata' 
                    COLUMNS 
                     YEARS  INTEGER    PATH 'F1/name/year', 
                     MONTHS VARCHAR(3) PATH 'F1/name/month', 
                     DAYS   VARCHAR(2) PATH 'F1/name/day', 
                     HOURS  VARCHAR(2) PATH 'F1/name/hour', 
                     MINS   VARCHAR(2) PATH 'F1/name/minute', 
                     VALUE  FLOAT      PATH 'F1/name/value', 
                     VALUE2 FLOAT      PATH 'F2/name/value'
                    #');
                    
                    call dbms_stats.gather_table_stats(user, 'XML_T2');
                    Use the query provided by Stew and check the explain plan, you'll see the difference.
                    • 7. Re: how to display the NULL records by multiple XMLTABLE()
                      odie_63
                      In addition, I would like to know the reason why can't use relative path...
                      I always wonder, why do people do that, using relative paths and wildcards?

                      You should always use a full path (unless the requirement really dictates otherwise).
                      This is especially true within Oracle XML DB where a lot of internal optimizations can take place.
                      Having the full path in the first place allows the CBO to rewrite the query and go straight to the data.
                      Using descendant axes ("//") forces to walk the tree down with different and more costly access paths.
                      • 8. Re: how to display the NULL records by multiple XMLTABLE()
                        Stew Ashton
                        I was hoping you would join this thread. Good stuff, thanks!