CODEWITHSUNDEEP
sqlxmloracle-database
Sri
Sri

Reputation: 37

oracle sql: how to read XML and how to get value

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.

Upvotes: 0

Views: 9445

Answers (2)

Noel
Noel

Reputation: 10525

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

Upvotes: 1

Frank Ockenfuss
Frank Ockenfuss

Reputation: 2043

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'));

Upvotes: 0

Related Questions