5 Replies Latest reply: Feb 20, 2013 5:07 PM by 889187 RSS

    Issues reading XML file

    889187
      Hello all,

      I am having trouble reading the XML file. I believe I have done the right query except the results back are blank and I am not sure why?

      Version = Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production

      XML File: E_table_export_CES.xml

      <?xml version='1.0' encoding='UTF-8' ?>
      <RESULTS>
           <ROW>
                <COLUMN NAME="ID">12688f8ac8aa6310VgnVCM10000078ccc70a____</COLUMN>
                <COLUMN NAME="BRANCH_NAME">Brooklyn</COLUMN>
           </ROW>
      </RESULTS>

      CREATE DIRECTORY XMLDIRX AS '/dbms/xml';

      Query that I am using:

      select *
      FROM xmltable ('*/ROW'
      passing XMLTYPE (bfilename ('XMLDIRX', 'E_table_export_CES.xml'), NLS_CHARSET_ID ('UTF8'))
      columns
      "ID" VARCHAR2(200) path '@ID'
      ,"BRANCH_NAME" varchar2(25) path '@BRANCH_NAME'
      ) xtbl

      I am getting back ID and Branch_Name columns but the row is empty. Just not sure what I am doing wrong. Any help would greatly be appreciated!
        • 1. Re: Issues reading XML file
          Jason_(A_Non)
          Your XML sample does not have attributes called ID or BRANCH_NAME. The only attribute is one called NAME. So to adjust your XML accordingly, it should be (not tested)
          select *
            FROM xmltable ('RESULTS/ROW'  -- changed to add in root node
                           passing XMLTYPE (bfilename ('XMLDIRX', 'E_table_export_CES.xml'), NLS_CHARSET_ID ('UTF8'))
                           columns
                           "ID" VARCHAR2(200) path 'COLUMN[@NAME="ID"]/@NAME'  -- updated
                           ,"BRANCH_NAME" varchar2(25) path 'COLUMN[@NAME="BRANCH_NAME"]/@NAME'  -- updated
                          ) xtbl
          • 2. Re: Issues reading XML file
            889187
            Ok, so I ran the updated query and it is not returning the data but just "ID" and "BRANCH_NAME" in the query row.

            So it returned:

            ID Branch_name
            -- ----------------
            ID Branch_name

            I'll try moving things around to see if I can get the data back but any more help would be appreciated.

            Thanks,
            Shawn
            • 3. Re: Issues reading XML file
              odie_63
              Hi,
              I'll try moving things around to see if I can get the data back but any more help would be appreciated.
              Don't try things at random, it's all very logic :)
              SQL> select x.*
                2  from xmltable(
                3         '/RESULTS/ROW'
                4         passing xmltype(bfilename('TEST_DIR', 'E_table_export_CES.xml'), nls_charset_id('AL32UTF8'))
                5         columns
                6           "ID"          varchar2(200) path 'COLUMN[@NAME="ID"]'
                7         , "BRANCH_NAME" varchar2(25)  path 'COLUMN[@NAME="BRANCH_NAME"]'
                8       ) x ;
               
              ID                                                                               BRANCH_NAME
              -------------------------------------------------------------------------------- -------------------------
              12688f8ac8aa6310VgnVCM10000078ccc70a____                                         Brooklyn
               
              The main XQuery extracts each ROW as a sequence, then each item of this sequence is passed to the COLUMNS clause to be broken as separate relational column(s), according to the specified PATH expression.
              For instance, the PATH 'COLUMN[@NAME="ID"]' means : extract the COLUMN child node (of ROW) whose NAME attribute value is 'ID', and since we specify a scalar datatype for the projection, the actual value - in this case the text() node - of the COLUMN element is returned.
              • 4. Re: Issues reading XML file
                Jason_(A_Non)
                My apologies for reading your question in a rush. Make sure you note the differences between the XPaths in my sample and what Odie provided above. I just went for the wrong piece of data was all as the XPath shows.

                As a tip, this is useful information for all the Oracle forums
                {message:id=9360002}
                • 5. Re: Issues reading XML file
                  889187
                  Ok, that worked Odie and thanks A Non!