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