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

Содержание

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

Статья

03/02/2023

Чтение занимает 3 мин

Применимо к:база данныхSQL Server Azure SQL Управляемый экземпляр 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-столбцам—ArcMap | Документация

Извлечение нескольких значений из XML-столбца

В системных таблицах базы геоданных GDB_Items и GDB_ItemRelationships есть несколько XML-столбцов, содержащих сведения о схеме и отношениях элементов. В частности, в столбце Definition в таблице GDB_Items записываются подробные сведения о базе геоданных. Тип документа XML в столбце зависит от конкретного типа элемента. Например, определение класса пространственных объектов содержит информацию о полях, доменах и подтипах таблицы, пространственной привязке геометрии, а также о том, участвует ли класс пространственных объектов в наборе данных контроллера.

Для работы со значением из столбца XML в системной таблице в IBM Db2, Microsoft SQL Server или PostgreSQL необходимо получить XML-документ из базы данных целиком и работать с ним локально в XML или текстовом средстве просмотра. Для программистов, работающих с Java, C++ или C#, будет удобнее просматривать документ в Document Object Model (DOM). Разработчики SQL могут использовать функции базы данных XML для получения определенных значений из определений элементов с помощью XPath (языка запросов для XML-документов).

В базах геоданных под управлением Oracle и IBM Informix столбцы XML используют ArcSDE XML, который хранит информацию в виде больших двоичных объектов в нескольких отдельных таблицах. К ним можно обращаться напрямую с помощью SQL.

Для просмотра содержимого в столбцах XML системных таблиц баз геоданных под управлением Oracle предусмотрены два системных вида, в которых содержимое столбцов XML-таблиц базы геоданных хранится в виде объектов с типом данных CLOB. Вид GDB_Items_vw отображает содержимое столбцов Definition, Documentation и ItemInfo из таблицы GDB_Items в столбцах CLOB. Вид GDB_ItemRelationships_vw отображает содержимое столбцов Attributes из таблицы GDB_ItemRelationships в столбце CLOB. CLOB могут быть прочитаны как текст.

Примечание:

В разных СУБД подписи и поведение XML-функций могут существенно отличаться.

В следующем примере показано определение элемента определения интервального домена в XML-документе:

<? xml version = "1.0" encoding="utf-8"?>
<GPRangeDomain2
     xmlns:xsi = "http://www.w3.org/2001/XMLSchema-instance"
     xmlns:xs = "http://www.w3.org/2001/XMLSchema"
     xmlns:typens = "http://www.esri.com/schemas/ArcGIS/10.0"
     xsi:type = "typens:GPRangeDomain2">
  <DomainName>Angle</DomainName>
  <FieldType>esriFieldTypeInteger</FieldType>
  <MergePolicy>esriMPTDefaultValue</MergePolicy>
  <SplitPolicy>esriSPTDuplicate</SplitPolicy>
  <Description>Valid rotation angles</Description>
  <Owner>harley</Owner>
  <MaxValue xsi:type = "xs:int">359</MaxValue>
  <MinValue xsi:type = "xs:int">0</MinValue>
</GPRangeDomain2>

Двумя наиболее важными значениями для интервального домена являются минимальное и максимальное значения. Эти элементы представлены выражениями /GPRangeDomain2/MinValue и /GPRangeDomain2/MaxValue, соответственно. Ниже приведен пример запроса SQL, извлекающего эти значения конкретного интервального домена в базе геоданных в SQL Server.

--Queries an sde-schema geodatabase in SQL Server
SELECT
  Definition.value('(/GPRangeDomain2/MinValue)[1]','nvarchar(max)') AS "MinValue",
  Definition.value('(/GPRangeDomain2/MaxValue)[1]','nvarchar(max)') AS "MaxValue"
FROM
  sde.GDB_ITEMS INNER JOIN sde.GDB_ITEMTYPES
  ON sde.GDB_ITEMS.Type = sde.GDB_ITEMTYPES.UUID
