I'm new to oracle,i have XML column in table,in that column data is in XML format
i.e,
<?xml version="1.0" encoding="UTF-8"?>
<charge textid="dacebab6-962e-3-b23d70eef85a" id="parse"><properties/></charge>
Now i want get only textid, so how to get textid thorough SQL query.
i tried select XMLSequence(Extract(x,'/charge/textid/text()')) from emp_datails; but not working.
Use EXTRACTVALUE.
select EXTRACTVALUE(x,'/charge/@textid') from emp_datails;
Example:
WITH x(y) AS
(SELECT xmltype('<?xml version="1.0" encoding="UTF-8"?>
<charge textid="dacebab6-962e-3-b23d70eef85a" id="parse">asdd<properties/>asd</charge>')
FROM dual
)
SELECT EXTRACTVALUE(y, '/charge/@textid') textid FROM x;
TEXTID
----------
dacebab6-962e-3-b23d70eef85a
with this statement you can extract the textid:
-- Your data
with data as
(select '<?xml version="1.0" encoding="UTF-8"?>
<charge textid="dacebab6-962e-3-b23d70eef85a" id="parse"><properties/></charge>' as xmlval
from dual)
(SELECT attr_value
FROM data d,
xmltable('.' PASSING xmltype(d.xmlval) COLUMNS attr_value
VARCHAR2(30) PATH '/charge/@textid'));
with data as (select 'hello' from dual) (select * from data) to understand it. In this case it's only a statement to prepare test data, replace it by your table.with data as (select '<?xml version="1.0" encoding="UTF-8"?> <charge textid="dacebab6-962e-3-b23d70eef85a" id="parse"><properties/></charge>' as xmlval from dual) (SELECT attr_value FROM data d, xmltable('.' PASSING xmltype(d.xmlval) COLUMNS attr_value VARCHAR2(30) PATH '/charge/@textid'));