MS SQL vs XML

Оказывается в MS SQL есть целый инструментарий XQuery, как работать с данными типа XML в качестве элемента записи. Получается почти еврейский SQL: внутри запроса содержится запрос. Можно работать со столбцами, когда ты работаешь со столбцами!

Как всегда заметка для освежения своей памяти в будущем ибо продвинутую магию можно загуглить.

Модель работы с XML достаточно простая: у нас есть вложенные друг в друга узлы (возможно именованные), по которым можно добраться до значения поля. Формат обращения относительно "интуитивен", если почитать документацию: у меня встали волосы дыбом от референсного SQL запроса от коллеги.

Простенький запрос для примера (база Интрасервис):

SELECT
     [Data]
    ,[Data].value('(/data/node())[2]', 'nvarchar(max)') TaskType
    ,[Data].value('(/data/field[@id=60])[1]','nvarchar(max)') background
    ,[Data].value('(/data/field[@id=12])[1]','nvarchar(max)') TaskType2
FROM [Intraservice420].[dbo].[Task]
WHERE TaskId = 183021

Да, меня забесило обращение по индексу в поле TaskType.

Почему разименование массива по индексу 1, когда итак есть обращение по имени элемента? Результат обращение по имени элемента - массив.

Собствекнно результат (с немного отформатированным XML) который всё проясняет. И таки да: nvarchar(max) - любимый костылик преобразования типов. True - оно так в базе: или пусто, или True.

Data       : <data>
<field id="2">7</field>
<field id="12">26</field>
<field id="26">61</field>
<field id="27" />
<field id="28" />
<field id="60">True</field>
</data>
TaskType   : 26
background : True
TaskType2  : 26

Писать в XML по схожей схеме? Почти. Если XML без объявления схемы, то тип элемента будет undefined (в который парсер не знает как писать) и надо воспользоваться маленьким костыликом преобразования типов.

    UPDATE [Intraservice420].[dbo].[Task]
    SET [Data].modify('replace value of 
        (/data/field[@id=60/text()])[1] with ("")')
    WHERE ID = 183021

Происходит (1) преобразование типа к текстовому виду /text() и (2) таки опять разименование элемента массива.