Запрос сериализованного словаря в столбце XML

Проблема, с которой вы столкнулись, связана со спецификацией схемы в вашем XML. Если узел в вашем XML-документе является частью схемы, вы должны указать эту схему при запросе этого узла. Кроме того, вы можете использовать подстановочный знак для спецификации схемы. Но указание имени узла без схемы не работает (как вы уже видели).

Итак, давайте рассмотрим пример:

скрипт SQL

Настройка схемы MS SQL Server 2008:

CREATE TABLE dbo.Tbl(id INT IDENTITY(1,1) PRIMARY KEY CLUSTERED, dict XML);

INSERT INTO dbo.Tbl(dict)
VALUES('<ArrayOfKeyValueOfstringanyType xmlns:i="http://www.w3.org/2001/XMLSchema-instance" xmlns="http://schemas.microsoft.com/2003/10/Serialization/Arrays">
      <KeyValueOfstringanyType>
         <Key>code</Key><Value xmlns:d3p1="http://www.w3.org/2001/XMLSchema" i:type="d3p1:string">WFR 052</Value>
      </KeyValueOfstringanyType>
      <KeyValueOfstringanyType>
        <Key>type</Key><Value xmlns:d3p1="http://www.w3.org/2001/XMLSchema" i:type="d3p1:string">Newsletter</Value>
      </KeyValueOfstringanyType>
    </ArrayOfKeyValueOfstringanyType>');

Таблица dbo.Tbl создается всего с двумя столбцами: идентификатором id и столбцом dict для XML.

Чтобы ваш первый запрос заработал, укажите схему, используя подстановочный знак для каждого узла:

Запрос 1:

SELECT dict.value('/*:ArrayOfKeyValueOfstringanyType[1]/*:KeyValueOfstringanyType[1]/*:Key[1]','NVARCHAR(MAX)')
FROM dbo.Tbl;

Это приводит к возвращению первого Key:

Результаты:

| COLUMN_0 |
|----------|
|     code |

Теперь вы хотите вернуть узлы Value для всех пар ключ-значение, где Key = 'code'. Вы можете сделать эту фильтрацию в xquery, но я обычно предпочитаю фильтрацию в SQL. Для этого нам сначала нужно вернуть все пары. Функция узлов XML делает нас на шаг ближе:

Запрос 2:

SELECT id,key_value.query('.')
  FROM dbo.Tbl
 CROSS APPLY dict.nodes('/*:ArrayOfKeyValueOfstringanyType/*:KeyValueOfstringanyType') AS N(key_value);

Он возвращает одну строку на узел KeyValueOfstringanyType:

Результаты:

| ID |                                                                                                                                                                                                                                                                                                      COLUMN_1 |
|----|---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------|
|  1 |    <p1:KeyValueOfstringanyType xmlns:p1="http://schemas.microsoft.com/2003/10/Serialization/Arrays"><p1:Key>code</p1:Key><p1:Value xmlns:d3p1="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:type="d3p1:string">WFR 052</p1:Value></p1:KeyValueOfstringanyType> |
|  1 | <p1:KeyValueOfstringanyType xmlns:p1="http://schemas.microsoft.com/2003/10/Serialization/Arrays"><p1:Key>type</p1:Key><p1:Value xmlns:d3p1="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:type="d3p1:string">Newsletter</p1:Value></p1:KeyValueOfstringanyType> |

Используя это, мы можем получить Key и Value с помощью функции XML.value: Запрос 3:

SELECT id,
       key_value.value('./*:Key[1]','NVARCHAR(MAX)') AS [key],
       key_value.value('./*:Value[1]','NVARCHAR(MAX)') AS [value]
  FROM dbo.Tbl
 CROSS APPLY dict.nodes('/*:ArrayOfKeyValueOfstringanyType/*:KeyValueOfstringanyType') AS N(key_value);

Теперь у нас есть строка для каждой пары ключ-значение с ключом и значением в отдельных столбцах:

Результаты:

| ID |  KEY |      VALUE |
|----|------|------------|
|  1 | code |    WFR 052 |
|  1 | type | Newsletter |

Отсюда легко применить дополнительные фильтры в предложении WHERE:

Запрос 4:

WITH KeyValues AS(
SELECT id,
       key_value.value('./*:Key[1]','NVARCHAR(MAX)') AS [key],
       key_value.value('./*:Value[1]','NVARCHAR(MAX)') AS [value]
  FROM dbo.Tbl
 CROSS APPLY dict.nodes('/*:ArrayOfKeyValueOfstringanyType/*:KeyValueOfstringanyType') AS N(key_value)
)
SELECT *
  FROM KeyValues
 WHERE [Key] = 'code';

Результаты:

| ID |  KEY |   VALUE |
|----|------|---------|
|  1 | code | WFR 052 |

Хороший ответ Себастьяна Майне, хотя при выполнении подобных запросов я предпочитаю фильтровать данные с помощью XPATH внутри функции nodes(), например:

select
    a.c.value('*:Value[1]','nvarchar(max)') as [value]
from Table1 as t
    outer apply t.Data.nodes('
        *:ArrayOfKeyValueOfstringanyType/*:KeyValueOfstringanyType[*:Key="code"]'
    ) as a(c)

Обычно это работает быстрее, чем синтаксический анализ всего xml, а затем фильтровать нужные вам ключи с помощью предложения where.

пример скрипта sql