Forum Stats

  • 3,872,214 Users
  • 2,266,407 Discussions
  • 7,911,106 Comments

Discussions

How to get the extracted values in sequence rather than concatenation of multiple ids. tried the bel

User_QDSE2
User_QDSE2 Member Posts: 9 Green Ribbon

How to get the extracted values in sequence rather than concatenation of multiple ids. tried the below approach but not working, receiving like "SYS.XMLSEQUENCETYPE(00000)"

select xmlsequence(extract(xmltype(message), 'aaa/bbb/ccc/@Id')) from xxxx;

Under bbb, there are multiple ccc so different ids

I should get value as

id1

id2

but not as id1id2

Answers

  • cormaco
    cormaco Member Posts: 2,035 Silver Crown

    XMLSEQUENCE and EXTRACT are deprecated functions, use XMLTABLE or XMLQUERY instead.

    Here is an example:

    with xxxx(message) as (select '<aaa><bbb><ccc id="id1"/><ccc id="id2"/></bbb></aaa>' from dual)
    select id
    from xxxx, xmltable(
        '/aaa/bbb/ccc' passing xmltype(message)
        columns
            id varchar2(10) path '@id'
    )
    
    ID        
    ----------
    id1
    id2