niedziela, 17 lutego 2013

ORA-31013: Invalid XPATH expression - multiple namespaces

Preparing some sql query, which goal was searching into xml document using xPath and Oracle API, I came across a little problem due the multiple namespaces in xml source. I prepared the query:
SELECT id,
   TO_NUMBER( extract(xml,
   '/content:MESSAGE/data:CITY/data:POPULATION/text()')) pop
  FROM xml_table where id='E0437C858C6E5A22097EF739EC045';
It was not working correctly, oracle respond with the following message:
ORA-31013: Invalid XPATH expression
31013. 00000 -  "Invalid XPATH expression"
*Cause:    XPATH expression passed to the function is invalid.
*Action:   Check the xpath expression for possible syntax errors.
Error at Line: 3 Column: 7
Error message was little confusing for not experienced Oracle user because of xPath was correct. I've found out what was wrong. I just had to add a namespaces definitions as a second parameter to extract function.
   SELECT id,
   TO_NUMBER( extract(xml,
    '/content:MESSAGE/data:CITY/data:POPULATION/text()',
   'xmlns:content="http://www.gkolpu.blogspot.com/ContentNamespace 
   xmlns:data="http://www.gkolpu.blogspot.com/DataNamespace"')) pop
  FROM xml_table where id='E0437C858C6E5A22097EF739EC045';
  

Brak komentarzy:

Prześlij komentarz