7 Replies Latest reply: Jul 16, 2013 5:37 AM by Boneist RSS

    XMLTable to extract columns with conditional paths not working

    Boneist

      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)

          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

            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

              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

                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

                  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

                    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

                      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