Чтение дочернего XML-узла из столбца

Содержание

Вопрос или проблема
Ответ или решение
Разбор SQL-запроса:
Оптимизация и SEO:

Вопрос или проблема

Мне была поставлена задача извлечь данные из системы учета, которая основана на базе данных SQL. Одна из колонок использует XML для хранения информации о производителе и модели, среди прочего. Мне удалось извлечь некоторые данные, но поскольку в XML есть повторяющаяся часть (“Field”), а затем происходит дальнейшая сортировка, мне не удалось добиться успеха.

XML в колонке MARCData:

<asset>
    <field>
        <name>
            <![CDATA[Классификация по обучению]]>
        </name>
        <dataType></dataType>
        <fieldID>34</fieldID>
        <data>
            <![CDATA[]]>
        </data>
    </field>
    <field>
        <name>
            <![CDATA[Местоположение]]>
        </name>
        <dataType>Текст</dataType>
        <fieldID>51</fieldID>
        <data>
            <![CDATA[]]>
        </data>
    </field>
    <field>
        <name>
            <![CDATA[Описание]]>
        </name>
        <dataType>Текст</dataType>
        <fieldID>1</fieldID>
        <data>
            <![CDATA[Ноутбук]]>
        </data>
    </field>
    <field>
        <name>
            <![CDATA[Цена замены]]>
        </name>
        <dataType>Валюта</dataType>
        <fieldID>24</fieldID>
        <data>
            <![CDATA[]]>
        </data>
    </field>
    <field>
        <name>
            <![CDATA[Производитель]]>
        </name>
        <dataType>Текст</dataType>
        <fieldID>202</fieldID>
        <data>
            <![CDATA[Lenovo]]>
        </data>
    </field>
    <field>
        <name>
            <![CDATA[Модель]]>
        </name>
        <dataType>Текст</dataType>
        <fieldID>203</fieldID>
        <data>
            <![CDATA[ThinkPad P17 Gen 2]]>
        </data>
    </field>
    <field>
        <name>
            <![CDATA[Соединения]]>
        </name>
        <dataType>Текст</dataType>
        <fieldID>1212</fieldID>
        <data>
            <![CDATA[Тест модели']]>
        </data>
    </field>
</asset>

Код, который я написал до сих пор:

SELECT 
    (CAST(MARCData AS xml)).value('(/asset/field)[6]', 'varchar(255)')

Я получаю “шестую” запись “field”, но хотел бы дальше ограничить результат, чтобы получить только информацию о модели. То, что указано выше, возвращает:

ModelText203ThinkPad P17 Gen 2

Но мне нужно, чтобы возвращало только:

ThinkPad P17 Gen 2

Я пробовал добавить больше ограничивающих элементов после [6], но получаю ошибки синтаксиса в SQL:

(CAST(MARCData AS xml)).value('(/asset/field)[6](/name/data)', 'varchar(255)')

Я знаю, как “углубляться” в XML, и знаю, как получить шестой элемент с определенным тегом “Field[6]”, но как продолжить “углубляться” после получения шестого элемента? (Извините, если формулировка неверная)

Похоже, вам просто нужно добавить оставшуюся часть пути, /data[1], например:

SELECT 
    (CAST(MARCData AS xml)).value('(/asset/field)[6]/data[1]', 'varchar(255)')

Ответ или решение

Для извлечения специфической информации из XML, хранящегося в колонке базы данных SQL, вы можете использовать XQuery. В вашем случае, чтобы получить информацию о модели из приведенного XML, вы уже правильно поняли, что после того, как вы получили нужный элемент "field", вам нужно продолжить "долго копать" в структуре XML.

Вот ваши исходные данные:

Ваш XML находится в колонке MARCData, и вы можете использовать следующий SQL-запрос:

SELECT 
    (CAST(MARCData AS xml)).value('(/asset/field)[6]/data[1]', 'varchar(255)') AS Model
FROM 
    YourTableName

Разбор SQL-запроса:

CAST(MARCData AS xml): Это преобразует вашу строку XML в формат XML, чтобы можно было удобно с ней работать.
.value(…): Это метод, который используется для извлечения значения из XML. Он принимает два аргумента: XQuery для выбора нужного элемента и тип данных, который вы хотите получить.
‘/asset/field’: Вы выбираете элемент field непосредственно в корневом элементе asset. Используя [6], вы указываете, что хотите получить шестой элемент, который в данном случае соответствует информации о модели.
‘/data[1]’: Это добавляется, чтобы извлечь текстовое значение, которое находится внутри элемента data. В данном случае [1] означает, что мы выбираем первый и единственный элемент data, который является дочерним к выбранному элементу field. Это нужно, так как вы хотите получить именно данные, а не всю структуру.

Оптимизация и SEO:

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

Проверка на null: Вы можете добавить проверку на null, если есть вероятность, что в некоторых записях может отсутствовать информация о модели.

SELECT 
    CASE 
        WHEN (CAST(MARCData AS xml)).value('(/asset/field)[6]/data[1]', 'varchar(255)') IS NULL 
        THEN 'Нет данных о модели' 
        ELSE (CAST(MARCData AS xml)).value('(/asset/field)[6]/data[1]', 'varchar(255)') 
    END AS Model
FROM 
    YourTableName

Индексы: Если колонка MARCData содержит много строк, рассмотрите возможность добавления индексов на частые запросы.
Оптимизация запросов: Если вы делаете подобные запросы часто, задумайтесь об оптимизации структуры базы данных, возможно, вынесите параметры из XML в отдельные колонки для быстрого доступа.

Таким образом, правильный SQL-запрос позволяет получить информацию о модели оборудования, хранящуюся в XML, эффективно и с минимальными затратами по производительности.