Reading data out from XML document inserted into table

Posted on 2009-02-20
Last Modified: 2013-12-18
I successfully loaded and XML document into an Oracle table now I want to query the table to extract data.

If I use the following query I don't see anything returned,

select extractValue(xmltype(XMLDOC),'/digital/state/city@mayor_name=SMITH]') from xml_documents;

If I use extract instead of extractValue I get,


I believe this is just a problem reading a text string so I tried using an example I saw on this site using


but so far have been unsuccessful.




 url    VARCHAR2(100);


 select XMLDOC into demo_block.XMLDOC  

 from xml_documents

 where extract(xmltype(XMLDOC),'/digital/state/city[@mayor_name=SMITH]') is not null;

 url := extract('/digital/state/city[@mayor_name=SMITH]').getStringVal;



Open in new window

Question by:talahi

    Author Comment

    extractValue(xmltype(XMLDOC),'/digital/state/city[@mayor_name]' Mayor_Last_Name
     from sys.xml_documents
     where XMLDOC is not null
     and mayor_name = 'SMITH';

    I've tried variations of the above query but run into the following error in the WHERE clause,

    ORA-00904: "mayor_name": invalid identifier
    LVL 73

    Expert Comment

    what are your trying to extract?  the name of city with a mayor named SMITH?

    Try the select below...  (don't include the WITH clause, that's simpy for demo purposes since I don't have your table or data)

    If that's not what you want, please replicate my example with a more complete set of data and explain what you would like returned.
    WITH xml_documents
            AS (SELECT   xmltype(
            <name>North Somewhere</name>
            <city mayor_name="SMITH">Somewhere City</city>
                  FROM   DUAL)
    SELECT   EXTRACTVALUE(xmldoc, '/digital/state/city[@mayor_name="SMITH"]')
      FROM   xml_documents

    Open in new window


    Author Comment

    My table description is,

    CREATE TABLE xml_documents (
       xmldoc     CLOB,

    I run the following but can't see the data.  Displays as (ORAXML).  I believe I need some sort of getStringVal implementation to read the data.

    select extract(xmltype(XMLDOC),'/digital/state/city[@mayor_name="SMITH"]') from xml_documents;

    If I run it with extractValue instead of extract I see nothing.
    LVL 73

    Accepted Solution

    I understand what you've been trying is not working but I don't know what your goal is.

    what data is it you are trying to see?

    my with clause above simulates your table, except we can both see the data, as can others that may want to help.  It's a self-contained test case.

    please change the xml with some more representative data or post your real xml file

    and post what you want to happen, not code, but results. what data do you want given sample data (either in the form of a file or put inside a with clause above.  

    The WITH way is easier for everybody trying to contribute.  And when the solution is found, you simply drop the with and run the select "as is"


    Author Comment

    If I load your data sample above into my table and run your query I get the correct returned value.  If I load a simple version of my data into the same table, and run the same type query,  I don't get a return.  At this point I'm sure its something simple that I'm not seeing but will continue to play with.  For A to Z this  is everything I'm working with.


    Table Created

     XMLDOC     CLOB

    Test data saved to file named xml_test_data.txt saved at c:\

       <state_abbrev ID="AK" state_fullname="Alaska">

    Procedure to load data into table.

                                              file VARCHAR2,
                                              name VARCHAR2 := NULL) IS
      theBFile   BFILE;
      theCLob    CLOB;
      theDocName VARCHAR2(200) := NVL(name,file);
      -- (1) Insert a new row into xml_documents with an empty CLOB, and
      -- (2) Retrieve the empty CLOB into a variable with RETURNING..INTO

      INSERT INTO xml_documents(xmldoc) VALUES(empty_clob(  ))
      RETURNING xmldoc INTO theCLob;

      -- (3) Get a BFile handle to the external file
      theBFile := BFileName(dir,file);

      -- (4) Open the file

      -- (5) Copy the contents of the BFile into the empty CLOB
      dbms_lob.loadFromFile(dest_lob => theCLob,
                             src_lob => theBFile,
                             amount  => dbms_lob.getLength(theBFile));

      -- (6) Close the file and commit

    execution statement to run procedure to load data into table.

    EXEC insertXmlFile('C_PATH','xml_test_data.txt');


    WITH xml_documents
            AS (SELECT   xmltype(
    '<digital >
       <state_abbrev ID="AK" state_fullname="Alaska">
                  FROM   DUAL)
    select extractvalue((XMLDOC),'/digital/state_abbrev[@state_fullname="Alaska"]')  
    from xml_documents;


    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Looking for New Ways to Advertise?

    Engage with tech pros in our community with native advertising, as a Vendor Expert, and more.

    Working with Network Access Control Lists in Oracle 11g (part 2) Part 1: Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many…
    This post first appeared at Oracleinaction  ( Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
    Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.
    This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

    761 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    14 Experts available now in Live!

    Get 1:1 Help Now