11 Replies Latest reply: Dec 10, 2012 5:53 AM by Rahul_India RSS

    XML issue

    Rahul_India
      I have following XML file
      <?xml version="1.0" encoding="UTF-8"?>
      <marketing-expenses xsi:noNamespaceSchemaLocation="C:/Users/r.rahul/Documents/Altova/MapForce2013/MapForceExamples/MarketingExpenses.xsd" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
           <Person>
                <FullName>Fred Landis</FullName>
                <Title>Project Manager</Title>
                <Phone>123-456-7890</Phone>
                <Email>f.landis@nanonull.com</Email>
           </Person>
           <expense-item>
                <type>Meal</type>
                <Date>2003-01-01</Date>
                <expense>122.11</expense>
           </expense-item>
           <expense-item>
                <type>Lodging</type>
                <Date>2003-01-02</Date>
                <expense>122.12</expense>
           </expense-item>
      I want to insert this data into a 2 tables.
      1.expenseitem- column names (rawtype,rawdata,rawexpense)
      2.PersonDetail-(fullname,title,phone,email)

      how to load the data in their corresponding fields.
      Oracle 11gr2
        • 1. Re: XML issue
          BluShadow
          You can extract data from your XML like this...
          SQL> ed
          Wrote file afiedt.buf
          
            1  with t as (select xmltype('<?xml version="1.0" encoding="UTF-8"?>
            2  <marketing-expenses xsi:noNamespaceSchemaLocation="C:/Users/r.rahul/Documents/Altova/MapForce2013/MapForceExamples/MarketingExpenses.xsd" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
            3     <Person>
            4             <FullName>Fred Landis</FullName>
            5             <Title>Project Manager</Title>
            6             <Phone>123-456-7890</Phone>
            7             <Email>f.landis@nanonull.com</Email>
            8     </Person>
            9     <expense-item>
           10             <type>Meal</type>
           11             <Date>2003-01-01</Date>
           12             <expense>122.11</expense>
           13     </expense-item>
           14     <expense-item>
           15             <type>Lodging</type>
           16             <Date>2003-01-02</Date>
           17             <expense>122.12</expense>
           18     </expense-item>
           19  </marketing-expenses>') as xml from dual)
           20  --
           21  -- end of test data
           22  --
           23  select x.person_fullname, x.person_title, x.person_phone, x.person_email
           24        ,y.*
           25  from   t
           26        ,xmltable('/marketing-expenses'
           27                  passing t.xml
           28                  columns person_fullname varchar2(20) path './Person/FullName'
           29                         ,person_title    varchar2(20) path './Person/Title'
           30                         ,person_phone    varchar2(20) path './Person/Phone'
           31                         ,person_email    varchar2(25) path './Person/Email'
           32                         ,expenses        xmltype      path '.'
           33                 ) x
           34        ,xmltable('/marketing-expenses/expense-item'
           35                  passing x.expenses
           36                  columns expense_type    varchar2(10) path './type'
           37                         ,expense_date    varchar2(10) path './Date'
           38                         ,expense_val     number       path './expense'
           39*                ) y
          SQL> /
          
          PERSON_FULLNAME      PERSON_TITLE         PERSON_PHONE         PERSON_EMAIL              EXPENSE_TY EXPENSE_DA EXPENSE_VAL
          -------------------- -------------------- -------------------- ------------------------- ---------- ---------- -----------
          Fred Landis          Project Manager      123-456-7890         f.landis@nanonull.com     Meal       2003-01-01      122.11
          Fred Landis          Project Manager      123-456-7890         f.landis@nanonull.com     Lodging    2003-01-02      122.12
          
          SQL>
          Or have a look at Mark Drake's example of shredding XML using a schema here:

          Re: XML file processing into oracle
          • 2. Re: XML issue
            Rahul_India
            i dont get your code at all :(

            Edited by: Rahul_India on Dec 10, 2012 4:19 PM
            • 3. Re: XML issue
              odie_63
              SQL> create table tmp_xml of xmltype;
               
              Table created
               
              SQL> 
              SQL> insert into tmp_xml
                2  values(
                3    xmltype(bfilename('TEST_DIR','market.xml'), nls_charset_id('AL32UTF8'))
                4  );
               
              1 row inserted
               
              SQL> commit
                2  ;
               
              Commit complete
               
              SQL> 
              SQL> insert into persondetail (fullname, title, phone, email)
                2  select x.fullname, x.title, x.phone, x.email
                3  from tmp_xml t
                4     , xmltable(
                5         '/marketing-expenses/Person'
                6         passing t.object_value
                7         columns FullName varchar2(30) path 'FullName'
                8               , Title    varchar2(30) path 'Title'
                9               , Phone    varchar2(30) path 'Phone'
               10               , Email    varchar2(30) path 'Email'
               11       ) x
               12  ;
               
              1 row inserted
               
              SQL> REM - if necessary :
              SQL> alter session set nls_numeric_characters=".,";
               
              Session altered
               
              SQL> 
              SQL> insert into expenseitem (rawtype, rawdate, rawexpense)
                2  select x.rawtype, x.rawdate, x.rawexpense
                3  from tmp_xml t
                4     , xmltable(
                5         '/marketing-expenses/expense-item'
                6         passing t.object_value
                7         columns rawtype    varchar2(30) path 'type'
                8               , rawdate    date         path 'Date'
                9               , rawexpense number       path 'expense'
               10       ) x
               11  ;
               
              2 rows inserted
               
              SQL> select * from persondetail;
               
              FULLNAME                       TITLE                          PHONE                          EMAIL
              ------------------------------ ------------------------------ ------------------------------ ------------------------------
              Fred Landis                    Project Manager                123-456-7890                   f.landis@nanonull.com
               
              SQL> select * from expenseitem;
               
              RAWTYPE                        RAWDATE     RAWEXPENSE
              ------------------------------ ----------- ----------
              Meal                           01/01/2003      122,11
              Lodging                        02/01/2003      122,12
               
              SQL> delete tmp_xml;
               
              1 row deleted
               
              SQL> commit;
               
              Commit complete
               
              If <Person> occurs only once per document, you can also use Blushadow's query combined with a multitable INSERT to load all the data in a single statement.
              Like this : http://odieweblog.wordpress.com/2012/05/10/how-to-load-xml-data-into-multiple-tables/

              Edited by: odie_63 on 10 déc. 2012 11:54 - added link
              • 4. Re: XML issue
                Rahul_India
                I dont have a create directory privilege.
                i am doing this
                insert into tmp_xml
                   values
                   (
                  xmltype('<?xml version="1.0" encoding="UTF-8"?>
                <marketing-expenses xsi:noNamespaceSchemaLocation="C:/Users/r.rahul/Documents/Altova/MapForce2013/MapForceExamples/MarketingExpenses.xsd" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
                     <Person>
                          <FullName>Fred Landis</FullName>
                          <Title>Project Manager</Title>
                          <Phone>123-456-7890</Phone>
                          <Email>f.landis@nanonull.com</Email>
                     </Person>
                     <expense-item>
                          <type>Meal</type>
                          <Date>2003-01-01</Date>
                          <expense>122.11</expense>
                     </expense-item>
                     <expense-item>
                          <type>Lodging</type>
                          <Date>2003-01-02</Date>
                          <expense>122.12</expense>
                     </expense-item>')
                  );
                but getting the error
                Error report:
                SQL Error: ORA-31061: XDB error: XML event error
                ORA-19202: Error occurred in XML processingLPX-00007: unexpected end-of-file encountered
                • 5. Re: XML issue
                  BluShadow
                  Rahul_India wrote:
                  i dont get your code at all :(
                  That's like saying you have code that doesn't work, but not explaining in what way.

                  The code works, it does what you asked for. So what exactly is your problem?
                  • 6. Re: XML issue
                    Rahul_India
                    BluShadow wrote:
                    Rahul_India wrote:
                    i dont get your code at all :(
                    That's like saying you have code that doesn't work, but not explaining in what way.

                    The code works, it does what you asked for. So what exactly is your problem?
                    Actually it's my fault.I am not able to get the semantics of the query .How is it working syntactically?
                    • 7. Re: XML issue
                      lee200
                      The XML is incorrectly formed. It should have the closing </marketing-expenses> tag:
                      insert into tmp_xml
                         values
                         (
                        xmltype('<?xml version="1.0" encoding="UTF-8"?>
                      <marketing-expenses xsi:noNamespaceSchemaLocation="C:/Users/r.rahul/Documents/Altova/MapForce2013/MapForceExamples/MarketingExpenses.xsd" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
                           <Person>
                                <FullName>Fred Landis</FullName>
                                <Title>Project Manager</Title>
                                <Phone>123-456-7890</Phone>
                                <Email>f.landis@nanonull.com</Email>
                           </Person>
                           <expense-item>
                                <type>Meal</type>
                                <Date>2003-01-01</Date>
                                <expense>122.11</expense>
                           </expense-item>
                           <expense-item>
                                <type>Lodging</type>
                                <Date>2003-01-02</Date>
                                <expense>122.12</expense>
                           </expense-item>
                      </marketing-expenses>')
                        );
                      • 8. Re: XML issue
                        BluShadow
                        Rahul_India wrote:
                        BluShadow wrote:
                        Rahul_India wrote:
                        i dont get your code at all :(
                        That's like saying you have code that doesn't work, but not explaining in what way.

                        The code works, it does what you asked for. So what exactly is your problem?
                        Actually it's my fault.I am not able to get the semantics of the query .How is it working syntactically?
                        Well which part are you not understanding?

                        XMLTABLE (in the way I've used it) supplies an XQuery/XPath expression which defines the repeating groups within the XML, the "passing" clause passes in the XML required to be processed by it, and the "columns" defines the output columns of it, which each specify the path where the data is to be found in the repeating group.

                        I've used 2 XMLTABLE expressions which is causing a cartesian product in this case, because the XML has one person with multiple expenses, so the top XMLTABLE is getting the person details, and the bottom XMLTABLE is providing each of the expenses.
                        • 9. Re: XML issue
                          Rahul_India
                          BluShadow wrote:
                          Rahul_India wrote:
                          BluShadow wrote:
                          Rahul_India wrote:
                          i dont get your code at all :(
                          That's like saying you have code that doesn't work, but not explaining in what way.

                          The code works, it does what you asked for. So what exactly is your problem?
                          Actually it's my fault.I am not able to get the semantics of the query .How is it working syntactically?
                          Well which part are you not understanding?

                          XMLTABLE (in the way I've used it) supplies an XQuery/XPath expression which defines the repeating groups within the XML, the "passing" clause passes in the XML required to be processed by it, and the "columns" defines the output columns of it, which each specify the path where the data is to be found in the repeating group.

                          I've used 2 XMLTABLE expressions which is causing a cartesian product in this case, because the XML has one person with multiple expenses, so the top XMLTABLE is getting the person details, and the bottom XMLTABLE is providing each of the expenses.
                          Got your code now...
                          but what does these lines mean exactly?
                          xmltable('/marketing-expenses'
                                          passing t.xml
                          • 10. Re: XML issue
                            odie_63
                            Time to read the manual : http://docs.oracle.com/cd/E11882_01/appdev.112/e23094/xdb_xquery.htm#CBAGCBGJ
                            • 11. Re: XML issue
                              Rahul_India
                              odie_63 wrote:
                              Time to read the manual : http://docs.oracle.com/cd/E11882_01/appdev.112/e23094/xdb_xquery.htm#CBAGCBGJ
                              lol i knew.
                              goes off to read