How to extract string in xml field using Oracle sql

I want to extract the error code from the xml string which is CLOB data type in my oracle table. But when i tried to extract it gives me an error as


ORA-19114: XPST0003 - error during parsing the XQuery expression: 
LPX-00801: XQuery syntax error at 'return'
1   http://.w3.org/2001/XMLSchema-instance";for $i in //<Header errorCode= return $i

Here is my xml string which is stored in the RESPONSE column of PROVISIONING_LOG table:


<?xml version="1.0" encoding="ISO-8859-15"?>
<Order xmlns:xsi="http://ift.tt/ra1lAU" xsi:noNamespaceSchemaLocation="sp.xsd">
  <Header dateOfExecution="2014-10-01 00:03:06" externalId1="" messageId="1" orderId="0021417905" serviceProviderId="SP605" status="V" type="RES" errorCode="224" errorString="Subscription 201407162076606 not found." />
  <Body>
    <Oli>
      <OliControl oliId="1" subscriptionId="201407162076606" errorCode="224" errorString="Subscription 201407162076606 not found." status="V" />
      <DEASUB />
    </Oli>
  </Body>

Here is the query i tried:


SELECT xt_req.ERROR_CODE
FROM TEMP_PROVISIONING_LOG sm
CROSS JOIN XMLTable(XMLNAMESPACES (
      'http://ift.tt/ra1lAU' as "Header"
    ),
    'for $i in //<Header errorCode= return $i'
    passing XMLType(sm.RESPONSE)
    columns "ERROR_CODE" VARCHAR2(100) path '/') xt_req;
</Order>

How to extract string in xml field using Oracle sql

No comments:

Post a Comment