我有一个列，该列中的数据库表中的名称属性是一个clob对象，该clob对象存储XML数据如下。使用SQL，我试图将KEY AccountExpires 的值回顾为 123456789L 作为输出。
我尝试使用节点(xquery(并进行交叉应用等，但是我只是在获取通常以价值格式的那些，例如 iiqdisabled 或 accountflags 等检索输入键和价值对。感谢你的帮助。
<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)
预期结果：
id accountExpires
-----------------
someid 123456789L
但实际上我正在获得 true 普通用户帐户用户帐户被禁用如果使用pref.value('。'，'，'varchar(50('(在输出中作为测试列。
从您自己的代码中我选择这是SQL-Server。至少语法看起来像这样。
您可以尝试以下方法：
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>';
- 查询
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);
结果
+--------------------+-----------------------------------------------------+---------+------+--------+--------------------------+
| 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 |
+--------------------+-----------------------------------------------------+---------+------+--------+--------------------------+
一些解释：
有三种数据：
- 简单的键值对
- 键入键值对
- 键入列表值
查询将使用.nodes()潜入所有<entry>元素，并将其返回为派生的表。第一个OUTER APPLY将在给定<entry>下方的<value>元素的情况下返回附加行/列。该元素可能具有一个值(例如布尔值" true"(，也可能包含一个键入列表。第二个OUTER APPLY潜水 - 如果存在 - - 进入<value>的子节点，并将它们作为附加行返回。
这样的查询将在EAV式
中返回更多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);
结果
+--------------------+-----------------------------------------------------+-----------+---------------+
| 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 | | |
+--------------------+-----------------------------------------------------+-----------+---------------+
您可以尝试使用ExtractValue函数和XPath
SELECT EXTRACTVALUE( xmlCol, '//Map/entry [@key='accountExpires']/@value')
AS accountExpires
from [identityiq].[identityiq].[spt_work_item_archive])