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-04T15:14:07.457

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 04 '15 at 15:14

answered Mar 04 '15 at 14:15

Boneist

22,910
1
25
40

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 ? – Andrew Mar 04 '15 at 14:24
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. – Boneist Mar 04 '15 at 14:30
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 ? – Andrew Mar 04 '15 at 14:34
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)`. – Boneist Mar 04 '15 at 14:38
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" – Andrew Mar 04 '15 at 14:40
I am doing small mistake somewhere i tried such queries before and it works fine but dont know for this query and xml why there us error – Andrew Mar 04 '15 at 14:43
I'm not getting an error with your updated query, using the example xml you provided. – Boneist Mar 04 '15 at 14:47
*Cause: An arithmetic, numeric, string, conversion, or constraint error occurred. For example, this error occurs if an attempt is made to assign the value NULL to a variable declared NOT NULL, or if an attempt is made to assign an integer larger than 99 to a variable declared NUMBER(2). *Action: Change the data, how it is manipulated, or how it is declared so – Andrew Mar 04 '15 at 14:49
I think this query runs perfectly for the above xml but i have Response field in table which contains many values and some of the xml string are not proper or big and its giving me error in that case. Can you please tell me the cause of this error which i have mentioned before this message – Andrew Mar 04 '15 at 14:51
Some of the error codes are null and some are not i want to extract both the null and not null error code. I think i have to handled in my query in that way right ? – Andrew Mar 04 '15 at 14:53
which error? The syntax error you had originally? Or the ORA-06502? If the latter, then maybe the column datatypes in the xmltable expression aren't wide enough to hold the data you're trying to put in them? – Boneist Mar 04 '15 at 14:53
re. the null/not null error codes... it sounds like you're talking about specific xml data. Since we don't have your data, you should update your question to provide enough sample data that adequately demonstrates the issues you're seeing. – Boneist Mar 04 '15 at 14:55
Its a complete error i am getting and i have divided into 2 message. Can you please tell me the solution behind this error. I am really unknown because of such issue – Andrew Mar 04 '15 at 14:56
a quick google suggests: http://stackoverflow.com/questions/15139329/extract-specfic-value-from-clob-containing-xml-while-creating-one-delimited-st – Boneist Mar 04 '15 at 15:01
no the RESPONSE_XML is clob data type its not a number. When i just run your select query it gives me error as ORA-00932: inconsistent datatypes: expected - got CLOB 00932. 00000 - "inconsistent datatypes: expected %s got %s" – Andrew Mar 04 '15 at 15:07
Should i changed the clob data type ? The select query has an issue i guess. If you store this xml string in clob data type field in your table. And run your select query it gives an error as inconsistent datatype clob – Andrew Mar 04 '15 at 15:11
I have updated my original answer, since you don't seem to be able to follow the directions I chatted earlier. – Boneist Mar 04 '15 at 15:14

How to extract string in xml field using Oracle sql

1 Answers1