9 Replies Latest reply: May 3, 2012 9:05 AM by odie_63 RSS

    mixed values: xml values + dynamic values

    926353
      Hello experts,

      I have a question regarding an existing topic:

      xml parsing inserting values

      It was already marked as answered, but I have an additional question that is posted at the bottom of that topic.

      situation:
      SQL> create table zipcodes (
        2   state_abbreviation  varchar2(2),
        3   zipcode             varchar2(5),
        4   zip_code_extn       varchar2(10),
        5   city                varchar2(80),
        6   num            number(5)
        7  );
       
      Table created
       
      SQL> CREATE OR REPLACE PROCEDURE insertZipcodes (
        2    p_directory IN VARCHAR2
        3  , p_filename  IN VARCHAR2
        4  )
        5  IS       l_num number(5);
        6  BEGIN
        7  l_num := 55;
        8    INSERT INTO zipcodes (state_abbreviation, zipcode, zip_code_extn, city, num)
        9    SELECT state_abbreviation
       10         , zipcode
       11         , zip_code_extn
       12         , city  ,num            
       13    FROM XMLTable('/Zipcodes/mappings'
       14           passing xmltype(bfilename(p_directory, p_filename), nls_charset_id('AL32UTF8'))
       15           columns state_abbreviation varchar2(2)  path 'STATE_ABBREVIATION'
       16                 , zipcode            varchar2(5)  path 'ZIPCODE'
       17                 , zip_code_extn      varchar2(10) path 'ZIP_CODE_EXTN'
       18                 , city               varchar2(80) path 'CITY'
       19                   , num               number(5) 'l_num'
       20         )
       21    ;
       22  
       23  END;
      my question:

      anyone found a solution for a dynamic value?

      For example:
      num               number(5) 'l_num'
      where l_num should be the return value of a function with a xml row value as in parameter
       num               number(5) 'l_num' ==> l_num :=get_modified_zipcode(zipcode)
      thanks for your help
        • 1. Re: mixed values: xml values + dynamic values
          odie_63
          Hi,

          Do you understand my reply in the thread you're quoting?

          Just call the function in the SELECT clause, that's plain old SQL, nothing fancy about it.
          INSERT INTO zipcodes (state_abbreviation, zipcode, zip_code_extn, city, num)
          SELECT state_abbreviation
               , zipcode
               , zip_code_extn
               , city  
               , get_modified_zipcode(zipcode) as num            
          FROM XMLTable('/Zipcodes/mappings'
                 passing xmltype(bfilename(p_directory, p_filename), nls_charset_id('AL32UTF8'))
                 columns state_abbreviation varchar2(2)  path 'STATE_ABBREVIATION'
                       , zipcode            varchar2(5)  path 'ZIPCODE'
                       , zip_code_extn      varchar2(10) path 'ZIP_CODE_EXTN'
                       , city               varchar2(80) path 'CITY'
               )
          ;
          • 2. Re: mixed values: xml values + dynamic values
            926353
            thanks for the reply, I don't know if you know a little about ODI? as your name suggests odi expert ;-)

            But basically I made a solution in which I map an xml to an oracle table. I was wondering if I could do it with pure pl/sql in which I almost have no experience.

            greets

            ps: sorry about double posting, at first after my reply in the other thread, the thread didn't get bumped. But now I see it is bumped to the top, my apologies.

            Edited by: iadgroe on Apr 30, 2012 5:56 AM
            • 3. Re: mixed values: xml values + dynamic values
              Jason_(A_Non)
              You can parse XML in PL/SQL as well. Here is something I wrote up a while back on one simple scenario.
              http://anononxml.blogspot.com/2010/05/one-question-that-comes-up-with-some.html

              As you have seen as well, you can parse XML purely in SQL as well. As for which method is faster, it is usually the SQL route, but that mainly depends upon your DB version and the size of the XML. It also depends upon what type of transformations/business logic that may need to happen between the XML and your DB as well.
              • 4. Re: mixed values: xml values + dynamic values
                926353
                A non thanks, I have another question:

                In your example imagine you would have this structure:
                <employees>
                    <emp>
                        <name>Scott</name>
                        <favorites>
                            <choice>
                                <month>January</month>
                                <colors>
                                    <color>red</color>
                                    <color>orange</color>
                                </colors>
                            </choice>
                            <choice>
                                <month>February</month>
                                <colors>
                                    <color>Pink</color>
                                    <color>Green</color>
                                </colors>
                            </choice>
                        </favorites>
                    </emp>
                
                    <emp>
                     ......
                    </emp>
                </employees>
                How would you get this output:
                *emp*         *month*         *color*
                Scott          January        Orange
                Scott          January        Red
                Scott          February       Pink
                Scott          February       Green
                I worked out 2 situation quite similar with xpath but I end up getting a cartesian product or a singleton error.
                The problem is the possibility of more colors per month...

                Do you have a suggestion?
                Thanks a lot

                Greets
                Yves
                • 5. Re: mixed values: xml values + dynamic values
                  odie_63
                  Method 5.b :

                  http://anononxml.blogspot.fr/2010/07/basic-xml-parsing-via-xmltable.html
                  • 6. Re: mixed values: xml values + dynamic values
                    926353
                    Hi thanks for the reply, however when I follow that approach I get too many rows. This is regarding timesheets.
                    I get following structure in my xml file:
                    <employees>
                        <employee>
                            <name>
                                <userName>HUYBR</userName>
                            </name>
                            <timeRegistration>
                                <days>
                                    <day>
                                        <date>16/03/2012</date>
                                        <absences>
                                            <result>
                                                <code>9790</code>
                                                <time>7</time>
                                            </result>
                                        </absences>
                                    </day>
                                    <day>
                                        <date>09/03/2012</date>
                                        <absences>
                                            <result>
                                                <code>1260</code>
                                                <time>8</time>
                                            </result>
                                        </absences>
                                    </day>
                                </days>
                            </timeRegistration>
                        </employee>
                        <employee>
                            <name>
                                <userName>PERME</userName>
                            </name>
                            <timeRegistration>
                                <days>
                                    <day>
                                        <date>06/03/2012</date>
                                        <absences>
                                            <result>
                                                <code>1290</code>
                                                <time>3,80</time>
                                            </result>
                                        </absences>
                                    </day>
                                </days>
                            </timeRegistration>
                        </employee>
                    </employees>
                    So normally it should give 3 results.
                    I use the following xml parsing method:
                    SELECT k.code
                         ,h.userName
                         , i.timesheet_datum
                         , k.uren
                         
                    FROM XMLTable('/employees/employee'
                           passing xmltype(bfilename('MY_DIR', 'webworx.xml'), nls_charset_id('AL32UTF16'))
                           columns 
                                  userName varchar2(20)  path './name/userName',
                                  timereg           xmltype  path './timeRegistration/days/day'
                         ) h,
                               XMLTABLE('day/date' passing h.timereg
                                  columns 
                                        timesheet_datum varchar2(20) path '.'
                                  ) i,
                          XMLTABLE('day/absences/result' passing h.timereg
                                  columns 
                                        code  number path './code',
                                        uren  number path './time'
                                  ) k
                    This structure gives me the following results (cartesian product):

                    [http://i.imgur.com/slce7.png]

                    I also tried this one:
                    SELECT k.code
                         ,h.userName
                         , i.timesheet_datum
                         , k.uren
                         
                    FROM XMLTable('/employees/employee'
                           passing xmltype(bfilename('MY_DIR', 'webworx.xml'), nls_charset_id('AL32UTF16'))
                           columns 
                                    userName varchar2(20)  path './name/userName'
                                 , timereg           xmltype  path './timeRegistration/days'
                         ) h,
                          XMLTABLE('days/day' passing h.timereg
                                  columns 
                                        timesheet_datum varchar2(20) path './date',
                                        code  number path './absences/result/code',
                                        uren  number path './absences/result/time'
                                  ) k
                    ;
                    But this one won't work when 2 absences occur (singleton error), how can I determine that the code en hours belong to a certain date?

                    Am I overlooking something? Thanks for your help!
                    • 7. Re: mixed values: xml values + dynamic values
                      odie_63
                      But this one won't work when 2 absences occur (singleton error)
                      Does that mean you may have 2 (or +) absences for the same day?
                                      <day>
                                          <date>16/03/2012</date>
                                          <absences>
                                              <result>
                                                  <code>9790</code>
                                                  <time>7</time>
                                              </result>
                                          </absences>
                                          <absences>
                                              <result>
                                                  <code>9791</code>
                                                  <time>1</time>
                                              </result>
                                          </absences>
                                      </day>
                      • 8. Re: mixed values: xml values + dynamic values
                        926353
                        Hi odie yes indeed for example half a day AM half a day PM.

                        I tweaked the last part to this:
                        XMLTABLE('days/day' passing h.timereg
                                      columns 
                                            timesheet_datum varchar2(20) path './date',
                                            resultaat xmltype path './absences/result'
                                      ) k,
                                XMLTABLE('result' passing k.resultaat
                                      columns 
                                            code number path './code',
                                            uren number path './time'
                                      ) l
                        So I didn't split the last part into a new xmltype node while it should have. Now is seems to work ... :-)
                        So in fact you can keep on splitting and splitting. Sorry but I was staring myself blind here for a while.
                        • 9. Re: mixed values: xml values + dynamic values
                          odie_63
                          So I didn't split the last part into a new xmltype node while it should have. Now is seems to work ... :-)
                          So in fact you can keep on splitting and splitting.
                          Exactly.


                          BTW, you don't need to specify the root here :
                          './absences/result'
                          In the PATH clause, the XPath/XQuery expression is relative to the context item passed from the main XQuery.
                          So this is sufficient :
                          'absences/result'
                          I personally find it easier to read and to write, especially when I have to deal with large XMLTable statements.