ExtractXML Oracle Query Returns Null

Question

I have the below data in a column called "attributes" of a table "data" on Oracle 12c database:

<Attributes>
  <Map>
    <entry key="accountFlags">
      <value>
        <List>
          <String>Normal User Account</String>
        </List>
      </value>
    </entry>
    <entry key="cn" value="paul.john"/>
    <entry key="department" value="IT"/>
    <entry key="description" value="New account. Automatically created"/>
    <entry key="displayName" value="John, Paul"/>
    <entry key="distinguishedName" value="CN=paul.john,OU=Users,DC=test,DC=com"/>
    <entry key="givenName" value="Paul"/>
    <entry key="homeMDB" value="CN=Test,CN=Databases,CN=Microsoft Exchange,CN=Services,CN=Configuration,DC=test,DC=com"/>
    <entry key="l" value="London"/>
    <entry key="mail" value="paul.john@test.com"/>
    <entry key="mailNickname" value="PaulJ"/>
    <entry key="manager" value="CN=brock.lesnar,OU=Users,DC=test,DC=com"/>
    <entry key="memberOf">
      <value>
        <List>
          <String>CN=Test1,OU=Rights,OU=Groups,DC=test,DC=com</String>
          <String>CN=Test2,OU=Rights,OU=Groups,DC=test,DC=com</String>
          <String>CN=Test3,OU=Rights,OU=Groups,DC=test,DC=com</String>
          <String>CN=Test4,OU=Rights,OU=Groups,DC=test,DC=com</String>
        </List>
      </value>
    </entry>
  </Map>
</Attributes>

I wish to extract the value of "memberOf" from this column like this:

MEMBER_OF
---------
CN=Test1,OU=Rights,OU=Groups,DC=test,DC=com
CN=Test2,OU=Rights,OU=Groups,DC=test,DC=com
CN=Test3,OU=Rights,OU=Groups,DC=test,DC=com
CN=Test4,OU=Rights,OU=Groups,DC=test,DC=com

I have tried this with the below query which is returning null:

SELECT EXTRACTVALUE(xmltype(attributes), '/Attributes/Map/entry[@key="memberOf"]/value/List/@String')
FROM DATA;

I have also tried the below query which also returns null

SELECT EXTRACTVALUE(xmltype(attributes), '/Attributes/Map/entry[@key="memberOf"]/value[1]/List/@String')
FROM DATA;

Not sure if there is something else that needs to be passed in the query?

Answer 1

Your path ends with .../@String, which is looking for an attribute called String, not a node. But if you fix that you'd get:

ORA-19025: EXTRACTVALUE returns value of only one node

ExtractValue() is long-deprecated anyway. You could use XMLQuery() instead, but that gives you a single XML fragment rather than separate strings:

select xmlquery('/Attributes/Map/entry[@key="memberOf"]/value/List/String'
  passing xmltype(attributes)
  returning content) as member_of
from data;

MEMBER_OF                                                                       
--------------------------------------------------------------------------------
<String>CN=Test1,OU=Rights,OU=Groups,DC=test,DC=com</String><String>CN=Test2,OU=
Rights,OU=Groups,DC=test,DC=com</String><String>CN=Test3,OU=Rights,OU=Groups,DC=
test,DC=com</String><String>CN=Test4,OU=Rights,OU=Groups,DC=test,DC=com</String>

Instead, use XMLTable() to get the individual values, as Bikash suggested - but it can be done more simply, without two XMLTable calls:

select x.member_of
from data
cross join XMLTable('/Attributes/Map/entry[@key="memberOf"]/value/List/String'
  passing XMLType(attributes)
  columns member_of varchar2(60) path '.'
) x;

MEMBER_OF                                                   
------------------------------------------------------------
CN=Test1,OU=Rights,OU=Groups,DC=test,DC=com
CN=Test2,OU=Rights,OU=Groups,DC=test,DC=com
CN=Test3,OU=Rights,OU=Groups,DC=test,DC=com
CN=Test4,OU=Rights,OU=Groups,DC=test,DC=com

Read more.

---

If you want to get other data in the same query, you can add those as more columns clauses, referring back up the tree - since the rest are single nodes, unless you have and need to show multiple account flags.

select x.*
from data
cross join XMLTable('/Attributes/Map/entry[@key="memberOf"]/value/List/String'
  passing XMLType(attributes)
  columns cn varchar2(20) path './../../../../entry[@key="cn"]/@value',
    department varchar2(10) path './../../../../entry[@key="department"]/@value',
    member_of varchar2(60) path '.'
) x;

CN                   DEPARTMENT MEMBER_OF                                                   
-------------------- ---------- ------------------------------------------------------------
paul.john            IT         CN=Test1,OU=Rights,OU=Groups,DC=test,DC=com                 
paul.john            IT         CN=Test2,OU=Rights,OU=Groups,DC=test,DC=com                 
paul.john            IT         CN=Test3,OU=Rights,OU=Groups,DC=test,DC=com                 
paul.john            IT         CN=Test4,OU=Rights,OU=Groups,DC=test,DC=com                 

although once you do that it might be easier to read and maintain if you do use multiple XMLTable calls:

select x1.cn, x1.department, x2.member_of
from data
cross join XMLTable('/Attributes/Map'
  passing XMLType(attributes)
  columns cn varchar2(20) path 'entry[@key="cn"]/@value',
    department varchar2(10) path 'entry[@key="department"]/@value',
    member_of_xml XMLType path 'entry[@key="memberOf"]'
) x1
cross join XMLTable('/entry/value/List/String'
  passing member_of_xml
  columns member_of varchar2(60) path '.'
) x2;

It's then simple to add a third XMLTable call to handle multiple account flags.

You could also do all this manipulation in a single XMLTable with a more complicated XPath with loops etc., but I think this is clearer and simpler, and unless you're dealing with large XML documents (and a lot of them) there may not be a significant performance difference.

Answer 2

Try this

SELECT y.*
  FROM (SELECT *
          FROM XMLTABLE ('/Attributes/Map/entry[@key="memberOf"]/value/List'
                         PASSING (SELECT xmltype (attributes)
                                    FROM data)
                         COLUMNS strings XMLTYPE PATH 'String')) x,
       XMLTABLE ('/String'
                 PASSING x.strings
                 COLUMNS memberOf VARCHAR (300) PATH '.') y;