This discussion is archived
7 Replies Latest reply: Jul 16, 2013 3:37 AM by Boneist RSS

XMLTable to extract columns with conditional paths not working

Boneist Guru
Currently Being Moderated

Hi,

 

I'm on 11.2.0.3 and I need to extract columns out of a section of XML with conditions on, but I get "ORA-00907: missing right parenthesis", so I'm guessing that the way I'm going about it is not supported or I've somehow got the wrong syntax or something.

 

Here's a highly simplified version of what I'm trying to do (there could be many different entries in the <sets1> and <sets2> sections, and they could be in different orders, so it's not like I can always choose the first or last):

 

with sample_data as (select 1 id,
                            xmltype('<root>
                                       <sets1>
                                         <set1><set_name>name1</set_name><set_val>val1</set_val></set1>
                                         <set1><set_name>name2</set_name><set_val>val2</set_val></set1>
                                       </sets1>
                                       <sets2>
                                         <set2><set_name>name3</set_name><set_val>val3</set_val></set2>
                                         <set2><set_name>name4</set_name><set_val>val4</set_val></set2>
                                       </sets2>
                                     </root>') xml_doc from dual)
select *
from   sample_data sd,
       xmltable('<root>' passing sd.xml_doc
                columns col1 varchar2(10) path 'sets1/set1[set_name=''name1'']/set_val',
                        col2 varchar2(10) path 'sets1/set1[set_name=''name2'']/set_val',
                        col3 varchar2(10) path 'sets2/set2[set_name=''name4'']/set_val') x;

 

What I'm trying to get:

 

COL1  COL2  COL3
----  ----  ----
val1  val2  val4

 

I know that I could split out the <set1> and <set2> nodes into their own xmltypes, do xmltable on that and then join them and do the necessary pivoting etc, but when I try running that against just 1000 rows, the db did not like it at all and terminated my session! Hence, I'm looking for a more elegant way of doing it.

 

Is there a way of doing this within one XMLTABLE? Any pointers will be very gratefully received!

  • 1. Re: XMLTable to extract columns with conditional paths not working
    Jason_(A_Non) Expert
    Currently Being Moderated

    This fixes your sample query issue and gets you the output you show.

     with sample_data as (select 1 id,  
                                 xmltype('<root>  
                                            <sets1>  
                                              <set1><set_name>name1</set_name><set_val>val1</set_val></set1>  
                                              <set1><set_name>name2</set_name><set_val>val2</set_val></set1>  
                                            </sets1>  
                                            <sets2>  
                                              <set2><set_name>name3</set_name><set_val>val3</set_val></set2>  
                                              <set2><set_name>name4</set_name><set_val>val4</set_val></set2>  
                                            </sets2>  
                                          </root>') xml_doc from dual)  
     select x.*  -- change
     from   sample_data sd,  
            xmltable('/root' passing sd.xml_doc  -- change
                     columns col1 varchar2(10) path 'sets1/set1[set_name="name1"]/set_val',  -- change
                             col2 varchar2(10) path 'sets1/set1[set_name="name2"]/set_val',  -- change
                             col3 varchar2(10) path 'sets2/set2[set_name="name4"]/set_val') x;  -- change

    As you can see, I marked the rows I changed.  The error you saw came from using two single quotes in the COLUMN XPath instead of using a double quote.  Then I changed the XPath in the XMLTable to remove the brackets as well.

     

    I'm confused on what columns you really want from the larger set, in terms of how you know which values you want, or did you mean you want something at random from sets1 and sets2.

     

    You are storing the XML in a SECUREFILE BINARY XMLType column, right?  It seems you do from previous visits, but just double checking.

  • 2. Re: XMLTable to extract columns with conditional paths not working
    Boneist Guru
    Currently Being Moderated

    Hi Jason,

     

    Thanks for your reply - changing the single quotes into double quotes worked, thanks! And d'oh on the '<root>' *blushes* That wasn't the case in my actual example, I promise! I completely missed that!

     

    I'm afraid we store our XML as CLOBs (sorry Marco!), mainly because our version of GoldenGate doesn't support securefile binary XMLType *{:-(

     

    As for the data - I'm effectively after specific key-values pairs, but the keys can appear in any order within the node. Also, it turns out that the same key can appear multiple times *big sigh*. So now I need to work out how the heck to pick the latest key-value in the node. Here's the example I'm working with now:

     

    with sample_data as (select 1 id,
                                xmltype('<root>
                                           <sets1>
                                             <set1><set_name>name2</set_name><set_val>val2</set_val></set1>
                                             <set1><set_name>name5</set_name><set_val>val5</set_val></set1>
                                             <set1><set_name>name1</set_name><set_val>val1</set_val></set1>
                                             <set1><set_name>name2</set_name><set_val>val2b</set_val></set1>
                                             <set1><set_name>name6</set_name><set_val>val6</set_val></set1>
                                           </sets1>
                                           <sets2>
                                             <set2><set_name>name3</set_name><set_val>val3</set_val></set2>
                                             <set2><set_name>name4</set_name><set_val>val4</set_val></set2>
                                           </sets2>
                                         </root>') xml_doc from dual)
    select *
    from   sample_data sd,
           xmltable('/root' passing sd.xml_doc
                    columns col1 varchar2(10) path 'sets1/set1[set_name="name1"]/set_val',
                            col2 varchar2(10) path 'sets1/set1[set_name="name2"]/set_val',
                            col3 varchar2(10) path 'sets2/set2[set_name="name3"]/set_val') x;

     

    What I expect to see:

     

    COL1   COL2  COL3
    ----  -----  ----
    val1  val2b  val3

     

    What I actually get: "ORA-19279: XPTY0004 - XQuery dynamic type mismatch: expected singleton sequence - got multi-item sequence".

     

    Anyone got any ideas as to how I can pick the last set of elements that I'm interested in, or am I reduced to having to go back to separate xmltable statements again? I'd like to avoid the latter if at all possible.

    (I've tried googling for examples, but my Google-fu must be weak, because I can't find anything relevant!)

  • 3. Re: XMLTable to extract columns with conditional paths not working
    odie_63 Guru
    Currently Being Moderated

    Assuming you mean "latest" = "last in document order", you can do :


    SQL> with sample_data as (

      2    select 1 id,

      3           xmltype('<root>

      4                      <sets1>

      5                        <set1><set_name>name2</set_name><set_val>val2</set_val></set1>

      6                        <set1><set_name>name5</set_name><set_val>val5</set_val></set1>

      7                        <set1><set_name>name1</set_name><set_val>val1</set_val></set1>

      8                        <set1><set_name>name2</set_name><set_val>val2b</set_val></set1>

      9                        <set1><set_name>name6</set_name><set_val>val6</set_val></set1>

    10                      </sets1>

    11                      <sets2>

    12                        <set2><set_name>name3</set_name><set_val>val3</set_val></set2>

    13                        <set2><set_name>name4</set_name><set_val>val4</set_val></set2>

    14                      </sets2>

    15                    </root>') xml_doc

    16    from dual

    17  )

    18  select x.*

    19  from sample_data sd,

    20       xmltable('/root'

    21         passing sd.xml_doc

    22         columns col1 varchar2(10) path 'sets1/set1[set_name="name1"][last()]/set_val',

    23                 col2 varchar2(10) path 'sets1/set1[set_name="name2"][last()]/set_val',

    24                 col3 varchar2(10) path 'sets2/set2[set_name="name3"][last()]/set_val') x

    25  ;

     

    COL1       COL2       COL3

    ---------- ---------- ----------

    val1       val2b      val3

     

  • 4. Re: XMLTable to extract columns with conditional paths not working
    Boneist Guru
    Currently Being Moderated

    Aha, this appears to work:

     

    with sample_data as (select 1 id,
                                xmltype('<root>
                                           <sets1>
                                             <set1><set_name>name2</set_name><set_val>val2</set_val></set1>
                                             <set1><set_name>name5</set_name><set_val>val5</set_val></set1>
                                             <set1><set_name>name1</set_name><set_val>val1</set_val></set1>
                                             <set1><set_name>name2</set_name><set_val>val2b</set_val></set1>
                                             <set1><set_name>name6</set_name><set_val>val6</set_val></set1>
                                             <set1><set_name>name1</set_name><set_val>val1b</set_val></set1>
                                           </sets1>
                                           <sets2>
                                             <set2><set_name>name3</set_name><set_val>val3</set_val></set2>
                                             <set2><set_name>name4</set_name><set_val>val4</set_val></set2>
                                           </sets2>
                                         </root>') xml_doc from dual)
    select col1, col2, col3
    from   sample_data sd,
           xmltable('/root' passing sd.xml_doc
                    columns col1 varchar2(10) path 'sets1/set1[set_name="name1"][last()]/set_val',
                            col2 varchar2(10) path 'sets1/set1[set_name="name2"][last()]/set_val',
                            col3 varchar2(10) path 'sets2/set2[set_name="name3"][last()]/set_val') x;
    

     

    resulting in:

     

     

    COL1       COL2       COL3     
    ---------- ---------- ----------
    val1b      val2b      val3
    

     

    Message was edited by: Boneist Oh, haha! Odie got there before me, but good to know I managed to come up with the right solution all by myself*! *Ok, I had some help from Google, eventually! *{;-)

  • 5. Re: XMLTable to extract columns with conditional paths not working
    Boneist Guru
    Currently Being Moderated

    Ok, final addendum to this question:

     

    For the <sets2> bit, instead of picking the latest element in the document that matches the condition, I've got to pick all the elements and combine them together into one string.

     

    I currently do this via:

     

    with sample_data as (select 1 id,
                                xmltype('<root>
                                           <sets1>
                                             <set1><set_name>name2</set_name><set_val>val2</set_val></set1>
                                             <set1><set_name>name5</set_name><set_val>val5</set_val></set1>
                                             <set1><set_name>name1</set_name><set_val>val1</set_val></set1>
                                             <set1><set_name>name2</set_name><set_val>val2b</set_val></set1>
                                             <set1><set_name>name6</set_name><set_val>val6</set_val></set1>
                                             <set1><set_name>name1</set_name><set_val>val1b</set_val></set1>
                                           </sets1>
                                           <sets2>
                                             <set2><set_name>name3</set_name><set_val>val3a</set_val></set2>
                                             <set2><set_name>name4</set_name><set_val>val4</set_val></set2>
                                             <set2><set_name>name3</set_name><set_val>val3b</set_val></set2>
                                           </sets2>
                                         </root>') xml_doc from dual)
    select XMLQUERY('//sets2/set2[set_name="name3"]/set_val/text()'
                          PASSING sd.xml_doc
                          RETURNING CONTENT).getstringval() col3
    FROM   sample_data sd;  

     

    producing:

     

    COL3                                                                            
    ----------
    val3aval3b  

     

    Is there a way to do this via XMLTABLE or am I stuck with having to use the XMLQUERY in the select clause? The following doesn't work:

     

    with sample_data as (select 1 id,
                                xmltype('<root>
                                           <sets1>
                                             <set1><set_name>name2</set_name><set_val>val2</set_val></set1>
                                             <set1><set_name>name5</set_name><set_val>val5</set_val></set1>
                                             <set1><set_name>name1</set_name><set_val>val1</set_val></set1>
                                             <set1><set_name>name2</set_name><set_val>val2b</set_val></set1>
                                             <set1><set_name>name6</set_name><set_val>val6</set_val></set1>
                                             <set1><set_name>name1</set_name><set_val>val1b</set_val></set1>
                                           </sets1>
                                           <sets2>
                                             <set2><set_name>name3</set_name><set_val>val3a</set_val></set2>
                                             <set2><set_name>name4</set_name><set_val>val4</set_val></set2>
                                             <set2><set_name>name3</set_name><set_val>val3b</set_val></set2>
                                           </sets2>
                                         </root>') xml_doc from dual)
    select col1, col2, col3
    from   sample_data sd,
           xmltable('/root' passing sd.xml_doc
                    columns col1 varchar2(10) path 'sets1/set1[set_name="name1"][last()]/set_val',
                            col2 varchar2(10) path 'sets1/set1[set_name="name2"][last()]/set_val',
                            col3 varchar2(10) path '//sets2/set2[set_name="name3"]/set_val/text()') x;
  • 6. Re: XMLTable to extract columns with conditional paths not working
    odie_63 Guru
    Currently Being Moderated

    Is there a way to do this via XMLTABLE or am I stuck with having to use the XMLQUERY in the select clause?

     

    Sure, you can use string-join() function with an empty separator :

    SQL> with sample_data as (select 1 id,

      2                         xmltype('<root>

      3                                    <sets1>

      4                                      <set1><set_name>name2</set_name><set_val>val2</set_val></set1>

      5                                      <set1><set_name>name5</set_name><set_val>val5</set_val></set1>

      6                                      <set1><set_name>name1</set_name><set_val>val1</set_val></set1>

      7                                      <set1><set_name>name2</set_name><set_val>val2b</set_val></set1>

      8                                      <set1><set_name>name6</set_name><set_val>val6</set_val></set1>

      9                                      <set1><set_name>name1</set_name><set_val>val1b</set_val></set1>

    10                                    </sets1>

    11                                    <sets2>

    12                                      <set2><set_name>name3</set_name><set_val>val3a</set_val></set2>

    13                                      <set2><set_name>name4</set_name><set_val>val4</set_val></set2>

    14                                      <set2><set_name>name3</set_name><set_val>val3b</set_val></set2>

    15                                    </sets2>

    16                                  </root>') xml_doc from dual )

    17  select col1, col2, col3

    18  from sample_data sd,

    19       xmltable('/root' passing sd.xml_doc

    20         columns col1 varchar2(10) path 'sets1/set1[set_name="name1"][last()]/set_val',

    21                 col2 varchar2(10) path 'sets1/set1[set_name="name2"][last()]/set_val',

    22                 col3 varchar2(10) path 'string-join(sets2/set2[set_name="name3"]/set_val, "")'

    23       ) x ;

     

    COL1       COL2       COL3

    ---------- ---------- ----------

    val1b      val2b      val3aval3b

     

     

    Another option would be :

    select col1, col2, xmlcast(col3 as varchar2(10)) as col3

    from sample_data sd, 

         xmltable('/root' passing sd.xml_doc 

           columns col1 varchar2(10) path 'sets1/set1[set_name="name1"][last()]/set_val', 

                   col2 varchar2(10) path 'sets1/set1[set_name="name2"][last()]/set_val', 

                   col3 xmltype      path 'sets2/set2[set_name="name3"]/set_val/text()'

         ) x ;

     

    Message was edited by: odie_63

  • 7. Re: XMLTable to extract columns with conditional paths not working
    Boneist Guru
    Currently Being Moderated

    Awesome!

     

    Thanks very much, Odie and Jason - I always learn such a lot from you guys whenever I have an XML problem to solve! *{:-D

Legend

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