0

I have data XML format like this.

<"id=1">
   <"a">Test<"/a">
   <"b">Test2<"/b">
   <"c">Test3<"/c">

How to get value C in field XMLData?

*value C is Test3
2
  • 2
    Your data is not valid XML Commented Mar 7, 2018 at 7:53
  • What is a "XML blob type"? Commented Mar 7, 2018 at 7:54

1 Answer 1

1

How to get value C in field XMLData?

As mentioned by @Wernfried Domscheit your xml doesnot look correct. See below one way to do it:

create table traptabclob(testclob clob);
/

insert into traptabclob 
values
('<?xml version="1.0" encoding="UTF-8"?>
<DCResponse>
 <id>
   <Field key="a">Test</Field>
   <Field key="b">Test2</Field>
   <Field key="c">Test3</Field> 
  </id>                            
</DCResponse>');
/

Query:

SELECT 
EXTRACTVALUE(xmltype(testclob), '/DCResponse/id/Field[@key="c"]') col1
FROM traptabclob;

Output:

Col1
----
Test3

DEMO

Edit:

Thank you for your reply, my data doesn't has tag <?xml> version="1.0".... my data just like <row> id="1"><a>Test</a><b>Test2</b></row>, it is looks like xml type but i dont know this is xml type or not. Thank you

Please see that i have just given an example of valid xml file which contains usually <?xml> version="1.0"..... However issue with your xml code is putting tags in ". See below how it works:

create table traptabclob(testclob clob);
insert into traptabclob values('
 <id>
   <a>Test</a>
   <b>Test2</b>
   <c>Test3</c> 
  </id>');

Query:

SELECT EXTRACTVALUE(xmltype(testclob), '/id/c') col1 
FROM traptabclob ;

Output:

Col1
----
Test3

DEMO 1

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

4 Comments

Thank you for your reply, my data doesn't has tag <?xml version="1.0".... my data just like <row id="1"><a>Test</a><b>Test2</b></row>, it is looks like xml type but i dont know this is xml type or not. Thank you
i've tried SELECT EXTRACTVALUE(xmltype(testclob), '/ROW/c2') col1 FROM traptabclob in sqlfiddle, it's works but when i tried at sql developer it doesnt work
@aldiyudha Can you edit your question and put what you tried ?
thank you for your replied, it works but i didn't use xmltype function, because my data already xmltype

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.