Xml value ms sql: value() Method (xml Data Type) — SQL Server

Метод value() (тип данных xml) — SQL Server

• 
  Статья
• 
  09/26/2022
• Чтение занимает 3 мин
• 
  

Область применения: SQL Server (все поддерживаемые версии) База данных SQL Azure Управляемый экземпляр SQL Azure

Выполняет запрос XQuery к структуре XML и возвращает значение типа SQL. Данный метод возвращает скалярное значение.

Обычно этот метод применяется для извлечения значения из экземпляра XML, который хранится в столбце, параметре или переменной типа xml. При этом его можно использовать в запросах SELECT для объединения или сравнения данных XML с данными в столбцах других типов.

Синтаксис

value (XQuery, SQLType)  

Примечание

Ссылки на описание синтаксиса Transact-SQL для SQL Server 2014 и более ранних версий, см. в статье Документация по предыдущим версиям.

Аргументы

XQuery
Выражение XQuery — строковый литерал, извлекающий данные из экземпляра XML. Выражение XQuery должно возвращать не более одного значения. Иначе возвращается ошибка.

SQLType
Предпочтительный тип SQL — строковый литерал, который необходимо вернуть. Тип возвращаемого этим методом значения соответствует значению параметра SQLType. SQLType не может быть типом данных xml, определяемым пользователем типом CLR, типом данных image, text, ntext или sql_variant. Значением SQLType может быть SQL и определяемый пользователем тип данных.

Метод value() неявно использует оператор Transact-SQL CONVERT и пытается преобразовать результат выражения XQuery (сериализованное строковое представление) из типа данных XSD в соответствующий тип SQL, указанный в преобразовании Transact-SQL. Дополнительные сведения о правилах приведения типов для оператора CONVERT см. в статье Функции CAST и CONVERT (Transact-SQL).

Примечание

Для увеличения производительности операции сравнения с реляционным значением вместо метода value() в предикате используйте метод exist() совместно с sql:column() . Это показано в следующем примере Г.

Примеры

A. Использование метода value() над переменной типа xml

В следующем примере экземпляр XML хранится в переменной типа xml. Метод value() извлекает из переменной XML значение атрибута ProductID. Затем полученное значение присваивается переменной типа int.

DECLARE @myDoc XML  
DECLARE @ProdID INT  
SET @myDoc = '<Root>  
<ProductDescription ProductID="1" ProductName="Road Bike">  
<Features>  
  <Warranty>1 year parts and labor</Warranty>  
  <Maintenance>3 year parts and labor extended maintenance is available</Maintenance>  
</Features>  
</ProductDescription>  
</Root>'  
  
SET @ProdID =  @myDoc. value('(/Root/ProductDescription/@ProductID)[1]', 'int' )  
SELECT @ProdID  

В результате возвращается значение 1.

Хотя экземпляр XML содержит только один атрибут ProductID, правила статической типизации требуют явно указать, что выражение пути возвращает единственное значение. Поэтому в конце выражения пути указан дополнительный знак [1]. Дополнительные сведения о статической типизации см. в разделе XQuery и статическая типизация.

Б. Использование метода value() для извлечения значения из столбца типа xml

В следующем примере выполняется запрос к столбцу типа xml (CatalogDescription) в базе данных AdventureWorks. Запрос извлекает значения атрибутов ProductModelID из каждого экземпляра XML, который хранится в столбце.

