2 Replies Latest reply on Mar 13, 2012 8:22 AM by Boneist

    XMLTABLE - help required extracting a single node

    Boneist
      (Oracle 10.2.0.4)

      Hi,

      Due to some dodgy front end stuff, my database ends up being passed xml that's more or less of the following format:
      CREATE TABLE xml_test
      (id integer,NOTEBOOK XMLTYPE);
       
      insert into xml_test values (1, '<?xml version="1.0" encoding="UTF-8"?>
      <Product Type=''Laptop''>
      <Notebook Brand="HP" Model="Pavilion dv6-3132TX Notebook">
      <Harddisk>640 GB</Harddisk>
      <Processor>Intel Core i7</Processor>
      <RAM>4 GB</RAM>
      <Example>
        <Example_sub>a</Example_sub>
        <Example_sub>b</Example_sub>
        <Example_sub>c</Example_sub>
        <Example_sub>d</Example_sub>
      </Example>
      </Notebook>
      <Notebook Brand="HP" Model="HP Pavilion dv6-3032TX Notebook">
      <Harddisk>640 GB</Harddisk>
      <Processor>Intel Core i7</Processor>
      <RAM>6 GB</RAM>
      <Example>
        <Example_sub>a</Example_sub>
        <Example_sub>b</Example_sub>
        <Example_sub>c</Example_sub>
        <Example_sub>d</Example_sub>
      </Example>
      </Notebook>
      <Notebook Brand="Toshiba" Model="Satellite A660/07R 3D Notebook">
      <Harddisk>640 GB</Harddisk>
      <Processor>Intel Core i7</Processor>
      <RAM>4 GB</RAM>
      <Example>
        <Example_sub>a</Example_sub>
        <Example_sub>b</Example_sub>
        <Example_sub>c</Example_sub>
        <Example_sub>d</Example_sub>
      </Example>
      </Notebook>
      <Notebook Brand="Toshiba" Model="Satellite A660/15J Notebook">
      <Harddisk>640 GB</Harddisk>
      <Processor>Intel Core i5</Processor>
      <RAM>6 GB</RAM>
      <Example>
        <Example_sub>a</Example_sub>
        <Example_sub>b</Example_sub>
        <Example_sub>c</Example_sub>
        <Example_sub>d</Example_sub>
      </Example>
      </Notebook>
      </Product>');
       
      insert into xml_test values (2, '<?xml version="1.0" encoding="UTF-8"?>
      <Product Type=''Laptop''>
      <Notebook Brand="HP" Model="Pavilion dv6-3132TX Notebook">
      <Harddisk>640 GB</Harddisk>
      <Processor>Intel Core i7</Processor>
      <RAM>4 GB</RAM>
      <Example>
        <Example_sub>a</Example_sub>
        <Example_sub>b</Example_sub>
        <Example_sub>c</Example_sub>
        <Example_sub>d</Example_sub>
      </Example>
      </Notebook>
      <Notebook Brand="HP" Model="HP Pavilion dv6-3032TX Notebook">
      <Harddisk>640 GB</Harddisk>
      <Processor>Intel Core i7</Processor>
      <RAM>6 GB</RAM>
      <Example>
        <Example_sub>b</Example_sub>
      </Example>
      </Notebook>
      <Notebook Brand="Toshiba" Model="Satellite A660/07R 3D Notebook">
      <Harddisk>640 GB</Harddisk>
      <Processor>Intel Core i7</Processor>
      <RAM>4 GB</RAM>
      <Example>
        <Example_sub>c</Example_sub>
      </Example>
      </Notebook>
      <Notebook Brand="Toshiba" Model="Satellite A660/15J Notebook">
      <Harddisk>640 GB</Harddisk>
      <Processor>Intel Core i5</Processor>
      <RAM>6 GB</RAM>
      <Example>
        <Example_sub>c</Example_sub>
        <Example_sub>d</Example_sub>
      </Example>
      </Notebook>
      </Product>');
      
      insert into xml_test values (3, '<?xml version="1.0" encoding="UTF-8"?>
      <Product Type=''Laptop''>
      <Notebook Brand="HP" Model="Pavilion dv6-3132TX Notebook">
      <Harddisk>640 GB</Harddisk>
      <Processor>Intel Core i7</Processor>
      <RAM>4 GB</RAM>
      <Example>
        <Example_sub>a</Example_sub>
      </Example>
      </Notebook>
      <Notebook Brand="HP" Model="HP Pavilion dv6-3032TX Notebook">
      <Harddisk>640 GB</Harddisk>
      <Processor>Intel Core i7</Processor>
      <RAM>6 GB</RAM>
      <Example>
        <Example_sub>b</Example_sub>
      </Example>
      </Notebook>
      <Notebook Brand="Toshiba" Model="Satellite A660/07R 3D Notebook">
      <Harddisk>640 GB</Harddisk>
      <Processor>Intel Core i7</Processor>
      <RAM>4 GB</RAM>
      <Example>
        <Example_sub>c</Example_sub>
      </Example>
      </Notebook>
      <Notebook Brand="Toshiba" Model="Satellite A660/15J Notebook">
      <Harddisk>640 GB</Harddisk>
      <Processor>Intel Core i5</Processor>
      <RAM>6 GB</RAM>
      <Example>
        <Example_sub>d</Example_sub>
      </Example>
      </Notebook>
      </Product>');
      
      commit;
      
      SELECT NOTEBOOKS2.*
        FROM xml_test PO,
             XMLTable('//Notebook' PASSING PO.NOTEBOOK
             COLUMNS  row_num for ordinality,
                      "BrandType"    CHAR(10) PATH '@Brand',
                      "ProductModel" CHAR(50) PATH '@Model',
                      "Harddisk" CHAR(10) PATH 'Harddisk',
                      "Processor" CHAR(20) PATH 'Processor',
                      "RAM" CHAR(10) PATH 'RAM') AS NOTEBOOKS2;
      
              ID    ROW_NUM BrandType  ProductModel                                       Harddisk   Processor            RAM       
      ---------- ---------- ---------- -------------------------------------------------- ---------- -------------------- ----------
               1          1 HP         Pavilion dv6-3132TX Notebook                       640 GB     Intel Core i7        4 GB      
               1          2 HP         HP Pavilion dv6-3032TX Notebook                    640 GB     Intel Core i7        6 GB      
               1          3 Toshiba    Satellite A660/07R 3D Notebook                     640 GB     Intel Core i7        4 GB      
               1          4 Toshiba    Satellite A660/15J Notebook                        640 GB     Intel Core i5        6 GB      
               2          1 HP         Pavilion dv6-3132TX Notebook                       640 GB     Intel Core i7        4 GB      
               2          2 HP         HP Pavilion dv6-3032TX Notebook                    640 GB     Intel Core i7        6 GB      
               2          3 Toshiba    Satellite A660/07R 3D Notebook                     640 GB     Intel Core i7        4 GB      
               2          4 Toshiba    Satellite A660/15J Notebook                        640 GB     Intel Core i5        6 GB      
               3          1 HP         Pavilion dv6-3132TX Notebook                       640 GB     Intel Core i7        4 GB      
               3          2 HP         HP Pavilion dv6-3032TX Notebook                    640 GB     Intel Core i7        6 GB      
               3          3 Toshiba    Satellite A660/07R 3D Notebook                     640 GB     Intel Core i7        4 GB      
               3          4 Toshiba    Satellite A660/15J Notebook                        640 GB     Intel Core i5        6 GB      
      However, I want to extract the Example_sub values - but only when there is one Example_sub node in each //Notebook/Example, otherwise I should return null - eg:
              ID    ROW_NUM BrandType  ProductModel                                       Harddisk   Processor            RAM        Example
      ---------- ---------- ---------- -------------------------------------------------- ---------- -------------------- ---------- --------
               1          1 HP         Pavilion dv6-3132TX Notebook                       640 GB     Intel Core i7        4 GB       
               1          2 HP         HP Pavilion dv6-3032TX Notebook                    640 GB     Intel Core i7        6 GB       
               1          3 Toshiba    Satellite A660/07R 3D Notebook                     640 GB     Intel Core i7        4 GB       
               1          4 Toshiba    Satellite A660/15J Notebook                        640 GB     Intel Core i5        6 GB       
               2          1 HP         Pavilion dv6-3132TX Notebook                       640 GB     Intel Core i7        4 GB       
               2          2 HP         HP Pavilion dv6-3032TX Notebook                    640 GB     Intel Core i7        6 GB       b
               2          3 Toshiba    Satellite A660/07R 3D Notebook                     640 GB     Intel Core i7        4 GB       c
               2          4 Toshiba    Satellite A660/15J Notebook                        640 GB     Intel Core i5        6 GB       
               3          1 HP         Pavilion dv6-3132TX Notebook                       640 GB     Intel Core i7        4 GB       a
               3          2 HP         HP Pavilion dv6-3032TX Notebook                    640 GB     Intel Core i7        6 GB       b
               3          3 Toshiba    Satellite A660/07R 3D Notebook                     640 GB     Intel Core i7        4 GB       c
               3          4 Toshiba    Satellite A660/15J Notebook                        640 GB     Intel Core i5        6 GB       d
      I've tried the following:
      SELECT NOTEBOOKS2.*
        FROM xml_test PO,
             XMLTable('//Notebook' PASSING PO.NOTEBOOK
             COLUMNS  row_num for ordinality,
                      "BrandType"    CHAR(10) PATH '@Brand',
                      "ProductModel" CHAR(50) PATH '@Model',
                      "Harddisk" CHAR(10) PATH 'Harddisk',
                      "Processor" CHAR(20) PATH 'Processor',
                      "RAM" CHAR(10) PATH 'RAM',
                      "Example" VARCHAR2(3) PATH 'Example/Example_sub') AS NOTEBOOKS2;
      but that just ends up with the following error (of course): ORA-19279: XQuery dynamic type mismatch: expected singleton sequence - got multi-item sequence

      Any ideas as to how I can go about getting this extracted? I'd like to get it done in one XMLTABLE call if I can, but it's probably not possible - if not, I'm happy to go with another call to XMLTABLE if necessary.

      Thanks.

      Ps. I'm on Oracle 10.2.0.4

      Edited by: Boneist on 12-Mar-2012 18:34

      Edited because the requirements changed.
        • 1. Re: XMLTABLE - help required extracting a single node
          odie_63
          Hi,

          I don' t have your version to test but this one should work :
          SQL> SELECT t.id, x.*
            2  FROM xml_test t
            3     , XMLTable(
            4         '/Product/Notebook'
            5         PASSING t.NOTEBOOK
            6         COLUMNS row_num for ordinality
            7               , "BrandType"    VARCHAR2(10) PATH '@Brand'
            8               , "ProductModel" VARCHAR2(50) PATH '@Model'
            9               , "Harddisk"     VARCHAR2(10) PATH 'Harddisk'
           10               , "Processor"    VARCHAR2(20) PATH 'Processor'
           11               , "RAM"          VARCHAR2(10) PATH 'RAM'
           12               , "Example"      VARCHAR2(3)  PATH 'Example[count(Example_sub)=1]/Example_sub'
           13       ) x
           14  ;
           
                 ID    ROW_NUM BrandType  ProductModel                                       Harddisk   Processor            RAM        Example
          --------- ---------- ---------- -------------------------------------------------- ---------- -------------------- ---------- -------
                  1          1 HP         Pavilion dv6-3132TX Notebook                       640 GB     Intel Core i7        4 GB       
                  1          2 HP         HP Pavilion dv6-3032TX Notebook                    640 GB     Intel Core i7        6 GB       
                  1          3 Toshiba    Satellite A660/07R 3D Notebook                     640 GB     Intel Core i7        4 GB       
                  1          4 Toshiba    Satellite A660/15J Notebook                        640 GB     Intel Core i5        6 GB       
                  2          1 HP         Pavilion dv6-3132TX Notebook                       640 GB     Intel Core i7        4 GB       
                  2          2 HP         HP Pavilion dv6-3032TX Notebook                    640 GB     Intel Core i7        6 GB       b
                  2          3 Toshiba    Satellite A660/07R 3D Notebook                     640 GB     Intel Core i7        4 GB       c
                  2          4 Toshiba    Satellite A660/15J Notebook                        640 GB     Intel Core i5        6 GB       
                  3          1 HP         Pavilion dv6-3132TX Notebook                       640 GB     Intel Core i7        4 GB       a
                  3          2 HP         HP Pavilion dv6-3032TX Notebook                    640 GB     Intel Core i7        6 GB       b
                  3          3 Toshiba    Satellite A660/07R 3D Notebook                     640 GB     Intel Core i7        4 GB       c
                  3          4 Toshiba    Satellite A660/15J Notebook                        640 GB     Intel Core i5        6 GB       d
           
          12 rows selected
           
          If not, try this alternative :
          SELECT t.id, x.*
          FROM xml_test t
             , XMLTable(
                 'for $i in /Product/Notebook
                  return element p {
                    $i/@Brand
                  , $i/@Model
                  , $i/Harddisk
                  , $i/Processor
                  , $i/RAM
                  , $i/Example[count(Example_sub)=1]/Example_sub
                  }' 
                 PASSING t.NOTEBOOK
                 COLUMNS row_num for ordinality
                       , "BrandType"    VARCHAR2(10) PATH '@Brand'
                       , "ProductModel" VARCHAR2(50) PATH '@Model'
                       , "Harddisk"     VARCHAR2(10) PATH 'Harddisk'
                       , "Processor"    VARCHAR2(20) PATH 'Processor'
                       , "RAM"          VARCHAR2(10) PATH 'RAM'
                       , "Example"      VARCHAR2(3)  PATH 'Example_sub'
               ) x
          ;
          Edited by: odie_63 on 12 mars 2012 20:38 - added alternative
          • 2. Re: XMLTABLE - help required extracting a single node
            Boneist
            odie_63 wrote:
            Hi,

            I don' t have your version to test but this one should work :
            SQL> SELECT t.id, x.*
            2  FROM xml_test t
            3     , XMLTable(
            4         '/Product/Notebook'
            5         PASSING t.NOTEBOOK
            6         COLUMNS row_num for ordinality
            7               , "BrandType"    VARCHAR2(10) PATH '@Brand'
            8               , "ProductModel" VARCHAR2(50) PATH '@Model'
            9               , "Harddisk"     VARCHAR2(10) PATH 'Harddisk'
            10               , "Processor"    VARCHAR2(20) PATH 'Processor'
            11               , "RAM"          VARCHAR2(10) PATH 'RAM'
            12               , "Example"      VARCHAR2(3)  PATH 'Example[count(Example_sub)=1]/Example_sub'
            13       ) x
            14  ;
            
            ID    ROW_NUM BrandType  ProductModel                                       Harddisk   Processor            RAM        Example
            --------- ---------- ---------- -------------------------------------------------- ---------- -------------------- ---------- -------
            1          1 HP         Pavilion dv6-3132TX Notebook                       640 GB     Intel Core i7        4 GB       
            1          2 HP         HP Pavilion dv6-3032TX Notebook                    640 GB     Intel Core i7        6 GB       
            1          3 Toshiba    Satellite A660/07R 3D Notebook                     640 GB     Intel Core i7        4 GB       
            1          4 Toshiba    Satellite A660/15J Notebook                        640 GB     Intel Core i5        6 GB       
            2          1 HP         Pavilion dv6-3132TX Notebook                       640 GB     Intel Core i7        4 GB       
            2          2 HP         HP Pavilion dv6-3032TX Notebook                    640 GB     Intel Core i7        6 GB       b
            2          3 Toshiba    Satellite A660/07R 3D Notebook                     640 GB     Intel Core i7        4 GB       c
            2          4 Toshiba    Satellite A660/15J Notebook                        640 GB     Intel Core i5        6 GB       
            3          1 HP         Pavilion dv6-3132TX Notebook                       640 GB     Intel Core i7        4 GB       a
            3          2 HP         HP Pavilion dv6-3032TX Notebook                    640 GB     Intel Core i7        6 GB       b
            3          3 Toshiba    Satellite A660/07R 3D Notebook                     640 GB     Intel Core i7        4 GB       c
            3          4 Toshiba    Satellite A660/15J Notebook                        640 GB     Intel Core i5        6 GB       d
            
            12 rows selected
            Oh that's awesome! It works very nicely - thank you SO much; you've really saved my bacon with that! And such a neat solution, too - so much for me doubting that XMLTABLE could handle it!