6 Replies Latest reply: Sep 5, 2013 11:31 AM by garywicke RSS

    How to output repeating/optional values from XML in CLOB column?

    garywicke

      Environment:


      Oracle 11.2.0.3 EE on Solaris


      Very much an XML newbie but trying.  :-)


      I have a table with XML stored in a CLOB (no, I didn't design the environment but I have to live here).


      I need to output the data in a flat file format.  Some of the data is both optional and potentially repeating if present.


      Here is a mocked up sample of the data:


      "<res:Customers xmlns:res="http://www.whatever.com/response">

           <res:lastName>SMITH</res:lastName>

           <res:firstName>JENNIFER</res:firstName>

           <res:Extras>

                <res:Extra>

                     <res:phoneType>HOME</res:phoneType>

                     <res:phoneNumber>555-555-5555</res:phoneNumber>

                </res:Extra>

                <res:Extra>

                     <res:phoneType>MOBILE</res:phoneType>

                     <res:phoneNumber>666-666-6666</res:phoneNumber>

                </res:Extra>

           </res:Extras>

           <res:lastName>JONES</res:firstName>

           <res:firstName>ROBERT</res:firstName>

           <res:Extras>

                <res:Extra>

                     <res:phoneType>HOME</res:/phoneType>

                     <res:phoneNumber>123-456-7890</res:phoneNumber>

                </res:Extra>

            </res:Extras>

            <res:lastName>MURPHY</res:lastName>

            <res:firstName>SEAN</res:firstName>

      </res:Customers>

       

      I am trying to get my output to look like this:


      Last Name     First Name     Type     Number

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

      SMITH         JENNIFER       HOME     555-555-5555

      SMITH         JENNIFER       MOBILE   666-666-6666

      JONES         ROBERT         HOME     123-456-7890

      MURPHY        SEAN


      I don't know how to get the repeated rows printed nor do I know how to get the row printed that doesn't contain the optional data.


      Here is the script that I've developed so far.  With my real data I can get the first and last names printed out and I can get the phone information printed out but only by excluding the names. I've been unable to get the data if the optional data is not present.


      I tried to reference down to the phone information and then back up 2 levels to get the name information but I keep getting: ORA-19110: unsupported XQuery expression


      select x.*

      from CUSTOMER m,

           XMLTable(XMLNamespaces('http://www.whatever.com/response' as "res")

                   , '/res:Customers/res:Extras/res:Extra'

                    passing xmltype(m.CUSTOMER_XML)

                    columns

                       lastName     varchar2(80) PATH '../../res:lastName'

                      ,firstName    varchar2(30) PATH '../../firstName'

                      ,phoneType    varchar2(30) PATH '.'

                      ,phoneNumber  varchar2(30) PATH '.'

                   ) x

       

      Any assistance is greatly appreciated.


      -gary

        • 1. Re: How to output repeating/optional values from XML in CLOB column?
          garywicke

          OK, I was able to get closer by using two (2) XMLTable sources like this:

           

          select x.*, y.*

          from CUSTOMER m,

               XMLTable(XMLNamespaces('http://www.whatever.com/response' as "res")

                       , '/res:Customers'

                        passing xmltype(m.CUSTOMER_XML)

                        columns

                           lastName     varchar2(80) PATH 'res:lastName'

                          ,firstName    varchar2(30) PATH 'res:firstName'

                       ) x,

              

          XMLTable(XMLNamespaces('http://www.whatever.com/response' as "res")

                       , '/res:Customers/res:Extras/res:Extra'

                        passing xmltype(m.CUSTOMER_XML)

                        columns

                           phoneType    varchar2(30) PATH '.'

                          ,phoneNumber  varchar2(30) PATH '.'

                       ) y;


          This gives me (I believe) the data when there is a match between the phone data and the name data.  I repeat, I believe that's what happening looking at my actual results.  It looks like it should be a Cartesian product but my results only show repeated names for as many phone records as there are for that name.  This is good because that's almost what I need.


          How do I get the name data for those people that do not have phone information?


          Thanks very much!!


          -gary

          • 2. Re: How to output repeating/optional values from XML in CLOB column?
            odie_63

            Hi Gary,

             

            Are you sure the sample XML match your real data ?

            The second query should errors out because the PATH expression references multiple targets.

             

            If it is really like that, the XML structure is not very easy to deal with. It would make much sense to have a container element enclosing each Customer information, like this :

            <res:Customers xmlns:res="http://www.whatever.com/response">

              <res:Customer>

                 <res:lastName>SMITH</res:lastName>

                 <res:firstName>JENNIFER</res:firstName>

                 <res:Extras>

                      <res:Extra>

                           <res:phoneType>HOME</res:phoneType>

                           <res:phoneNumber>555-555-5555</res:phoneNumber>

                      </res:Extra>

                      <res:Extra>

                           <res:phoneType>MOBILE</res:phoneType>

                           <res:phoneNumber>666-666-6666</res:phoneNumber>

                      </res:Extra>

                 </res:Extras>

              </res:Customer>

              <res:Customer>

                ...

              </res:Customer>

            </res:Customers>

             

            To answer your main question, you can use an OUTER JOIN to include data that don't have Extras.

            Using your original sample XML, something like this works for me :

            SQL> select x.lastName

              2       , x.firstName

              3       , y.phoneType

              4       , y.phoneNumber

              5  from customer m

              6     , XMLTable(

              7         XMLNamespaces(default 'http://www.whatever.com/response')

              8       , 'for $i in /Customers/lastName

              9          return element r {

            10            $i/following-sibling::Extras[1]

            11          , $i/following-sibling::firstName[1]

            12          , $i

            13          }'

            14         passing xmltype(m.CUSTOMER_XML)

            15         columns

            16           lastName   varchar2(80) PATH 'lastName'

            17         , firstName  varchar2(30) PATH 'firstName'

            18         , extras     xmltype      PATH 'Extras'

            19       ) x

            20     , XMLTable(

            21         XMLNamespaces(default 'http://www.whatever.com/response')

            22       , '/Extras/Extra'

            23         passing x.extras

            24         columns

            25           phoneType    varchar2(30) PATH 'phoneType'

            26         , phoneNumber  varchar2(30) PATH 'phoneNumber'

            27       ) (+) y

            28  ;

             

            LASTNAME     FIRSTNAME   PHONETYPE   PHONENUMBER

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

            SMITH        JENNIFER    HOME        555-555-5555

            SMITH        JENNIFER    MOBILE      666-666-6666

            JONES        ROBERT      HOME        123-456-7890

            MURPHY       SEAN                   

             

            • 3. Re: How to output repeating/optional values from XML in CLOB column?
              garywicke

              Hi Odie

               

              Thanks very much for  your help.  No, this does not exactly match my 'real' data but I can't display the actual data due to its sensitive nature.

               

              I understand what you mean about the container and the actual data reflects that correct structure.

               

              I had tried using the OUTER JOIN but had the '(+)' in the wrong place!  doh!

               

              I appreciate you taking the time to answer what are probably very simple questions from us newbies but we learn a great deal from you.  We all have to start somewhere and appreciate the hand-holding in these early stages.

               

              THANK YOU!

               

              -gary

              • 4. Re: How to output repeating/optional values from XML in CLOB column?
                garywicke

                Hi Odie


                Well of course it turns out I don't want a Cartesian Product after all.  doh!

                 

                How do I specify the JOIN condition to only match the rows from the 2nd data source to their corresponding rows from the 1st?


                I had originally tried to reference all data from just one XMLTable call but I couldn't get the syntax right.  It looks like I may need to go back and figure that one out.


                I tried to reference the different levels of the XML structure by using the '../../' directory notation in the COLUMN section but got the error I referenced above.


                It looks like I need a bit more help with the syntax as I continue to research.


                I appreciate you sharing your expertise!!


                -gary

                • 5. Re: How to output repeating/optional values from XML in CLOB column?
                  Jason_(A_Non)

                  In 11.2.x.x and earlier, you cannot use ../ style notation to see outside the location specified by the XPath statement in the XMLTable command.  Apparently starting in 12.1 you can now, which will be a nice feature.

                   

                  In Odie's example, did you notice that in the first XMLTable, one of the COLUMNS he is extracting is an XMLType and in the second XMLTable, he is referencing that column via the PASSING clause?  That is how the join between tables x and y was accomplished.  That is what your earlier SQL was missing but I'm just guessing on whether it is still missing now.

                   

                  You may also need a more XQuery approach using something like Odie showed in his first XMLTable, but I'm just guessing at this point based on the structure you have to work with.

                  • 6. Re: How to output repeating/optional values from XML in CLOB column?
                    garywicke

                    Hi Jason


                    Thanks very much for your response.


                    If I would have just read Odie's response more carefully I would have seen the answer.  Thank you for pointing out my carelessness!


                    I see how the two sections are connected and have actually got it working in my 'real' environment.


                    I saw the '../' notation in an IBM document and thought it was a standard, I guess it is but not until 12c.


                    Let me say again how much I personally, and I'm sure other newbies, appreciate the time and effort guys like you and Odie and the others spend on these sites to help those of us just beginning with XML or in my case are thrown into it by a customer request.


                    Thanks and have a great day!


                    -gary