9 Replies Latest reply: Apr 2, 2014 7:32 AM by odie_63 RSS

    How to get the column name and table name from xml file

    Girish Attarde

      I have one XML file, I generated xsd file from that xml file but the problem is i dont know table name and column name. So my question is how can I retrieve the data from that xml file?

        • 1. Re: How to get the column name and table name from xml file
          odie_63

          Hi,

           

          I have no idea what you've done nor what you're trying to do.

          So, if you want help, please post a complete test case and explain what you want to do.

           

          (don't forget to mention the db version, all digits)

           

          Thanks.

          • 2. Re: How to get the column name and table name from xml file
            Girish Attarde

            My db version: 11.2.0.3 RAC on solaris 10

            We have already installed XDB. Now we have a text.xsd(derived from test.xml using Oxygen XML software ) and text.xml with us being loaded into database but we are unable to fetch or retrieve data from it i.e we dont know table and column name from that file.

             

            And hence unable to fetch data from XML table.

            • 3. Re: How to get the column name and table name from xml file
              odie_63

              and text.xml with us being loaded into database

              Where is this file loaded? in a table, a local variable in a program, a repository resource, ... ?

               

              i.e we dont know table and column name from that file.

              Sorry, I don't understand what that means.

              What table? What column?

               

              Again, post what you've done so far, hopefully it'll be clearer with an example.

              • 4. Re: How to get the column name and table name from xml file
                Girish Attarde

                test.xml file is loaded in XML DB repository using DBMS_XDB.CREATERESOURCE API, now we want to retrive the data from test.xml, how can we retrieve it?



                I followed all the steps from here : loading xml files with xml db

                Just replaced XSD file and XML file with my appropriate location and directory.

                Now I just want to retrieve data using SELECT query.

                • 5. Re: How to get the column name and table name from xml file
                  odie_63

                  Ok, now we're getting somewhere. Why didn't you say that in the first place?

                   

                  You can retrieve the XML document (as XMLType datatype) using an XDBURITYPE instance, e.g.

                  xdburitype('/public/test1.xml').getxml()

                   

                  Have you registered the schema?

                  If you've registered the schema in the DB with genTables = true, then Oracle has created an XMLType table to store XML instances.

                  Provided the resource created in the repository is a valid schema-based XML document, it will be automatically loaded in that table.

                   

                  Just saw your edit :

                  The article explains all you need to load and parse XML document, including where to find the generated table(s), just take the time to read it carefully.

                  • 6. Re: How to get the column name and table name from xml file
                    Girish Attarde

                    We are using dba_xml_tables and dba_xml_schemas to get the table name with reference to our test.xsd file.

                    But the view shows too many tables for the perticular XSD file. When selecting data from any table using proper xml SELECT syntax it shows ORA-00942 table or view does not exist.

                    • 8. Re: How to get the column name and table name from xml file
                      Girish Attarde

                      <?xml version="1.0" encoding="UTF-8" ?>

                          <xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema">

                                <xs:element name="employee">

                                <xs:complexType>

                                <xs:sequence>

                                    <xs:element name="details">

                                    <xs:complexType>

                                    <xs:sequence>

                                          <xs:element name="emp_id" type="xs:int"/>

                                          <xs:element name="emp_name" type="xs:string"/>

                                          <xs:element name="emp_age" type="xs:int"/>

                                          <xs:element name="emp_dept" type="xs:string"/>

                                    </xs:sequence>

                                    </xs:complexType>

                                    </xs:element>

                                </xs:sequence>

                                </xs:complexType>

                                </xs:element>

                            </xs:schema>

                      • 9. Re: How to get the column name and table name from xml file
                        odie_63

                        Here's an example using binary XML storage (instead of Object-Relational storage as described in the article).

                         

                        begin

                         

                          dbms_xmlschema.registerSchema(

                            schemaURL       => 'my_schema.xsd'

                          , schemaDoc       => xmltype(bfilename('TEST_DIR','my_schema.xsd'), nls_charset_id('AL32UTF8'))

                          , local           => true

                          , genTypes        => false

                          , genTables       => true

                          , enableHierarchy => dbms_xmlschema.ENABLE_HIERARCHY_CONTENTS

                          , options         => dbms_xmlschema.REGISTER_BINARYXML

                          );

                         

                        end;

                        /

                         

                        genTables => true : means that a default schema-based XMLType table will be created during registration.

                        enableHierarchy => dbms_xmlschema.ENABLE_HIERARCHY_CONTENTS : indicates that a repository resource conforming to the schema will be automatically stored in the default table.

                         

                        If the schema is not annotated, the name of the default table is system-generated but derived from the root element name :

                        SQL> select table_name

                          2  from user_xml_tables

                          3  where xmlschema = 'my_schema.xsd'

                          4  and element_name = 'employee';

                         

                        TABLE_NAME

                        ------------------------------

                        employee1121_TAB

                        (warning : the name is case-sensitive)

                         

                        To annotate the schema and control the naming, modify the content to :

                        <?xml version="1.0" encoding="UTF-8" ?>

                        <xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns:xdb="http://xmlns.oracle.com/xdb">

                          <xs:element name="employee" xdb:defaultTable="EMPLOYEE_XML">

                            <xs:complexType>

                              ...

                         

                        Next step : create a resource, or just directly insert an XML document into the table.

                        Example of creating a resource :

                        declare

                         

                          res  boolean;

                          doc  xmltype := xmltype(

                        '<employee>

                          <details>

                            <emp_id>1</emp_id>

                            <emp_name>SMITH</emp_name>

                            <emp_age>40</emp_age>

                            <emp_dept>10</emp_dept>

                          </details>

                        </employee>'

                        );

                         

                        begin

                          res := dbms_xdb.CreateResource(

                                   abspath   => '/public/test.xml'

                                 , data      => doc

                                 , schemaurl => 'my_schema.xsd'

                                 , elem      => 'employee'

                                 );  

                        end;

                        /

                         

                        The resource has to be schema-based so that the default storage mechanism is triggered.

                        It could also be achieved if the document possesses an xsi:noNamespaceSchemaLocation attribute :

                        SQL> declare

                          2 

                          3    res  boolean;

                          4    doc  xmltype := xmltype(

                          5  '<employee xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"

                          6             xsi:noNamespaceSchemaLocation="my_schema.xsd">

                          7    <details>

                          8      <emp_id>1</emp_id>

                          9      <emp_name>SMITH</emp_name>

                        10      <emp_age>40</emp_age>

                        11      <emp_dept>10</emp_dept>

                        12    </details>

                        13   </employee>'

                        14   );

                        15 

                        16  begin

                        17    res := dbms_xdb.CreateResource(

                        18             abspath   => '/public/test.xml'

                        19           , data      => doc

                        20           );

                        21  end;

                        22  /

                         

                        PL/SQL procedure successfully completed

                         

                        SQL> set long 5000

                        SQL> select * from "employee1121_TAB";

                         

                        SYS_NC_ROWINFO$

                        --------------------------------------------------------------------------------

                        <employee xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceS

                          <details>

                            <emp_id>1</emp_id>

                            <emp_name>SMITH</emp_name>

                            <emp_age>40</emp_age>

                            <emp_dept>10</emp_dept>

                          </details>

                        </employee>

                         

                        Then use XMLTABLE to shred the XML into relational format :

                        SQL> select x.*

                          2  from "employee1121_TAB" t

                          3     , xmltable('/employee/details'

                          4         passing t.object_value

                          5         columns emp_id   integer      path 'emp_id'

                          6               , emp_name varchar2(30) path 'emp_name'

                          7       ) x

                          8  ;

                         

                                                         EMP_ID EMP_NAME

                        --------------------------------------- ------------------------------

                                                              1 SMITH