Query xml ms sql: How can I query a value in SQL Server XML column

Содержание

Запрос данных XML с использованием SQL XML в SQL Server

Усовершенствования SQL Server XML заслуживают особого внимания в отношении SQL Server 2005 и его преемников SQL Server 2008 и SQL Server 2012.
Программисты SQL могут импортировать XML в SQL Server и хранить данные XML в таблицах SQL Server в столбцах таблиц, которые имеют типы данных XML.
Данные XML в столбцах таблицы или сохраненные в переменных XML можно легко прочитать с помощью операторов SQL XML Select.

В этом учебном пособии по SQL Server XML я хочу показать, как разработчики Transact-SQL могут запрашивать XML-данные для чтения текста узла и значений атрибутов.
После преобразования XML в SQL разработчики T-SQL могут легко импортировать XML в таблицы SQL Server с помощью простой инструкции SQL INSERT INTO.

В нашем образце данных XML для этого руководства по SQL у нас есть книги по SQL Server (названия книг), сохраненные в формате XML.
В пакетном сценарии T-SQL сначала я определяю переменную типа данных XML @SQLXML для хранения данных XML.
Затем я устанавливаю переменную SQL типа данных XML в список XML, который у нас есть, который включает книги SQL Server и их имена.

DECLARE @SQLXML XML SET @SQLXML = ' Pro SQL Server 2008 XML Professional SQL Server 2005 XML Программирование SQL Server 2012 Руководство программиста Pro T-SQL 2012 '

SELECT @SQLXML

Код

Последний оператор SQL Select возвращает XML-данные.

Если щелкнуть возвращенные XML-данные указанного выше запроса SQL Server, можно увидеть XML-данные, лучше отформатированные в редакторе XML в SQL Server Management Studio

<книги> Pro SQL Server 2008 XML Professional SQL Server 2005 XML Программирование SQL Server 2012 Pro T-SQL Руководство программиста 2012 г.

Код

SQL XML-запрос для преобразования XML в данные SQL

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

ВЫБЕРИТЕ title.value('.','varchar(100)') as [Название книги] FROM @SQLXML.nodes('/books/sql/book') as books(название)

Код

Как вы видите на снимке экрана ниже из Microsoft SQL Server Management Studio, когда я выполняю этот оператор SQL XML Select в SQL Server 2012 (или в любых других версиях SQL Server), текст для узлов книги в иерархии /books/sql/ будет возвращается как набор результатов этого SQL-запроса

Вы можете думать об этом запросе SQL XML следующим образом, чтобы понять его структуру:
Запрос Transact-SQL SQL Server позволяет программисту SQL считывать узлы книг в /books/sql/ иерархии XML-данных @SQLXML в воображаемую таблицу books с заголовком имени столбца.
Псевдоним части книги (название) может быть любым, установленным разработчиком SQL. Я предпочел использовать связанные имена для псевдонима.
Оператор SELECT считывает текстовое значение узла книги с title.value в списке SELECT.
Чтобы успешно прочитать XML-текст книжного узла, программист SQL должен преобразовать текст ( «.» ) в допустимый строковый тип данных, например varchar(100).

Как вы понимаете, мы не читали значения атрибутов узлов XML, таких как атрибуты id, в наших примерах данных SQL XML.
Давайте продолжим этот учебник по SQL XML, прочитав дополнительные данные XML, атрибут id книги узла XML.

Посмотрите на первый элемент в списке SELECT.
Узел XML, представленный title.value, используется еще раз.
На этот раз вместо текстовых данных узла XML («.»), которые представляют собой текстовое значение между тегами узла XML, программист SQL считывает идентификатор атрибута XML, используя нотацию @id.
И данные атрибута id книги узла XML преобразуются в числовой тип данных smallint SQL Server.

SELECT title.value('@id','smallint') as [Идентификатор книги], title.value('.','varchar(100)') as [Название книги] FROM @SQLXML.nodes(' /books/sql/book') как книги (название)

Код

Вот результат вывода вышеуказанного XML-запроса SQL Server после его выполнения на SQL Server 2012 с использованием SSMS.

Импорт XML в таблицу SQL Server в виде строк

Программисты SQL теперь готовы преобразовывать строки данных XML в SQL в соответствии со своими требованиями.
После преобразования XML-данных в строки данных XML-данные легко импортировать в таблицу SQL Server в виде новых строк с помощью команд SQL INSERT INTO или SELECT INTO.
Вот пример запроса SQL XML для вставки новых строк с помощью оператора SQL XML Select.

-- Импорт XML в новую таблицу базы данных SQL Server SELECT title.value('@id','smallint') as [Идентификатор книги], title. value('.','varchar(100)') as [ Название книги] INTO SQLBooks -- создает новую таблицу SQL для XML-данных FROM @SQLXML.nodes('/books/sql/book') as books(title)

-- ИЛИ

-- Импорт XML в существующий SQL Таблица серверов ВСТАВИТЬ В SQLBooks ([Идентификатор книги], [Название книги]) ВЫБРАТЬ title.value('@id','smallint') как [Идентификатор книги], title.value('.','varchar(100) ') как [Название книги] ИЗ @SQLXML.nodes('/books/sql/book') как книги (название)

Код

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

SQL Server

Установка SQL Server 2019
загрузить SQL Server 2019
загрузить SQL Server 2017
загрузить SQL Server 2016
загрузить SQL Server 2014
загрузить SQL Server 2012
MacOS и SQL Server 2019

Ресурсы SQL

Руководства по SQL Server
SQL Server 2012
Инструменты SQL Server
SQL Server SSIS

Запросы столбцов XML — ArcMap | Документация

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

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

Чтобы работать со значением из столбца XML в системной таблице в IBM Db2, Microsoft SQL Server или PostgreSQL, полностью извлеките документ XML из базы данных и работайте с ним локально в средстве просмотра XML или текста. Разработчики, использующие такие языки, как Java, C++ или C#, могут предпочесть читать документ в объектной модели документа (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-документе:

 
 0"
     xsi: type = "typens:GPRangeDomain2">
  Угол
  esriFieldTypeInteger
  esriMPTDefaultValue
  esriSPTDuplicate
  Действительные углы поворота
  <Владелец>харли
  359
  0

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

 -- Запрашивает базу геоданных sde-schema в SQL Server.
ВЫБРАТЬ
  Definition.value('(/GPRangeDomain2/MinValue)[1]','nvarchar(max)') AS "MinValue",
  Definition.value('(/GPRangeDomain2/MaxValue)[1]','nvarchar(max)') AS "MaxValue"
ОТ
  sde. GDB_ITEMS ВНУТРЕННЕЕ СОЕДИНЕНИЕ sde.GDB_ITEMTYPES
  ON sde.GDB_ITEMS.Type = sde.GDB_ITEMTYPES.UUID
КУДА
  sde.GDB_ITEMS.Name = 'Угол' И
  sde.GDB_ITEMTYPES.Name = 'Домен диапазона'
Минимальное значение Максимальное значение
      0 359

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

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

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

 
Материал
  esriFieldTypeString
  esriMPTDefaultValue
  esriSPTDuplicate
  Допустимые материалы для труб
  <Владелец>Эльфлад
  
   
     Чугун
     ЭК
   
   
     Ковкий чугун
     DI
   
   
     ПВХ
     ПВХ
   
   
     Асбобетон
     <Код xsi:type= "xs:string">AC
   
   
     Медь
     COP

Значения, которые обычно представляют наибольший интерес для разработчиков и администраторов, — это пары код-значение, которые имеют выражение XPath /GPCodedValueDomain2/CodedValues/CodedValue.