How to retrieve a value from key value pair in XML via SQL Query?

Question

I have a column with name attributes in my database table which is a clob object storing XML data as below. Using SQL, I am trying to retrieve the values of key accountExpires which should give me the value of 123456789L as output.

I tried using nodes(Xquery) and CROSS APPLY etc but I am only fetching those which are typically in Value format like the IIQDisabled or accountFlags etc but I want to retrieve entry key and value pairs. Appreciate your help.

<Attributes>
  <Map>
    <entry key="Division" value="TRAINING"/>
    <entry key="IIQDisabled">
      <value>
        <Boolean>true</Boolean>
      </value>
    </entry>
    <entry key="accountExpires" value="123456789L"/>
    <entry key="accountFlags">
      <value>
        <List>
          <String>Normal User Account</String>
          <String>User Account is Disabled</String>
        </List>
      </value>
    </entry>
    <entry key="department" value="LOYALTY CLUB"/>
    <entry key="distinguishedName" value="CN=Account02\,TM_Test02,OU=SailpointQA,OU=Users...."/>
    <entry key="employeeID" value="333223"/>
    <entry key="givenName" value="TM_Test02"/>
    <entry key="memberOf"/>
    <entry key="mobile" value="9"/>
    <entry key="sAMAccountName" value="TM_Test02.Account02"/>
    <entry key="sAMAccountType" value="805306368"/>
    <entry key="sn" value="Account02"/>
    <entry key="userAccountControl" value="514"/>
  </Map>
</Attributes>


select a.id as id
,pref.value('(@accountExpires)[1]', 'varchar(50)') as accountExpires
,pref.value('.', 'varchar(50)') as test
FROM (
select  
         id,CONVERT(XML, attributes, 1) xmlCol 
from [identityiq].[identityiq].[spt_work_item_archive]) a 
CROSS APPLY xmlCol.nodes('//Attributes/Map') AS ApprovalItem(pref)

Expected results :

id       accountExpires
-----------------
someid   123456789L

But actually i am getting true Normal User Account User Account is Disabled if use pref.value('.', 'varchar(50)') in the output as test column.

Answer 1

From your own code I take this is SQL-Server. At least the syntax looks like this.

You can try this:

DECLARE @xml XML=
N'<Attributes>
  <Map>
    <entry key="Division" value="TRAINING"/>
    <entry key="IIQDisabled">
      <value>
        <Boolean>true</Boolean>
      </value>
    </entry>
    <entry key="accountExpires" value="123456789L"/>
    <entry key="accountFlags">
      <value>
        <List>
          <String>Normal User Account</String>
          <String>User Account is Disabled</String>
        </List>
      </value>
    </entry>
    <entry key="department" value="LOYALTY CLUB"/>
    <entry key="distinguishedName" value="CN=Account02\,TM_Test02,OU=SailpointQA,OU=Users...."/>
    <entry key="employeeID" value="333223"/>
    <entry key="givenName" value="TM_Test02"/>
    <entry key="memberOf"/>
    <entry key="mobile" value="9"/>
    <entry key="sAMAccountName" value="TM_Test02.Account02"/>
    <entry key="sAMAccountType" value="805306368"/>
    <entry key="sn" value="Account02"/>
    <entry key="userAccountControl" value="514"/>
  </Map>
</Attributes>';

--The query

SELECT entr.value('@key','nvarchar(100)') AS AttrKey
      ,entr.value('@value','nvarchar(500)') AS AttrValue
      ,HasValueElement.value('local-name(.)','nvarchar(100)') AS ValueType
      ,HasValueElement.value('text()[1]','nvarchar(500)') AS ValueTypeValue
      ,IsAList.value('local-name(.)','nvarchar(100)') AS ListValueType
      ,IsAList.value('text()[1]','nvarchar(500)') AS ListValueValue

FROM @xml.nodes(N'/Attributes/Map/entry') A(entr)
OUTER APPLY A.entr.nodes(N'value/*') B(HasValueElement)
OUTER APPLY B.HasValueElement.nodes('*') C(IsAList);

the result

