Database Software

Hi gurus,
I need your help on using the XMLQuery function. We are FINALLY in the processing of migrating from 10g to 11gR2! Oracle says that we should replace extracValue with XMLQuery, so I am trying to do that but I am getting the errors below,

XVM-01003: [XPST0003] Syntax error at (if I use a PL/SQL variable).
LPX-00801: XQuery syntax error at (if I use the literals).

I have read through many of the posts related to XMLQuery and its default element namespace, but I have no luck on that. I have an example that has this kind prefix (a part of the xml is posted here):

<soap:Body>
<soap:Fault xmlns:soap="http://www.w3.org/2003/05/soap-envelope">
<soap:Code>
<soap:Value>soap:Sender</soap:Value>
<soap:Subcode>
<soap:Value>soap:InvalidMessage</soap:Value>
</soap:Subcode>
</soap:Code>
<soap:Reason>
<soap:Text xml:lang="en">UpdateCaseDetentionStatus does not apply to this case&apos;s type.</soap:Text>
</soap:Reason>
<soap:Node>CourtFileNumber</soap:Node>

I need to get the Text inside the Reason node. No matter what I do with the default namespace (and if I don't declare one I also get an error), I get the following error:

XMLQuery ('declare default element namespace s="http://www.w3.org/2003/05/soap-envelope" ; /s:Envelope/s:Body/s:Fault/s:Reason/s:Text'):

LPX-00801: XQuery syntax error at 's'
1 declare default element namespace s="http://www.w3.org/2003/05/soap-envelop
- ^
ORA-06512: at line 103


XMLQuery('declare default element namespace xmlns:soap="http://www.w3.org/2003/05/soap-envelope" ; /soap:Envelope/soap:Body/soap:Fault/soap:Reason/soap:Text'):

LPX-00801: XQuery syntax error at 'xmlns:soap'
1 declare default element namespace xmlns:soap="http://www.w3.org/2003/05/soa
- ^
ORA-06512: at line 103

Using the XMLTable function does not have any problems.

What do I do wrong here? I could just use the XMLTable to replace all the extractValue(s) that I have, but I really want to learn how to make the XMLQuery correct. Please help!

I just tried this and I got the same error:

XMLQuery('declare default namespace s="http://www.w3.org/2003/05/soap-envelope" ; (::) $p/s:Envelope/s:Body/s:Fault/s:Reason/s:Text' passing p_XMLDoc as "p"

Thank you.

Ben

Edited by: myora9i on Apr 22, 2011 1:42 PM