asked on 7/18/2008

XML parsing

Hi

I am trying to parse the data from XMLTYPE value.
Below is the xml file which I have loaded.

Now I am trying to select data from the XML type using below query but I am getting error.
Can you please help.

SQL> SELECT a.xml.extract('//xdp:xdp/xfa:datasets/xfa:data/Email2Inv/text()').ge
tStringVal() AS "Table Name"
2 FROM xml_tab a
3 WHERE a.xml.existsNode('/xdp:xdp') = 1;
SELECT a.xml.extract('//xdp:xdp/xfa:datasets/xfa:data/Email2Inv/text()').getStri
ngVal() AS "Table Name"
*
ERROR at line 1:
ORA-31011: XML parsing failed
ORA-19202: Error occurred in XML processing
LPX-00601: Invalid token in: '/xdp:xdp'

Thanks

<?xml version="1.0" encoding="UTF-8" ?> 
  <?xfa generator="XFA2_4" APIVersion="2.6.7120.0"?> 
- <xdp:xdp xmlns:xdp="http://ns.adobe.com/xdp/">
- <xfa:datasets xmlns:xfa="http://www.xfa.org/schema/xfa-data/1.0/">
- <xfa:data>
- <FORM>
  <FORM_VERSION>IMVA4_V2</FORM_VERSION> 
  <PI_ADDR1>14547 Downs Blvd.</PI_ADDR1> 
  <Email2Inv>dhoom@gmail.com</Email2Inv> 
- <HEADER>
  <PI_ADDR1>14547 Bruce B. Downs Blvd.</PI_ADDR1> 
  <Email2Inv>heubanks@gmail.com</Email2Inv> 
  </HEADER>
  </FORM>
  </xfa:data>
  </xfa:datasets>
  <pdf href="../../../../../../../../DOCUME~1/jan/LOCALS~1/Temp/notes0BDDB2/~6686154.pdf" xmlns="http://ns.adobe.com/xdp/pdf/" /> 
- <xfdf xmlns="http://ns.adobe.com/xfdf/" xml:space="preserve">
  <annots /> 
  </xfdf>
  </xdp:xdp>

Last Comment

7/28/2008

7/18/2008

ASKER

I have load the XML file into the table using below procedure.

Thanks

CREATE TABLE xml_tab (
  id        NUMBER(10),
  filename  VARCHAR2(100),
  xml       XMLTYPE
)
/
 
-------------------------------------------------------------------
ALTER TABLE xml_tab ADD (
  CONSTRAINT xml_tab_pk PRIMARY KEY (id)
)
/
 
--------------------------------------------------------------------
CREATE SEQUENCE xml_tab_seq;
 
CREATE OR REPLACE PROCEDURE load_xml (p_dir       IN  VARCHAR2,
                                      p_filename  IN  VARCHAR2) AS
  l_bfile  BFILE := BFILENAME(p_dir, p_filename);
  l_clob   CLOB;
BEGIN
  DBMS_LOB.createtemporary (l_clob, TRUE);
  
  DBMS_LOB.fileopen(l_bfile, DBMS_LOB.file_readonly);
  DBMS_LOB.loadfromfile(l_clob, l_bfile, DBMS_LOB.getlength(l_bfile));
  DBMS_LOB.fileclose(l_bfile);
 
  INSERT INTO xml_tab (
    id,
    filename,
    xml
  )
  VALUES (
    xml_tab_seq.NEXTVAL,
    p_filename,
    XMLTYPE.createXML(l_clob)
  );
  COMMIT;
  
  DBMS_LOB.freetemporary (l_clob);
END;
/
 
EXEC load_xml(p_dir => 'LOB_TEST_DIR', p_filename => 'sample.xml');
 
----------------------------------------------------------------------
 
SELECT a.xml.extract('//xdp:xdp/xfa:datasets/xfa:data/Email2Inv/text()').getStringVal() AS "Table Name"
FROM   xml_tab a
where filename = 'sample.xml'
 
----------------------------------------------------------------------
 
SQL> SELECT a.xml.extract('//xdp:xdp/xfa:datasets/xfa:data/Email2Inv/text()').ge
tStringVal() AS "Table Name"
  2  FROM   xml_tab a
SELECT a.xml.extract('//xdp:xdp/xfa:datasets/xfa:data/Email2Inv/text()').getStri
ngVal() AS "Table Name"
       *
ERROR at line 1:
ORA-31011: XML parsing failed
ORA-19202: Error occurred in XML processing
LPX-00601: Invalid token in: '//xdp:xdp/xfa:datasets/xfa:data/Email2Inv/text()'
ORA-06512: at "SYS.XMLTYPE", line 111