CODEWITHSUNDEEP
sqlxmloracle-databaseparsingxquery-sql
Marcus
Marcus

Reputation: 3869

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   .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://www.w3.org/2001/XMLSchema-instance" 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>
</Order>

Here is the query i tried:

 select x.*
from   TEMP_PROVISIONING_LOG PL
       CROSS JOIN XMLTable(XMLNAMESPACES (
      'http://core.signup.data.soap.CDRator.com/xsd' as "Header"),
                            'for $i in //Order return $i'
                            passing XMLType(PL.RESPONSE)
                            columns error_code varchar2(100) path 'Header/@errorCode') x;

Upvotes: 0

Views: 2513

Answers (1)

Boneist
Boneist

Reputation: 23588

I think you're after this:

with sample_data as (select xmltype('<?xml version="1.0" encoding="ISO-8859-15"?>
<Order xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" 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>
</Order>') response from dual)
select x.*
from   sample_data tpl
       cross join xmltable (XMLNAMESPACES ('http://www.w3.org/2001/XMLSchema-instance' as "Header"),
                            '/Order' passing tpl.response
                            columns error_code varchar2(100) path 'Header/@errorCode') x;

ERROR_CODE                                                                      
--------------------------------------------------------------------------------
224

ETA: Try this instead:

with TEMP_PROVISIONING_LOG as (select '<?xml version="1.0" encoding="ISO-8859-15"?>
<Order xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" 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>
</Order>' response from dual)
select *
from   TEMP_PROVISIONING_LOG PL,
       XMLTable(XMLNAMESPACES (
      'http://core.signup.data.soap.CDRator.com/xsd' as "Header"),
                            'for $i in //Order return $i'
                            passing XMLType.createxml(PL.RESPONSE)
                            columns error_code varchar2(100) path 'Header/@errorCode') x;

Upvotes: 1

Related Questions