This discussion is archived
2 Replies Latest reply: Mar 13, 2012 1:22 AM by Boneist RSS

XMLTABLE - help required extracting a single node

Boneist Guru
Currently Being Moderated
(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 Guru
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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!

Legend

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