WHERE
  sde.GDB_ITEMS.Name = 'Angle' AND
  sde.GDB_ITEMTYPES.Name = 'Range Domain'
MinValue   MaxValue
      0         359

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

Вы можете выполнять запросы к другим XML-столбцам системных таблиц также, как они формулируются для столбца Definition таблицы GDB_Items. Учтите, что для столбца Documentation не предусмотрено XML-схемы, определяемой базой геоданных. В столбце Documentation содержатся метаданные, связанные с элементами базы геоданных. В разных организациях элементы метаданных, содержащиеся в этом столбце, отличаются, так как это зависит от стандартов метаданных и рабочих процессов, используемых для управления ими. Документ XML DTD с описанием структуры метаданных ArcGIS – ArcGISmetadatav1.dtd – поставляется вместе с ArcGIS Desktop и находится в папке \Metadata\Translator\Rules директории установки ArcGIS.

Довольно часто имеет смысл извлечь из одного XML-документа несколько значений сразу. Ниже приведено значение Definition для одного из таких примеров, домена кодированных значений:

<? xml version = "1.0" encoding="utf-8"?>
<GPCodedValueDomain2
    xmlns:xsi = "http://www.w3.org/2001/XMLSchema-instance"
    xmlns:xs = "http://www.w3.org/2001/XMLSchema"
    xmlns:typens = "http://www.esri.com/schemas/ArcGIS/10. 0"
  <DomainName>Material</DomainName>
  <FieldType>esriFieldTypeString</FieldType>
  <MergePolicy>esriMPTDefaultValue</MergePolicy>
  <SplitPolicy>esriSPTDuplicate</SplitPolicy>
  <Description>Valid pipe materials</Description>
  <Owner>aelflad</Owner>
  <CodedValues xsi:type= "typens:ArrayOfCodedValue">
   <CodedValue  xsi:type= "typens:CodedValue">
     <Name>Cast iron</Name>
     <Code xsi:type= "xs:string">CI</Code>
   </CodedValue>
   <CodedValue  xsi:type= "typens:CodedValue">
     <Name>Ductile iron</Name>
     <Code xsi:type= "xs:string">DI</Code>
   </CodedValue>
   <CodedValue  xsi:type= "typens:CodedValue">
     <Name>PVC</Name>
     <Code xsi:type= "xs:string">PVC</Code>
   </CodedValue>
   <CodedValue  xsi:type= "typens:CodedValue">
     <Name>Asbestos concrete</Name>
     <Code xsi:type= "xs:string">AC</Code>
   </CodedValue>
   <CodedValue  xsi:type= "typens:CodedValue">
     <Name>Copper</Name>
     <Code xsi:type= "xs:string">COP</Code>
   </CodedValue>
  </CodedValues>
</GPCodedValueDomain2>

Как правило разработчикам и администраторам требуются пары кодов и значений с выражением XPath вида /GPCodedValueDomain2/CodedValues/CodedValue. В следующем примере показано, каким образом можно извлечь несколько значений из одного XML-определения, чтобы получить пары кода и значений для всех доменов в базе геоданных в SQL Server.

-- Get the code/value pairs for each coded value domain in the geodatabase.
SELECT
   codedValue.value('Code[1]', 'nvarchar(max)') AS "Code",
   codedValue.value('Name[1]', 'nvarchar(max)') AS "Value"
FROM
   dbo.GDB_ITEMS AS items INNER JOIN dbo.GDB_ITEMTYPES AS itemtypes
   ON items.Type = itemtypes.UUID
CROSS APPLY
   items.Definition.nodes
    ('/GPCodedValueDomain2/CodedValues/CodedValue') AS CodedValues(codedValue)
WHERE
   itemtypes.Name = 'Coded Value Domain' AND
   items.Name = 'Material'
Code    Value
CI      Cast iron
DI      Ductile iron
PVC     PVC
AC      Asbestos concrete
COP     Copper

Метод

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

Статья
28. 02.2023
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 – Учебник

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