7 Replies Latest reply on Feb 8, 2013 10:56 AM by odie_63

    XmlSerialize - SqlDeveloper

    989750
      Hi everyone! I have a file .xml stored in C:\ and i want to extract some tag from this file and insert them into a table as varchar/interger. I'm trying to use xmlserialize, but it doesn't work:

      insert into categoria(nome) values (XMLSERIALIZE(DOCUMENT 'C:\file.xml\Name' AS varchar(30)));

      The file.xml is something like this:

      <?xml version="1.0" encoding="UTF-8"?>
      <GolfCompetition xsi:noNamespaceSchemaLocation="GolfCompetition.xsd" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
           <Name>Summer Cup</Name>
           <Date>2010-05-24</Date>
           <Sponsor>Yuppi Yuppi</Sponsor>
           <Category NumPrize="2" From="0" To="12">First</Category>
      </GolfCompetition>

      And i want to obtain a table like that:

      Category
      -------------------------------------------------------
      Name | Num_P | Min_H | Max_H
      --------------------------------------------------------
      Summer Cup | 2 | 0 | 12


      An important thing is that i have to access the file directly like in my example. I'm using SQLDevelepor.

      Thanks in advance for any help :)
        • 1. Re: XmlSerialize - SqlDeveloper
          odie_63
          Hi,

          Welcome to the forum.

          I'm sorry to put it like this but you're missing some very important concepts here.

          A few questions first :

          - The file is on your local drive?
          - Is the database on your local machine too, or on a remote server?
          - What's the database version?

          Where did you learn about XMLSerialize?
          That function takes an XMLType instance (transient variable or persistent object in the db) and convert it to a character or byte stream representation (VARCHAR2, CLOB or BLOB).
          So it's clearly not what you're after.
          • 2. Re: XmlSerialize - SqlDeveloper
            989750
            The file .xml is on my local drive, but i access the DB remotly with SQLDeveloper and i think that the DB version is 10 or 11g.

            So how can i obtain a table such that starting from an xml file?

            Thanks in advance.
            • 3. Re: XmlSerialize - SqlDeveloper
              odie_63
              Accessing the file directly from a query is only possible if it is located on the database server or another network location the db has access too.

              You'll have to send the file to the server, for example using FTP or SQL*Loader to insert it in a table.
              • 4. Re: XmlSerialize - SqlDeveloper
                989750
                Ok so i've some question about this thread... If I install on my pc oracle 11g where is the directory to put the file.xml?
                • 5. Re: XmlSerialize - SqlDeveloper
                  odie_63
                  So now you want to install a database on your machine?

                  What's your requirement? What do you want to do ultimately with the XML file?

                  where is the directory to put the file.xml?
                  Once you manage to get the file local to the db, create an Oracle directory object, for example :
                  create or replace directory TEST_DIR
                    as 'c:\oracle\XML';
                  and you will be able to access the data relationally, like this :
                  SQL> select x.*
                    2  from xmltable(
                    3         '/GolfCompetition'
                    4         passing xmltype(bfilename('TEST_DIR', 'file.xml'), nls_charset_id('AL32UTF8'))
                    5         columns name   varchar2(30) path 'Name'
                    6               , num_p  number       path 'Category/@NumPrize'
                    7               , min_h  number       path 'Category/@From'
                    8               , max_h  number       path 'Category/@To'
                    9       ) x
                   10  ;
                   
                  NAME                                NUM_P      MIN_H      MAX_H
                  ------------------------------ ---------- ---------- ----------
                  Summer Cup                              2          0         12
                   
                  Edited by: odie_63 on 7 févr. 2013 21:47
                  • 6. Re: XmlSerialize - SqlDeveloper
                    989750
                    Thanks so much for your help. The real important thing is that i access the file .xml directly.

                    However, if i run
                    create or replace directory TEST_DIR as 'c:\oracle\XML';
                    SQLDev tells me that the directory was created, but i can't find it on my local drive. Why? Is it created on the server?
                    • 7. Re: XmlSerialize - SqlDeveloper
                      odie_63
                      The CREATE DIRECTORY command doesn't create anything physically on the machine. It's up to you to create the directory in the filesystem.