How to extract string in xml field using Oracle sql

Question

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;

Boneist · Accepted Answer · 2015-03-04 15:14:07Z

1

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;

edited Mar 4, 2015 at 15:14

answered Mar 4, 2015 at 14:15

Boneist

23.6k1 gold badge28 silver badges42 bronze badges

Sign up to request clarification or add additional context in comments.

17 Comments

Marcus Over a year ago

Hello i didn't understand your solution what you are trying to do. Can you please tell me. I have a column Response which is CLOB data type and which consist of xml string and i just want to extract erroCode which is 224 from this xml_string. I dont know why did you use With clause ?

Boneist Over a year ago

The with clause (known as "Sub-query factoring" or sometimes (esp in other database platforms) common table expression (CTE)) is just there to mimic your table. I've renamed it to make its purpose clearer. If you haven't ever come across them, it's well worth looking into. In your case, you just need to focus on the main select query underneath. I've put the xmltype inside the sample_data subquery, you'd need to move it to the passing clause of the xmltable, but that shouldn't be too hard for you to figure out.

Marcus Over a year ago

Yes i just ran the select query and its giving me an error as ORA-00932: inconsistent datatypes: expected - got CLOB at line 4 in select query. When i ran the complete query with the With clause its running perfectly with correct result. But i only want to run select query indeed right ?

Boneist Over a year ago

In your original query you did passing XMLType(sm.RESPONSE). I did the conversion of the string to xmltype in the "table" (aka the sample_data sub-query). In your actual, real-life example, you would need to continue to do passing XMLType(sm.RESPONSE).

Marcus Over a year ago

Please check the question. I have changed the query and tried like this now but there is new error now : ORA-06502: PL/SQL: numeric or value error ORA-06512: at "SYS.XMLTYPE", line 272 ORA-06512: at line 1 06502. 00000 - "PL/SQL: numeric or value error%s"

|

Collectives™ on Stack Overflow

How to extract string in xml field using Oracle sql

1 Answer 1

17 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

17 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related