SELECT CatalogDescription.value('             
    declare namespace PD="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelDescription";             
       (/PD:ProductDescription/@ProductModelID)[1]', 'int') AS Result             
FROM Production. ProductModel             
WHERE CatalogDescription IS NOT NULL             
ORDER BY Result DESC             

Обратите внимание на следующие данные из предыдущего запроса:

• Для определения префикса пространства имен используется ключевое слово namespace.

• Требования к статической типизации в выражении пути обязывают в методе [1] добавлять value() в конце выражения пути, чтобы явно указать, что оно возвращает единственное значение.

Частичный результат:

-----------  
35           
34           
...  

В. Использование методов value() и exist() для извлечения значений из столбца типа xml

В следующем примере демонстрируется совместное использование методов value() и exist(), возвращающих тип данных xml. Метод value() извлекает значения атрибутов ProductModelID из экземпляров XML. Метод exist() в предложении WHERE фильтрует строки, извлеченные из таблицы.

Запрос извлекает идентификаторы моделей продуктов из экземпляров XML, которые среди прочего содержат сведения о гарантии (элемент <Warranty>). Условие в предложении WHERE приводит к тому, что метод exist() извлекает только те строки, которые соответствуют условию.

SELECT CatalogDescription.value('  
     declare namespace PD="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelDescription";  
           (/PD:ProductDescription/@ProductModelID)[1] ', 'int') AS Result  
FROM  Production.ProductModel  
WHERE CatalogDescription.exist('  
     declare namespace PD="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelDescription";  
     declare namespace wm="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelWarrAndMain";  
  
     /PD:ProductDescription/PD:Features/wm:Warranty ') = 1  

Обратите внимание на следующие данные из предыдущего запроса:

• Столбец CatalogDescription является типизированным XML-столбцом. Это означает, что с ним связан набор схем. В XQuery Prolog объявлено пространство имен, определяющее префикс, который затем используется в теле запроса.

• Если метод exist() возвращает значение 1 (True), это значит, что экземпляр XML содержит дочерний элемент <Warranty>.

• Метод value() в предложении SELECT затем извлекает значения атрибутов ProductModelID в виде целых чисел.

Частичный результат:

Result       
-----------  
19           
23           
...  

Г. Использование метода exist() вместо метода value()

Для увеличения производительности операции сравнения с реляционным значением вместо метода value() в предикате используйте метод exist() совместно с sql:column(). Пример:

CREATE TABLE T (c1 INT, c2 VARCHAR(10), c3 XML)  
GO  
  
SELECT c1, c2, c3   
FROM T  
WHERE c3.value( '(/root[@a=sql:column("c1")]/@a)[1]', 'integer') = c1  
GO  

Запрос будет выглядеть следующим образом:

SELECT c1, c2, c3   
FROM T  
WHERE c3. exist( '/root[@a=sql:column("c1")]') = 1  
GO  

См. также:

Добавление пространств имен в запросы с WITH XMLNAMESPACES
Сравнение типизированного и нетипизированного XML
Создание экземпляров данных XML
Методы для типа данных XML
Язык модификации XML-данных (XML DML)

Работа с XML данными в Microsoft SQL Server 2008. Тип данных «xml».

В прошлой статье мы узнали, каким образом из базы можно извлекать реляционные данные в виде XML структуры. Сегодня мы познакомимся с принципами хранения структуры XML данных в базе.

Основной элемент поддержки XML в MS SQL Server – тип данных “xml”. Данный тип может использоваться для объявления переменных и как тип колонки в таблице наравне со стандартными типами данных SQL (int, nvarchar и т.д.).

Для типа данных XML доступны следующие специализированные операции. Приведем их краткое описание:

1. query() – осуществляет запросы к XML данным;

2. nodes() – извлекает поддерево из структуры XML.

3. value() – позволяет извлекать значения атрибутов из XML элемента;

4. exist() – проверяет существуют ли результаты запроса;

5. modify() – производит обновление XML данных;

Рассмотрим данные команды подробнее:

Команда «query()».

Эта команда позволяет писать запросы к XML дереву. Команда возвращает набор результатов, соответствующих запросу. Рассмотрим пример. Допустим, у нас есть следующая XML структура:

   1:  DECLARE @xmlData XML =N'

   2:  <Shops> 

   3:      <Shop>

   4:          <device name="Sensation" vendor="HTC" />

   5:          <device name="iPhone" vendor="Apple" />

   6:      </Shop>

   7:      <Shop>

   8:          <device name="Mozart" vendor="HTC" />

   9:          <device name="Lumia" vendor="Nokia" />

  10:      </Shop>

  11:  </Shops>';

Выполним следующий запрос:

   1:  SELECT @xmlData. query('/Shops/Shop/device')

Как результат, мы получим все элементы <device>, путь к которым соответствует нашему запросу:

   1:  <device name="Sensation" vendor="HTC" />

   2:  <device name="iPhone" vendor="Apple" />

   3:  <device name="Mozart" vendor="HTC" />

   4:  <device name="Lumia" vendor="Nokia" />

Стоит отметить, что имена элементов и атрибутов регистрозависимые, как в XML структуре, так и в тексте запроса.

Также, в запросе, можно наложить некоторые ограничения на выбираемые данные, помимо пути. Например, давайте напишем запрос, который будет выбирать из структуры XML все устройства, производителем которых является компания «HTC»:

   1:  SELECT @xmlData.query('/Shops/Shop/device/.[@vendor cast as xs:string? = "HTC"]') as Data

Запрос вернет следующий результат:

Команда «nodes()».

Разбивает XML структуру на одно или несколько поддеревьев, в соответствии с указанным запросом. Для примера будем использовать ту же структуру данных @xmlData.

Выполним следующий запрос:

   1:  SELECT shop.query('.') as data FROM @xmlData.nodes('/Shops/Shop') col(shop)

Данный запрос разобьет исходную структуру на строки, по количеству элементов <Shop> и вернет нам две строки:

Разберем текст запроса подробнее.

‘@xmlData.nodes(‘/Shops/Shop’)’ — собственно разбивает данные по указанному запросу.

‘col(shop)’ – это псевдоним для результатов разбиения. Данный псевдоним необходим для дальнейшей работы с результатами.

‘shop.query(‘.’)’ – здесь осуществляется запрос к каждой строке результатов, при помощи псевдонима. Данный подзапрос не осуществляет фильтрации, выбирая все данные из поддерева.

Команда «value()».

С помощью данной команды можно извлекать значения из XML дерева. Команда умеет конвертировать строковые литералы из XML текста в любые типы данных среды MS SQL Server.

В качестве примера давайте преобразуем XML структуру @xmlData в табличное представление, выполнив следующий запрос:

   1:  SELECT 

   2:      device.value('@id', 'int') as Id,

   3:      device.value('@vendor', 'nvarchar(50)') as Company,

   4:      device.value('@name', 'nvarchar(50)') as Name        

   5:  FROM @xmlData.nodes('/Shops/Shop/device') col(device)

Данный запрос вернет следующий табличный результат:

Команда value() принимает два параметра: название атрибута с префиксом @ и название типа данных, к которому необходимо привести переменную.

Аналогичным способом можно получить доступ к значению, расположенным внутри элемента хмл, например:

   1:  DECLARE @data XML = N'

   2:  <Shops>

   3:      <Shop>Winodws Marketplace</Shop>    

   4:  </Shops>';

   5:   

   6:  SELECT 

   7:      shop. value('@id', 'int') as Id,

   8:      shop.value('.[1]', 'nvarchar(50)') as CompanyName    

   9:  FROM @data.nodes('/Shops/Shop') col(shop)

Результат будет следующим:

Также можно получить доступ к родительскому элементу любого уровня. Давайте модернизируем наш первый запрос к @xmlData, добавив в выборку Id магазина, в котором находится устройство:

   1:  SELECT 

   2:      device.value('@id', 'int') as Id,

   3:      device.value('@vendor', 'nvarchar(50)') as Company,

   4:      device.value('@name', 'nvarchar(50)') as Name,

   5:      device.value('../@id', 'int') as ShopId

   6:  FROM @xmlData.nodes('/Shops/Shop/device') col(device)

Результат похож на то, что мы хотели:

Результат отобразился как надо, однако при больших объемах данных такой запрос будет крайне медленным и неэффективным. Проблема в том, что для каждого устройства среда выполнения запроса ищет родительский элемент и считывает его атрибут. Перепишем запрос следующим образом:

   1:  SELECT 

   2:      device.value('@id', 'int') as Id,

   3:      device.value('@vendor', 'nvarchar(50)') as Company,

   4:      device.value('@name', 'nvarchar(50)') as Name,

   5:      shop.value('@id', 'int') as ShopId

   6:  FROM 

   7:      @xmlData.nodes('/Shops/Shop') col(shop)

   8:  CROSS APPLY

   9:      shop.nodes('device') tab(device)

Результат будет аналогичным, однако теперь мы поменяли логику запроса таким образом, что вначале будут выбираться все магазины, а потом, для каждого магазина, будут присоединяться устройства. Поскольку устройств всегда будет намного больше, чем магазинов, данный запрос даст нам существенный прирост производительности.

Команда «exist()».

Данная команда возвращает значения типа bit. 1 – если результаты, соответствующие запросу существуют, и 0 – если не существуют.

Запрос формируется таким же образом, как и в команде query(). Команда используется, как правило, для наложения ограничений в запросе выборки, применяя фильтрующий запрос exist() к поддереву. Приведем пример:

   1:  SELECT     

   2:      shop.value('@id', 'int') as ShopId

   3:  FROM 

   4:      @xmlData.nodes('/Shops/Shop') col(shop)

   5:  WHERE shop.exist('device[@vendor cast as xs:string? = "Nokia"]') = 1

Данный запрос выберет Id всех магазинов, в которых присутствуют устройства от компании «Nokia»:

На этом данная статья подходит к концу. В следующей статье мы рассмотрим последнюю команду типа «xml» — modify(), а также обсудим различные способы оптимизации взаимодействия с XML структурами в среде Microsoft SQL Server.

Метод

value () (тип данных xml) — SQL Server

• Статья
• 17.12. 2021
• 4 минуты на чтение

Применимо к:
SQL Server (все поддерживаемые версии)
База данных SQL Azure
Управляемый экземпляр Azure SQL

Выполняет запрос XQuery к XML и возвращает значение типа SQL. Этот метод возвращает скалярное значение.

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

Синтаксис

Значение

 (XQuery, SQLType)
 

Примечание

Чтобы просмотреть синтаксис Transact-SQL для SQL Server 2014 и более ранних версий, см. документацию по предыдущим версиям.

Аргументы

XQuery
Это выражение XQuery , строковый литерал, который извлекает данные внутри экземпляра XML. XQuery должен возвращать не более одного значения. В противном случае возвращается ошибка.

SQLType
Предпочтительный возвращаемый тип SQL, строковый литерал. Тип возвращаемого значения этого метода соответствует параметру SQLType . SQLType не может быть типом данных xml , определяемым пользователем типом общеязыковой среды выполнения (CLR), image , text , ntext или sql_variant тип данных. SQLType может быть SQL, определяемым пользователем типом данных.

Метод value() неявно использует оператор Transact-SQL CONVERT и пытается преобразовать результат выражения XQuery, сериализованное строковое представление, из типа XSD в соответствующий тип SQL, указанный преобразованием Transact-SQL. Дополнительные сведения о правилах приведения типов для CONVERT см. в разделе CAST и CONVERT (Transact-SQL).

Примечание

Из соображений производительности вместо использования метода value() в предикате для сравнения с реляционным значением используйте exists() с sql:column() . Это показано в следующем примере D.

Примеры

A. Использование метода value() для переменной типа xml

В следующем примере экземпляр XML хранится в переменной типа xml . Метод value() извлекает ProductID 9Значение атрибута 0082 из XML. Затем значение присваивается переменной int .

 DECLARE @myDoc XML
ОБЪЯВИТЬ @ProdID INT
УСТАНОВИТЕ @myDoc = '<Корень>

<Возможности>
  <Гарантия>1 год на запчасти и работу
  Доступно трехлетнее техническое обслуживание на запчасти и ремонт


'
  
SET @ProdID = @myDoc.value('(/Root/ProductDescription/@ProductID)[1]', 'int' )
ВЫБЕРИТЕ @ProdID
 

В результате возвращается значение 1.

Хотя в экземпляре XML имеется только один атрибут ProductID , правила статической типизации требуют, чтобы вы явно указали, что выражение пути возвращает одноэлементное значение. Поэтому в конце выражения пути указывается дополнительный [1] . Дополнительные сведения о статической типизации см. в разделе XQuery и статическая типизация.

B. Использование метода value() для извлечения значения из столбца типа xml

Следующий запрос задан для столбца типа xml ( CatalogDescription ) в базе данных AdventureWorks . Запрос извлекает значение атрибута ProductModelID из каждого экземпляра XML, хранящегося в столбце.

 ВЫБЕРИТЕ КаталогОписание.значение('
    объявить пространство имен PD="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelDescription";
       (/PD:ProductDescription/@ProductModelID)[1]', 'int') КАК Результат
ИЗ Производство. Модель Продукта
ГДЕ КаталогОписание НЕ НУЛЕВОЕ
ORDER BY Результат DESC
 

Обратите внимание на следующее из предыдущего запроса:

• Ключевое слово пространства имен используется для определения префикса пространства имен.

• В соответствии с требованиями статической типизации [1] добавляется в конце выражения пути в методе value() , чтобы явно указать, что выражение пути возвращает одноэлементное значение.

Это частичный результат:

 -----------
35
34
...
 

C. Использование методов value() и exists() для извлечения значений из столбца типа xml

В следующем примере показано использование как метода value() , так и метода exists() для типа данных xml . Метод value() используется для получения значений атрибута ProductModelID из XML. Метод exists() в предложении WHERE используется для фильтрации строк из таблицы.

Запрос извлекает идентификаторы моделей продуктов из экземпляров XML, которые включают информацию о гарантии (< Гарантия > элемент) в качестве одной из функций. Условие в предложении WHERE использует метод exists() для извлечения только тех строк, которые удовлетворяют этому условию.

 ВЫБЕРИТЕ КаталогОписание.значение('
     объявить пространство имен PD="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelDescription";
           (/PD:ProductDescription/@ProductModelID)[1] ', 'int') КАК Результат
ИЗ Производство. Модель Продукта
ГДЕ КаталогОписание.существует('
     объявить пространство имен PD="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelDescription";
     объявить пространство имен wm="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelWarrAndMain";
  
     /PD:ProductDescription/PD:Features/wm:Warranty ') = 1
 

Обратите внимание на следующее из предыдущего запроса:

• Столбец CatalogDescription представляет собой типизированный столбец XML. Это означает, что с ним связана коллекция схем. В прологе XQuery объявление пространства имен используется для определения префикса, который используется позже в теле запроса.

• Если метод exists() возвращает 1 (Истина), это указывает на то, что экземпляр XML включает дочерний элемент < Warranty > в качестве одной из функций.

• Метод value() в предложении SELECT затем извлекает значения атрибута ProductModelID в виде целых чисел.

Это частичный результат:

 Результат
-----------
19
23
...
 

D. Использование метода exists() вместо метода value()

Из соображений производительности вместо использования метода value() в предикате для сравнения с реляционным значением используйте exists() с sql:column() . Например:

 CREATE TABLE T (c1 INT, c2 VARCHAR(10), c3 XML)
ИДТИ
  
ВЫБЕРИТЕ с1, с2, с3
ОТ Т
ГДЕ c3.value( '(/root[@a=sql:column("c1")]/@a)[1]', 'integer') = c1
ИДТИ
 

Это можно записать следующим образом:

 SELECT c1, c2, c3
ОТ Т
ГДЕ c3.exist('/root[@a=sql:column("c1")]') = 1
ИДТИ
 

См. также

Добавление пространств имен в запросы с помощью WITH XMLNAMESPACES
Сравнение типизированного XML с нетипизированным XML
Создание экземпляров данных XML
Методы типов данных xml
Язык модификации данных XML (XML DML)

Как запросить значения и атрибуты XML из таблицы в SQL Server?

У меня есть таблица, содержащая столбец Xml :

 SELECT *
ОТ кв. м.
 

Пример данных строки xml :

 
  <Показатели>
    73701
    632704
    12967
    2299194
    13752614
  

 

В случае этих данных я хотел бы:

 SqmId тип идентификатора единица счета сумма minValue maxValue стандартное значение отклонения
===== ============================================= ====== ===== ==== ===== ====== ======== ======== ======== ========= ======
1 Таймер TransactionCleanupThread. RecordUsedTransactionShift мкс 1 21490 73701 73701 НУЛЕВОЙ 73701
1 Таймер TransactionCleanupThread.RefundOldTrans мкс 1 184487 632704 632704 NULL 632704
1 Таймер Database.CreateConnection_SaveContextUserGUID мкс 2 7562 12928 13006 16 12967
1 Таймер Global.CurrentUser мкс 6 4022464 15 13794345 1642047 2299194
1 Таймер Global.CurrentUser_FetchIdentityFromDatabase мкс 1 4010057 13752614 13752614 NULL 13752614
2 ...
 

В конце концов, я действительно буду выполнять SUM() , MIN() , MAX() агрегацию. Но пока я просто пытаюсь запросить столбец xml.

В псевдокоде я бы попробовал что-то вроде:

 SELECT
    SqmId,
    Data.query('/Sqm/Metrics/Metric/@id') Идентификатор AS,
    Data.query('/Sqm/Metrics/Metric/@type') Тип AS,
    Data.query('/Sqm/Metrics/Metric/@unit') Единица AS,
    Data.query('/Sqm/Metrics/Metric/@sum') КАК сумма,
    Data.query('/Sqm/Metrics/Metric/@count') Количество AS,
    Data.query('/Sqm/Metrics/Metric/@minValue') AS minValue,
    Data. query('/Sqm/Metrics/Metric/@maxValue') AS maxValue,
    Data.query('/Sqm/Metrics/Metric/@standardDeviation') КАК стандартное отклонение,
    Data.query('/Sqm/Metrics/Metric') Значение AS
ОТ кв.м.
 

Но этот запрос SQL не работает:

Сообщение 2396, уровень 16, состояние 1, строка 2
XQuery [Sqm.data.query()]: атрибут не может отображаться за пределами элемента

Я искал, и поразительно, насколько плохо документированы или приведены примеры запросов Xml. Большинство ресурсов вместо запроса таблицы запрашивают переменную ; что я не делаю. Большинство ресурсов используют XML-запросы только для фильтрации и выбора, а не для чтения значений. Большинство ресурсов считывают жестко закодированные дочерние узлы (по индексу), а не фактические значения.

• https://stackoverflow.com/questions/966441/xml-query-in-sql-server-2008
• XML-атрибут запроса SQL Server для значения элемента
• SQL запрашивает атрибуты XML
• SQL Server 2005 XQuery и XML-DML — часть 1
• BOL: поддержка XML в Microsoft SQL Server 2005
• Запрос XML в SQL Server
• Основные XML-запросы SQL Server
• BOL: метод query() (тип данных xml)
• XML Workshop V — Чтение значений из XML-столбцов
• SQL SERVER – Введение в обнаружение методов типов данных XML – Учебник

Я пытался случайным образом использовать .