[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1352
  • Last Modified:

Reading data out from XML document inserted into table

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,

(OraXML)

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

getStringVal

but so far have been unsuccessful.

<<demo_block>>
declare
 XMLDOC CLOB;
 url    VARCHAR2(100);
BEGIN
 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;
 DBMS_OUTPUT.PUT_LINE(url);
END;

Open in new window

0
talahi
Asked:
talahi
  • 3
  • 2
1 Solution
 
talahiAuthor Commented:
select
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
0
 
sdstuberCommented:
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(
'<digital>
    <state>
        <name>North Somewhere</name>
        <city mayor_name="SMITH">Somewhere City</city>
    </state>
</digital>
')
                         xmldoc
              FROM   DUAL)
SELECT   EXTRACTVALUE(xmldoc, '/digital/state/city[@mayor_name="SMITH"]')
  FROM   xml_documents

Open in new window

0
 
talahiAuthor Commented:
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.
0
 
sdstuberCommented:
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"

0
 
talahiAuthor Commented:
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

CREATE TABLE XML_DOCUMENTS
(
 XMLDOC     CLOB
);

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

<digital>
   <state_abbrev ID="AK" state_fullname="Alaska">
   </state_abbrev>
</digital_tpp>


----------------------------------
Procedure to load data into table.

CREATE OR REPLACE PROCEDURE insertXmlFile( dir VARCHAR2,
                                          file VARCHAR2,
                                          name VARCHAR2 := NULL) IS
  theBFile   BFILE;
  theCLob    CLOB;
  theDocName VARCHAR2(200) := NVL(name,file);
BEGIN
  -- (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
  dbms_lob.fileOpen(theBFile);

  -- (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
  dbms_lob.fileClose(theBFile);
  COMMIT;
END;

-------------------------------------------------------
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">
   </state_abbrev>
</digital>
')
                         xmldoc
              FROM   DUAL)
select extractvalue((XMLDOC),'/digital/state_abbrev[@state_fullname="Alaska"]')  
from xml_documents;



0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now