1 2 Previous Next 16 Replies Latest reply: Dec 7, 2012 7:20 AM by BluShadow RSS

    Converting XML file into .txt file or .csv format

    Rahul_India
      Good Morning everyone :)
      How can i convert XML file into .txt file or .csv file using pl/sql?
      Is it possible?
        • 1. Re: Converting XML file into .txt file or .csv format
          PeterValencic
          What would you like to do?
          XML is also TXT file.... Be more precise...

          You can extract values from xml with xpath and then you can make a txt or csv (delimited file)...

          Xpath:
          http://www.java2s.com/Tutorial/Oracle/0640__XML/XPathtextfunction.htm
          • 2. Re: Converting XML file into .txt file or .csv format
            Marwim
            Hello,
            How can i convert XML file into .txt file or .csv file using pl/sql?
            no need to convert. An XML file is a text file. You name the extension .txt and you have a txt file :-)

            Seriously: you have to give us more details. You can read a XML file with PL/SQL and you can convert it into different output formats. You have to describe what you want. Give us an example of your XML and an example of the expected output.

            {message:id=9360002}

            Regards
            Marcus

            BTW: There is a forum especially for XML and PL/SQL {forum:id=157}
            • 3. Re: Converting XML file into .txt file or .csv format
              Rahul_India
              >
              Seriously: you have to give us more details. You can read a XML file with PL/SQL and you can convert it into different output formats. You have to describe what you want. Give us an example of your XML and an example of the expected output.
              >
              Regards
              Marcus
              I dont have a sample xml file.BUt requirement as of now is that xml file i will get is very complex and we are using tools to convert into simple xmple file applying filtering conditions i guess..... i will get back with a sample xml file :)
              • 4. Re: Converting XML file into .txt file or .csv format
                BluShadow
                I would have thought by now you would have learnt how to ask a question on the forums, and know that people need details to be able to help you, not just some generic question.

                Here's an example of taking some XML and flattening it to a table style:
                SQL> ed
                Wrote file afiedt.buf
                
                  1  WITH t as (select XMLTYPE('
                  2  <RECSET xmlns:aa="http://www.w3.org">
                  3    <aa:REC>
                  4      <aa:COUNTRY>1</aa:COUNTRY>
                  5      <aa:POINT>1800</aa:POINT>
                  6      <aa:USER_INFO>
                  7        <aa:USER_ID>1</aa:USER_ID>
                  8        <aa:TARGET>28</aa:TARGET>
                  9        <aa:STATE>6</aa:STATE>
                 10        <aa:TASK>12</aa:TASK>
                 11      </aa:USER_INFO>
                 12      <aa:USER_INFO>
                 13        <aa:USER_ID>5</aa:USER_ID>
                 14        <aa:TARGET>19</aa:TARGET>
                 15        <aa:STATE>1</aa:STATE>
                 16        <aa:TASK>90</aa:TASK>
                 17      </aa:USER_INFO>
                 18    </aa:REC>
                 19    <aa:REC>
                 20      <aa:COUNTRY>2</aa:COUNTRY>
                 21      <aa:POINT>2400</aa:POINT>
                 22      <aa:USER_INFO>
                 23        <aa:USER_ID>3</aa:USER_ID>
                 24        <aa:TARGET>14</aa:TARGET>
                 25        <aa:STATE>7</aa:STATE>
                 26        <aa:TASK>5</aa:TASK>
                 27      </aa:USER_INFO>
                 28    </aa:REC>
                 29  </RECSET>') as xml from dual)
                 30  -- END OF TEST DATA
                 31  select x.country, x.point, y.user_id, y.target, y.state, y.task
                 32  from t
                 33      ,XMLTABLE(XMLNAMESPACES('http://www.w3.org' as "aa"),
                 34                '/RECSET/aa:REC'
                 35                PASSING t.xml
                 36                COLUMNS country NUMBER PATH '/aa:REC/aa:COUNTRY'
                 37                       ,point   NUMBER PATH '/aa:REC/aa:POINT'
                 38                       ,user_info XMLTYPE PATH '/aa:REC/*'
                 39               ) x
                 40      ,XMLTABLE(XMLNAMESPACES('http://www.w3.org' as "aa"),
                 41                '/aa:USER_INFO'
                 42                PASSING x.user_info
                 43                COLUMNS user_id NUMBER PATH '/aa:USER_INFO/aa:USER_ID'
                 44                       ,target  NUMBER PATH '/aa:USER_INFO/aa:TARGET'
                 45                       ,state   NUMBER PATH '/aa:USER_INFO/aa:STATE'
                 46                       ,task    NUMBER PATH '/aa:USER_INFO/aa:TASK'
                 47*              ) y
                SQL> /
                
                   COUNTRY      POINT    USER_ID     TARGET      STATE       TASK
                ---------- ---------- ---------- ---------- ---------- ----------
                         1       1800          1         28          6         12
                         1       1800          5         19          1         90
                         2       2400          3         14          7          5
                Once you've got it flattened, you can use any of the regular techniques for taking data from a table, and creating CSV or TXT files as you require.
                (and that can be found by googling or searching these forums, so don't dare ask for an example of that too)
                • 5. Re: Converting XML file into .txt file or .csv format
                  BluShadow
                  Further example(s) of shredding XML documents, especially if they are large, can be found in the XML DB Forum e.g.

                  Re: XML file processing into oracle
                  • 6. Re: Converting XML file into .txt file or .csv format
                    odie_63
                    I dont have a sample xml file.BUt requirement as of now is that xml file i will get is very complex and we are using tools to convert into simple xmple file applying filtering conditions i guess..... i will get back with a sample xml file :)
                    Oracle XML DB has a lot of great features to deal with XML files, no matter how complex it is.
                    If you want to convert it to a flat file format in the hope of handling it easily, you're probably in the wrong track.

                    As mentioned already, make sure you explain your requirement as clearly as possible.

                    If the purpose is really to transform XML into another format, then XSLT may be a good choice.
                    You'll find plenty of sample XSL stylesheets on the Internet to convert to CSV, JSON etc.
                    • 7. Re: Converting XML file into .txt file or .csv format
                      Rahul_India
                      odie_63 wrote:


                      If the purpose is really to transform XML into another format, then XSLT may be a good choice.
                      You'll find plenty of sample XSL stylesheets on the Internet to convert to CSV, JSON etc.
                      What we are doing now
                                                coverting xml into .txt or 
                                                  .csv using altova                                        loading data into 
                                                                                                                 database
                      COMPLEX XML file------------------------------------> TEXT OR CSV file----------------------------->DATABASE
                      I dont have access to xml file as of now.I will get the sample XML file and post it.
                      I want them to use pl/sql to directly load data into table or covert into .csv file(using pl/sql)
                      Thanks
                      • 8. Re: Converting XML file into .txt file or .csv format
                        odie_63
                        That's what I thought.

                        You don't need intermediate transformation steps to load XML into relational tables.

                        Review BluShadow's example and link given above to get an idea of what you can do.

                        Don't forget to give your exact database version.
                        • 9. Re: Converting XML file into .txt file or .csv format
                          Rahul_India
                          odie_63 wrote:
                          That's what I thought.

                          You don't need intermediate transformation steps to load XML into relational tables.

                          Review BluShadow's example and link given above to get an idea of what you can do.

                          Don't forget to give your exact database version.
                          <?xml version="1.0" encoding="UTF-8"?><DIAMessage xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns="http://pearson
                          .com/DIA" xsi:schemaLocation="http://pearson.com/DIA http://esb.svvsbak.pearsontc.net:8001/dia/DIA.xsd" release="1.2">
                          <Header sourceSystem="WCM_APRIMO" messageDate="2012-10-18 06:42:18.0" messageNumber="1360691" messageNote="+diaEnvironment+">
                          <defaultLanguage>eng</defaultLanguage>
                          </Header>
                          <Account opCode="I" DIAKey="85539051" sourceClassName="ACCOUNT" sourceInstanceID="DIATEMP|$|437625" sourceSystem="WCM_APRIMO"
                          >
                          <accountCategory opCode="I" sourceClassName="AccountCategory" sourceInstanceID="DIATEMP" sourceSystem="WCM_APRIMO">
                          <name opCode="I">Temporary Account created in DIA for orphan Person records</name>
                          </accountCategory>
                          <hasAccountRelation opCode="I" DIAKey="89958987" sourceClassName="ACCOUNT_RELATION" sourceInstanceID="DIATEMP|$|437625" sourc
                          eSystem="WCM_APRIMO">
                          <hasAccountPerson opCode="I" DIAKey="87907990" sourceClassName="PERSON" sourceInstanceID="437625" sourceSystem="WCM_APRIMO">
                          <hasIdentifier opCode="I" DIAKey="84090020" sourceClassName="PERSON_IDENTIFIER" sourceInstanceID="437625" sourceSystem="WCM_A
                          PRIMO">
                          <IDType opCode="I">WCM_PERSON_ID</IDType>
                          <IDTypeName opCode="I">WCM Person ID</IDTypeName>
                          <IDValue opCode="I">437625</IDValue>
                          </hasIdentifier>
                          <hasEmail opCode="I" DIAKey="86691097" sourceClassName="EMAIL" sourceInstanceID="437842123" sourceSystem="WCM_APRIMO">
                          <name opCode="I">Email1</name>
                          <contactInfoType opCode="I" DIAKey="84366553" sourceClassName="EmailType" sourceInstanceID="PrimaryEmail" sourceSystem="WCM_A
                          PRIMO">
                          <name opCode="I">Primary Email</name>
                          </contactInfoType>
                          <preferredContactInfo opCode="I">Y</preferredContactInfo>
                          <email opCode="I">haoch123@sina.com</email>
                          </hasEmail>
                          <hasPostalAddress opCode="I" DIAKey="89237762" sourceClassName="POSTAL_ADDRESS" sourceInstanceID="437842" sourceSystem="WCM_A
                          PRIMO">
                          <name opCode="I">Hao Caihong</name>
                          <contactInfoType opCode="I" DIAKey="84366557" sourceClassName="AddressType" sourceInstanceID="INSTITUTION" sourceSystem="WCM_
                          APRIMO">
                          <name opCode="I">INSTITUTION</name>
                          </contactInfoType>
                          <preferredContactInfo opCode="I">Y</preferredContactInfo>
                          <country opCode="I">China</country>
                          </hasPostalAddress>
                          <alias opCode="I">Guest730689</alias>
                          <firstName opCode="I">Hao</firstName>
                          <hasPersonRelation opCode="I" DIAKey="91151735" sourceClassName="PERSON_RELATION" sourceInstanceID="437625" sourceSystem="WCM
                          _APRIMO">
                          <hasPersonOrganization opCode="I" DIAKey="34469188" sourceClassName="DIVISION" sourceInstanceID="ELT" sourceSystem="WCM_APRIM
                          O">
                          <name opCode="I">English Language Teaching</name>
                          <hasOrganizationRelation opCode="I" DIAKey="35830121" sourceClassName="DIVISION_HAS_SUB_DIVISION" sourceInstanceID="ELT|$|GG"
                           sourceSystem="WCM_APRIMO">
                          <name opCode="I">GG is a sub division of ELT</name>
                          <hasOrganization opCode="I" DIAKey="34469189" sourceClassName="SUB_DIVISION" sourceInstanceID="GG" sourceSystem="WCM_APRIMO">
                          <name opCode="I">Global</name>
                          </hasOrganization>
                          <organizationRelationType sourceClassName="OrganizationRelationType" sourceInstanceID="DIVISION_HAS_SUB_DIVISION" sourceSyste
                          m="WCM_APRIMO"/>
                          </hasOrganizationRelation>
                          </hasPersonOrganization>
                          </hasPersonRelation>
                          <hasPersonStatus opCode="I" DIAKey="84366561" sourceClassName="PersonStatus" sourceInstanceID="A" sourceSystem="WCM_APRIMO">
                          <name opCode="I">A</name>
                          </hasPersonStatus>
                          <hasSubscription opCode="I" DIAKey="85247769" sourceClassName="SUBSCRIPTION" sourceInstanceID="447926" sourceSystem="WCM_APRI
                          MO">
                          <name opCode="I">PEARSON LONGMAN ELT MAILING LIST</name>
                          <personSiteId opCode="I">868015</personSiteId>
                          <registrationDate opCode="I">30-09-2011 12:00:00</registrationDate>
                          <subscriptionStatus opCode="I">ACTIVE</subscriptionStatus>
                          <subStartDate opCode="I">30-09-2011 12:00:00</subStartDate>
                          </hasSubscription>
                          <hasWebsiteRelation opCode="I" DIAKey="92283375" sourceClassName="WEBSITE_RELATION" sourceInstanceID="868015" sourceSystem="W
                          CM_APRIMO">
                          <hasWSRelWebsite opCode="I" DIAKey="68272713" sourceClassName="WEBSITE" sourceInstanceID="pearsonelt" sourceSystem="WCM_APRIM
                          O">
                          <name opCode="I">pearsonelt</name>
                          </hasWSRelWebsite>
                          <websiteRelationDate opCode="I">30-09-2011 00:00:00</websiteRelationDate>
                          <websiteRelationStatus opCode="I">A</websiteRelationStatus>
                          </hasWebsiteRelation>
                          <isPersonSuspended opCode="I">A</isPersonSuspended>
                          <lastName opCode="I">Caihong</lastName>
                          <userName opCode="I">Guest730689</userName>
                          </hasAccountPerson>
                          </hasAccountRelation>
                          </Account>
                          <Account opCode="I" DIAKey="85539053" sourceClassName="ACCOUNT" sourceInstanceID="DIATEMP|$|437622" sourceSystem="WCM_APRIMO"
                          >
                          <accountCategory opCode="I" sourceClassName="AccountCategory" sourceInstanceID="DIATEMP" sourceSystem="WCM_APRIMO">
                          <name opCode="I">Temporary Account created in DIA for orphan Person records</name>
                          This IS THE SAMPLE xml file (icomplete as it is too long 70kb ).

                          Select *from v$version
                          Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
                          PL/SQL Release 11.2.0.2.0 - Production
                          "CORE     11.2.0.2.0     Production"
                          TNS for Linux: Version 11.2.0.2.0 - Production
                          NLSRTL Version 11.2.0.2.0 - Production
                          Edited by: Rahul_India on Dec 7, 2012 3:44 PM
                          • 10. Re: Converting XML file into .txt file or .csv format
                            odie_63
                            And the expected output would be?

                            Give the DDLs of the target tables, and mapping between XML nodes and columns.
                            • 11. Re: Converting XML file into .txt file or .csv format
                              Rahul_India
                              odie_63 wrote:
                              And the expected output would be?

                              Give the DDLs of the target tables, and mapping between XML nodes and columns.
                              I will get back at you with this tomorrow.My manager is not present.he only knows that.
                              I just want to ask
                              1)Does xml looks complex to you?or in general ?
                              2)From one xml file can i insert data into multiple tables in DB?
                              3)Is it advisable to use tool for this method or just plain pl/sql.They say that there are lot of filtering conditions applied too.
                              • 12. Re: Converting XML file into .txt file or .csv format
                                odie_63
                                1)Does xml looks complex to you?or in general ?
                                There are some nested levels, but no, that doesn't make it complex to handle.
                                2)From one xml file can i insert data into multiple tables in DB?
                                Yes.
                                3)Is it advisable to use tool for this method or just plain pl/sql.They say that there are lot of filtering conditions applied too.
                                Plain (PL/)SQL will do fine.
                                Not sure what you mean by filtering conditions though.
                                • 13. Re: Converting XML file into .txt file or .csv format
                                  Rahul_India
                                  blue shadow i am getting an error while executing your query
                                  ORA-00600: internal error code, arguments: [kkoljt1], [], [], [], [], [], [], [], [], [], [], []
                                  00600. 00000 -  "internal error code, arguments: [%s], [%s], [%s], [%s], [%s], [%s], [%s], [%s]"
                                  *Cause:    This is the generic internal error number for Oracle program
                                             exceptions.     This indicates that a process has encountered an
                                             exceptional condition.
                                  *Action:   Report as a bug - the first argument is the internal error number
                                  • 14. Re: Converting XML file into .txt file or .csv format
                                    BluShadow
                                    Rahul_India wrote:
                                    blue shadow i am getting an error while executing your query
                                    ORA-00600: internal error code, arguments: [kkoljt1], [], [], [], [], [], [], [], [], [], [], []
                                    00600. 00000 -  "internal error code, arguments: [%s], [%s], [%s], [%s], [%s], [%s], [%s], [%s]"
                                    *Cause:    This is the generic internal error number for Oracle program
                                    exceptions.     This indicates that a process has encountered an
                                    exceptional condition.
                                    *Action:   Report as a bug - the first argument is the internal error number
                                    And your database version is?
                                    1 2 Previous Next