This discussion is archived
5 Replies Latest reply: Feb 20, 2013 3:07 PM by 889187 RSS

Issues reading XML file

889187 Newbie
Currently Being Moderated
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) Expert
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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) Expert
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    Ok, that worked Odie and thanks A Non!

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points