This discussion is archived
9 Replies Latest reply: May 3, 2012 7:05 AM by odie_63 RSS

mixed values: xml values + dynamic values

926353 Newbie
Currently Being Moderated
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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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) Expert
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    Method 5.b :

    http://anononxml.blogspot.fr/2010/07/basic-xml-parsing-via-xmltable.html
  • 6. Re: mixed values: xml values + dynamic values
    926353 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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.

Legend

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