+--------------------+-----------------------------------------------------+---------+------+--------+--------------------------+
| Division           | TRAINING                                            |         | NULL |        | NULL                     |
+--------------------+-----------------------------------------------------+---------+------+--------+--------------------------+
| IIQDisabled        | NULL                                                | Boolean | true |        | NULL                     |
+--------------------+-----------------------------------------------------+---------+------+--------+--------------------------+
| accountExpires     | 123456789L                                          |         | NULL |        | NULL                     |
+--------------------+-----------------------------------------------------+---------+------+--------+--------------------------+
| accountFlags       | NULL                                                | List    | NULL | String | Normal User Account      |
+--------------------+-----------------------------------------------------+---------+------+--------+--------------------------+
| accountFlags       | NULL                                                | List    | NULL | String | User Account is Disabled |
+--------------------+-----------------------------------------------------+---------+------+--------+--------------------------+
| department         | LOYALTY CLUB                                        |         | NULL |        | NULL                     |
+--------------------+-----------------------------------------------------+---------+------+--------+--------------------------+
| distinguishedName  | CN=Account02\,TM_Test02,OU=SailpointQA,OU=Users.... |         | NULL |        | NULL                     |
+--------------------+-----------------------------------------------------+---------+------+--------+--------------------------+
| employeeID         | 333223                                              |         | NULL |        | NULL                     |
+--------------------+-----------------------------------------------------+---------+------+--------+--------------------------+
| givenName          | TM_Test02                                           |         | NULL |        | NULL                     |
+--------------------+-----------------------------------------------------+---------+------+--------+--------------------------+
| memberOf           | NULL                                                |         | NULL |        | NULL                     |
+--------------------+-----------------------------------------------------+---------+------+--------+--------------------------+
| mobile             | 9                                                   |         | NULL |        | NULL                     |
+--------------------+-----------------------------------------------------+---------+------+--------+--------------------------+
| sAMAccountName     | TM_Test02.Account02                                 |         | NULL |        | NULL                     |
+--------------------+-----------------------------------------------------+---------+------+--------+--------------------------+
| sAMAccountType     | 805306368                                           |         | NULL |        | NULL                     |
+--------------------+-----------------------------------------------------+---------+------+--------+--------------------------+
| sn                 | Account02                                           |         | NULL |        | NULL                     |
+--------------------+-----------------------------------------------------+---------+------+--------+--------------------------+
| userAccountControl | 514                                                 |         | NULL |        | NULL                     |
+--------------------+-----------------------------------------------------+---------+------+--------+--------------------------+

Some explanation:

There are three kinds of data:

1. Simple key-value-pairs
2. Typed key-value-pairs
3. Typed list values

The query will use .nodes() to dive into all <entry> elements and return them as a derived table. The first OUTER APPLY will return addtional rows/columns in cases, where there is a <value> element below a given <entry>. This element might have a value (like the boolean "true"), or it might contain a typed list. The second OUTER APPLY dives - if this exists - into the sub-nodes of <value> and returns them as additional rows.

A Query like this would return it more in an EAV-style

SELECT entr.value('@key','nvarchar(100)') AS AttrKey
      ,COALESCE(entr.value('@value','nvarchar(500)'),HasValueElement.value('text()[1]','nvarchar(500)'),IsAList.value('text()[1]','nvarchar(500)')) AS AttrValue
      ,HasValueElement.value('local-name(.)','nvarchar(100)') AS ValueType
      ,IsAList.value('local-name(.)','nvarchar(100)') AS ListValueType

FROM @xml.nodes(N'/Attributes/Map/entry') A(entr)
OUTER APPLY A.entr.nodes(N'value/*') B(HasValueElement)
OUTER APPLY B.HasValueElement.nodes('*') C(IsAList);

The result

+--------------------+-----------------------------------------------------+-----------+---------------+
| AttrKey            | AttrValue                                           | ValueType | ListValueType |
+--------------------+-----------------------------------------------------+-----------+---------------+
| Division           | TRAINING                                            |           |               |
+--------------------+-----------------------------------------------------+-----------+---------------+
| IIQDisabled        | true                                                | Boolean   |               |
+--------------------+-----------------------------------------------------+-----------+---------------+
| accountExpires     | 123456789L                                          |           |               |
+--------------------+-----------------------------------------------------+-----------+---------------+
| accountFlags       | Normal User Account                                 | List      | String        |
+--------------------+-----------------------------------------------------+-----------+---------------+
| accountFlags       | User Account is Disabled                            | List      | String        |
+--------------------+-----------------------------------------------------+-----------+---------------+
| department         | LOYALTY CLUB                                        |           |               |
+--------------------+-----------------------------------------------------+-----------+---------------+
| distinguishedName  | CN=Account02\,TM_Test02,OU=SailpointQA,OU=Users.... |           |               |
+--------------------+-----------------------------------------------------+-----------+---------------+
| employeeID         | 333223                                              |           |               |
+--------------------+-----------------------------------------------------+-----------+---------------+
| givenName          | TM_Test02                                           |           |               |
+--------------------+-----------------------------------------------------+-----------+---------------+
| memberOf           | NULL                                                |           |               |
+--------------------+-----------------------------------------------------+-----------+---------------+
| mobile             | 9                                                   |           |               |
+--------------------+-----------------------------------------------------+-----------+---------------+
| sAMAccountName     | TM_Test02.Account02                                 |           |               |
+--------------------+-----------------------------------------------------+-----------+---------------+
| sAMAccountType     | 805306368                                           |           |               |
+--------------------+-----------------------------------------------------+-----------+---------------+
| sn                 | Account02                                           |           |               |
+--------------------+-----------------------------------------------------+-----------+---------------+
| userAccountControl | 514                                                 |           |               |
+--------------------+-----------------------------------------------------+-----------+---------------+

Answer 2

You can try using the EXTRACTVALUE function and the XPATH

SELECT EXTRACTVALUE( xmlCol,  '//Map/entry [@key='accountExpires']/@value')

  AS accountExpires

from [identityiq].[identityiq].[spt_work_item_archive])