CODEWITHSUNDEEP
sqlxmloracle-database
5hak3y
5hak3y

Reputation: 3

ORACLE Parsing XML string into separate records

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

Upvotes: 0

Views: 3511

Answers (2)

A.B.Cade
A.B.Cade

Reputation: 16905

@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

Upvotes: 0

DazzaL
DazzaL

Reputation: 21973

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

Upvotes: 1

Related Questions