Как получить значение из пары ключ-значение в XML через SQL-запрос?

У меня есть столбец с атрибутами имени в моей таблице базы данных, который представляет собой объект clob, хранящий данные XML, как показано ниже. Используя SQL, я пытаюсь получить значения ключа accountExpires, который должен дать мне значение 123456789Л в качестве вывода.

Я пытался использовать узлы (Xquery) и CROSS APPLY и т. д., но я извлекаю только те, которые обычно имеют формат значения, например IIQDisabled или аккаунтФлаги и т. д., но я хочу получить пары ключа входа и значения. Ценю твою помощь.

<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

Но на самом деле я получаю истинныйОбычная учетная запись пользователяУчетная запись пользователя отключена, если использую pref.value('.', 'varchar(50)') в выводе в качестве тестового столбца.

Привет, просто для чистоты и порядка: вы отметили этот вопрос [sql-server], но приняли ответ для [oracle]. Это не поможет будущим посетителям, пытающимся найти ответ. Пожалуйста, либо измените теги вопроса на [oracle], либо примите существующий ответ, связанный с [sql-server] (или напишите свой собственный и примите этот...)

— 17.06.2019 13:27

sql sql-server xml

27.05.2019 08:05

ReactJs | Supabase | Добавление данных в базу данных

Это и есть ваш редактор таблиц в supabase.👇

Понимание Python и переход к SQL

Перед нами лабораторная работа по BloodOath:

3 728

Перейти к ответу Данный вопрос помечен как решенный

Ответы 2

Ответ принят как подходящий

Вы можете попробовать использовать функцию EXTRACTVALUE и XPATH

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

  AS accountExpires

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

27.05.2019 08:32

Из вашего собственного кода я понимаю, что это SQL-сервер. По крайней мере, синтаксис выглядит так.

Вы можете попробовать это:

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 вернет дополнительные строки/столбцы в случаях, когда есть элемент <value> под данным <entry>. Этот элемент может иметь значение (например, логическое значение «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                                                 |           |               |
+--------------------+-----------------------------------------------------+-----------+---------------+

Идеально! Это то, что я ищу. Спасибо за ваше время и подробное объяснение.

— 27.05.2019 12:16

27.05.2019 09:39