ORACLE Parsing XML string into separate records

Question

I'm trying to find members of several groups. The group membership is stored as XML in an ORACLE database. I need a SQL query to split each member into a separate rows.

I've seen a few posts on using xmlsequence without success through lack of understanding.

A sample string is

<D:href xmlns:D='DAV:'>/users/admin@Native Directory</D:href>
<D:href xmlns:D='DAV:'>/users/oracle@Native Directory</D:href>
<D:href xmlns:D='DAV:'>/users/user1@DomainProd</D:href>

My current results are

Group       User
-------------------------------------------------------------------------
group1      <D:href xmlns:D='DAV:'>/users/admin@Native Directory</D:href>
            <D:href xmlns:D='DAV:'>/users/oracle@Native Directory</D:href>
            <D:href xmlns:D='DAV:'>/users/user1@DomainProd</D:href>
group2      <D:href xmlns:D='DAV:'>/users/admin@Native Directory</D:href>
            <D:href xmlns:D='DAV:'>/users/oracle@Native Directory</D:href>
            <D:href xmlns:D='DAV:'>/users/user1@DomainProd</D:href>
group3      <D:href xmlns:D='DAV:'>/users/admin@Native Directory</D:href>
            <D:href xmlns:D='DAV:'>/users/oracle@Native Directory</D:href>
            <D:href xmlns:D='DAV:'>/users/user1@DomainProd</D:href>

I want my results set to be

Group       User
-------------------
group1      admin
group1      oracle
group1      user1
group2      admin
group2      oracle
group2      user1
group3      admin
group3      oracle
group3      user1

Any help would be appreciated...

Thanks

Duplicate of: stackoverflow.com/questions/4715696/… ?

Wolfgang
– Wolfgang

2013-01-19 16:52:17 +00:00
Commented Jan 19, 2013 at 16:52 — Wolfgang
– Wolfgang, Commented Jan 19, 2013 at 16:52

DazzaL · Accepted Answer · 2013-01-19 19:00:34Z

1

you can use XMLTABLE. as your XML document seems to be a fragment in the row, i've wrapped this in a <root> element.

select grp, substr(name, 
              instr(name, '/', -1) + 1,
              instr(name, '@') - instr(name, '/', -1) - 1
             ) name
  from mytab m, 
       xmltable(xmlnamespaces('DAV:' as "D"), 
                '/root/D:href' passing xmltype('<root>'||usr||'</root>')
                columns
                name varchar2(200) path './text()');

i've assumed a table where your xml column is stored as a clob/varchar2 called (usr) .

example output for group1:

SQL> select grp, substr(name,
  2                instr(name, '/', -1) + 1,
  3                instr(name, '@') - instr(name, '/', -1) - 1
  4               ) name
  5    from mytab m,
  6         xmltable(xmlnamespaces('DAV:' as "D"),
  7                  '/root/D:href' passing xmltype('<root>'||usr||'</root>')
  8                  COLUMNS
  9                  name VARCHAR2(200) path './text()');

GRP    NAME
------ ----------
group1 admin
group1 oracle
group1 user1

http://sqlfiddle.com/#!4/435cd/1

edited Jan 19, 2013 at 19:00

answered Jan 19, 2013 at 16:54

DazzaL

22.1k3 gold badges52 silver badges57 bronze badges

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

2 Comments

5hak3y Over a year ago

Thanks @DazzaL. It's working on sqlfiddle but not on the server. I'm getting ORA-19102: XQuery string literal expected. The query i'm using it

select X.name   from PROPERTIES p,     xmltable(xmlnamespaces('DAV:' as "D"),     '/root/D:href' passing xmltype('<root>'||p.PROPERTY_VALUE||'</root>')                 COLUMNS                 name CLOB PATH './text()') X'

DazzaL Over a year ago

@5hak3y this may be because you have CURSOR_SHARING set to FORCE or SIMILAR. can you confirm this? a simple way of seeing is add this hint in select /*+ cursor_sharing_exact */ grp

Community · Accepted Answer · 2017-05-23 12:03:27Z

0

@DazzaL's answer is a good one (and probably faster in most cases),
but you can also do something like this:

with tt as 
(select ggroup, regexp_replace(uuser, '([[:print:]]*)(/users/)([[:alnum:]]*)(@)([[:print:]]*)','\3,') user_csv
from t)
select distinct ggroup, regexp_substr(user_csv,'[^,]+',1,level)
from tt
connect by regexp_substr(user_csv,'[^,]+',1,level) is not null
order by ggroup

t in my query is the table's name

Here is a sqlfiddle demo

edited May 23, 2017 at 12:03

CommunityBot

11 silver badge

answered Jan 19, 2013 at 20:29

A.B.Cade

16.9k1 gold badge39 silver badges55 bronze badges

Collectives™ on Stack Overflow

ORACLE Parsing XML string into separate records

2 Answers 2

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related