Как парсить xml sql

Парсинг XML в T-SQL запросе

как вытащить остальные значения ? я так понимаю, что проблема в //*[not(*)] ?

Отслеживать

задан 17 окт 2019 в 8:34

23 4 4 бронзовых знака

x.y.value(‘@Text’, ‘VARCHAR(MAX)’) as b

17 окт 2019 в 8:42

при таком решении теряется значение DiscoveryScriptBody, и не выдает вторые значения к примеру x.y.value(‘@Name’, ‘VARCHAR(MAX)’) as b Name=»SEP» Description=»des_sep» вытащит только SEP

17 окт 2019 в 10:02

Парсер xml на sql. Теперь я видел всё.

21 окт 2019 в 6:38

1 ответ 1

Сортировка: Сброс на вариант по умолчанию

DECLARE @xml xml; SET @xml = . ; WITH XMLNAMESPACES( 'http://schemas.com' AS ns, 'http://schemas.microsoft.com' AS ms ) SELECT cir.x.value('@Name', 'nvarchar(100)') AS Name, cir.x.value('@Description', 'nvarchar(100)') AS Description, confi.x.value('@CreatedBy', 'nvarchar(100)') AS CreatedBy, confi.x.value('@DateCreated', 'datetime2(0)') AS DateCreated, digest.x.value('(ms:Annotation/ms:DisplayName/@Text)[1]', 'nvarchar(100)') AS DisplayName, digest.x.value('(ms:Annotation/ms:Description/@Text)[1]', 'nvarchar(100)') AS Description, rcs.x.value('(ms:Annotation/ms:DisplayName/@Text)[1]', 'nvarchar(100)') AS DisplayName, rcs.x.value('(ms:Annotation/ms:DisplayName/@ResourceId)[1]', 'nvarchar(100)') AS ResourceId, src.x.value('(ns:DiscoveryScriptBody/text())[1]', 'nvarchar(4000)') AS DiscoveryScriptBody FROM @xml.nodes('CIR') cir(x) OUTER APPLY cir.x.nodes('confi[1]') confi(x) OUTER APPLY confi.x.nodes('(xml/ns:DesiredConfigurationDigest)[1]') digest(x) OUTER APPLY digest.x.nodes('(ns:Settings/ns:RootComplexSetting)[1]') rcs(x) OUTER APPLY rcs.x.nodes('(ns:ScriptDiscoverySource)[1]') src(x);

WITH XMLNAMESPACES( 'http://schemas.com' AS ns, 'http://schemas.microsoft.com' AS ms ) SELECT cir.x.value('@Name', 'nvarchar(100)') AS Name, cir.x.value('@Description', 'nvarchar(100)') AS Description, cir.x.value('(confi/@CreatedBy)[1]', 'nvarchar(100)') AS CreatedBy, cir.x.value('(confi/@DateCreated)[1]', 'datetime2(0)') AS DateCreated, da.x.value('(ms:DisplayName/@Text)[1]', 'nvarchar(100)') AS DisplayName, da.x.value('(ms:Description/@Text)[1]', 'nvarchar(100)') AS Description, rcsa.x.value('(ms:DisplayName/@Text)[1]', 'nvarchar(100)') AS DisplayName, rcsa.x.value('(ms:DisplayName/@ResourceId)[1]', 'nvarchar(100)') AS ResourceId, cir.x.value('(.//ns:DiscoveryScriptBody/text())[1]', 'nvarchar(4000)') AS DiscoveryScriptBody FROM @xml.nodes('CIR') cir(x) OUTER APPLY cir.x.nodes('(.//ns:DesiredConfigurationDigest/ms:Annotation)[1]') da(x) OUTER APPLY cir.x.nodes('(.//ns:RootComplexSetting/ms:Annotation)[1]') rcsa(x);

Если эффективность критична, то лучше сравнить оба вариант на конкретных данных.

[XML] Парсинг необычной xml

Столкнулся с необычной xml-кой, которую по долгу службы необходимо парсить в mssql. К сожалению, не смог найти решение, надеюсь кто-нибудь сможет подсказать направление решения данной проблемы.

1 2 3 4 5 6 7 8 9 10 11 12

 version="1.0" encoding="UTF-8"?>  xmlns:mbtrp="mbtrp"> >331548745 > >SV_45689975 > >StR174856 > >5f005c0b-2581-4bc7-a694-c93a3d821d6e > >STATUS_REQ_OK > >2011-07-15T13:38:32 > >TORREX > >SELECT_SMC > >REFUSE > >

Пробовал и с помощью XMLNAMESPACES и через обычные ноды — ни в какую. Мне необходимо достать значения минимум из 2х тегов — messageType и state

Лучшие ответы ( 1 )
94731 / 64177 / 26122
Регистрация: 12.04.2006
Сообщений: 116,782
Ответы с готовыми решениями:

Процедура, принимает строку (path) к файлу xml. Считываем его и возвращает xml
Добрый день. Помогите написать процедуру которая принимает строку в которой храниться путь к.

Выгрузка в XML файл результатов запроса. Создание xml схемы с имеющегося xml файла
Доброго времени суток. Имеется необходимый для загрузки пример XML файла и из него необходимо.

Парсинг XML-файла с помощью LINQ to XML
Здрасивуйте. Трабл никак не могу понять в чем дело не могу считать инфу с XML login, getWorkersOUs.

3363 / 2059 / 736
Регистрация: 02.06.2013
Сообщений: 5,044

Лучший ответ

Сообщение было отмечено Zodt как решение

Решение

MSSQL не поддерживает UTF-8. Поэтому как-то так

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29

declare @s varchar(max) = '  331548745 SV_45689975 StR174856 5f005c0b-2581-4bc7-a694-c93a3d821d6e STATUS_REQ_OK 2011-07-15T13:38:32 TORREX SELECT_SMC REFUSE '; declare @x xml; select @x = replace(@s, 'encoding="UTF-8"', ''); with xmlnamespaces('mbtrp' as mbtrp) select t.n.value('messageType[1]', 'varchar(100)'), t.n.value('state[1]', 'varchar(100)') from @x.nodes('mbtrp:response') t(n); select t.n.value('messageType[1]', 'varchar(100)'), t.n.value('state[1]', 'varchar(100)') from @x.nodes('*:response') t(n);

Инструкция OPENXML (SQL Server)

OPENXML — это ключевое слово Transact-SQL, которое предоставляет набор строк над XML-документами в памяти, похожими на таблицу или представление. OPENXML позволяет получить доступ к XML-данным, как будто это реляционный набор строк. Это делается при помощи представления внутреннего отображения XML-документа в виде набора строк. Записи в наборе строк могут храниться в таблицах базы данных.

OPENXML может использоваться в инструкциях SELECT и SELECT INTO в любых позициях, где в качестве источника могут присутствовать поставщики наборов строк, представления или функция OPENROWSET. Сведения о синтаксисе OPENXML см. в разделе OPENXML (Transact-SQL).

Чтобы писать запросы к XML-документу с использованием OPENXML, необходимо сначала вызвать хранимую процедуру sp_xml_preparedocument. Таким образом производится синтаксический анализ XML-документа и возвращается дескриптор для проанализированного документа, готового к использованию. Проанализированный документ является представлением дерева объектной модели документа (DOM) различных узлов в XML-документе. Дескриптор документа передается OPENXML. Затем инструкция OPENXML выдает представление документа в виде набора строк, основываясь на переданных ей аргументах.

Хранимая процедураsp_xml_preparedocument использует обновленную под SQL версию средства синтаксического анализа MSXML, Msxmlsql.dll. Эта версия средства синтаксического анализа MSXML была разработана для поддержки SQL Server и обеспечения обратной совместимости с MSXML версии 2.6.

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

На следующей иллюстрации показан этот процесс.

Обратите внимание на то, что для понимания OPENXML необходимо иметь общее представление о запросах XPath и XML. Дополнительные сведения о поддержке XPath в SQL Server см. в разделе Использование запросов XPath в SQLXML 4.0.

OpenXML позволяет параметризовать шаблоны XPath столбцов и строк как переменные. Такая параметризация может привести к включению выражений XPath, если разработчик допускает демонстрацию параметризации внешним пользователям (например, если аргументы предоставляются через вызываемую извне хранимую процедуру). Чтобы избежать подобных потенциально опасных ситуаций, рекомендуется никогда не допускать демонстрации аргументов XPath внешним вызывающим программам.

пример

В следующем примере показано применение процедуры OPENXML в инструкции INSERT и инструкции SELECT . Образец XML-документа содержит элементы и .

Сначала вызывается хранимая процедура sp_xml_preparedocument для проведения синтаксического анализа XML-документа. Проанализированный документ является древовидным представлением узлов (элементов, атрибутов, текста и комментариев) в XML-документе. OPENXML ссылается на этот проанализированный XML-документ и выдает представление всех частей этого XML-документа в виде набора строк. Инструкция INSERT , использующая функцию OPENXML , может вставлять данные из такого набора строк в таблицу базы данных. Можно вызывать функцию OPENXML несколько раз, получая и обрабатывая представление в виде набора строк различных частей XML-документа. Например, их можно вставить в различные таблицы. Данный процесс также называют разделение XML-данных по таблицам.

В следующем примере XML-документ разрезается таким образом, что элементы сохраняются в таблице Customers , а элементы сохраняются в таблице Orders с помощью двух инструкций INSERT . Этот пример также демонстрирует инструкцию SELECT , использующую функцию OPENXML , которая получает элементы CustomerID и OrderDate из XML-документа. Последним шагом обработки является повторный вызов процедуры sp_xml_removedocument . Это позволяет освободить память, выделенную для внутреннего древовидного представления XML, создаваемого в фазе синтаксического анализа.

-- Create tables for later population using OPENXML. CREATE TABLE Customers (CustomerID varchar(20) primary key, ContactName varchar(20), CompanyName varchar(20)); GO CREATE TABLE Orders( CustomerID varchar(20), OrderDate datetime); GO DECLARE @docHandle int; DECLARE @xmlDocument nvarchar(max); -- or xml type SET @xmlDocument = N'     No Orders yet! '; EXEC sp_xml_preparedocument @docHandle OUTPUT, @xmlDocument; -- Use OPENXML to provide rowset consisting of customer data. INSERT Customers SELECT * FROM OPENXML(@docHandle, N'/ROOT/Customers') WITH Customers; -- Use OPENXML to provide rowset consisting of order data. INSERT Orders SELECT * FROM OPENXML(@docHandle, N'//Orders') WITH Orders; -- Using OPENXML in a SELECT statement. SELECT * FROM OPENXML(@docHandle, N'/ROOT/Customers/Orders') WITH (CustomerID nchar(5) '../@CustomerID', OrderDate datetime); -- Remove the internal representation of the XML document. EXEC sp_xml_removedocument @docHandle;

На следующем рисунке показано XML-дерево, полученное в результате анализа предыдущего XML-документа и созданное с помощью хранимой процедуры sp_xml_preparedocument.

Параметры OPENXML

К числу аргументов OPENXML относятся:

дескриптор XML-документа (idoc);
выражение XPath для идентификации узлов, которые должны быть сопоставлены со строками (rowpattern);
описание набора строк, который должен быть создан;
сопоставление столбцов набора строк и узлов XML;

Дескриптор XML-документа (idoc)

Дескриптор документа возвращается хранимой процедурой sp_xml_preparedocument .

Выражение XPath для идентификации узлов, обрабатываемых (rowpattern)

Выражение XPath, указанное аргументом rowpattern , распознает набор узлов в XML-документе. Каждый узел, распознанный rowpattern , соотносится с отдельной строкой в наборе строк, созданном OPENXML.

Узлами, распознанными выражением XPath, могут быть любые узлы XML в XML-документе. Если rowpattern идентифицирует набор элементов в XML-документе, то для каждого узла элемента имеется одна строка в наборе строк. Например, если rowpattern приводит к атрибуту, создается строка для каждого узла атрибута, выбранного rowpattern.

Описание создаваемого набора строк

Схема набора строк используется OPENXML для создания результирующего набора строк. Можно использовать нижеследующие параметры при указании схемы набора строк.

Использование формата таблицы edge

Для указания схемы набора строк следует использовать формат краевой таблицы. Не используйте предложение WITH.

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

Краевые таблицы представляют внутри отдельной таблицы подробную структуру XML-документа. Структура включает в себя имена элементов и атрибутов, иерархию документа, пространства имен и инструкции по обработке. Формат пограничной таблицы позволяет получить дополнительные сведения, которые не предоставляются с помощью метапродажа. Дополнительные сведения о метасвойствах см. в разделе Specify Metaproperties in OPENXML.

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

Используя краевую таблицу, можно писать хранимые процедуры, которые рассматривают XML-документы как входные данные большого двоичного объекта (BLOB), создают краевую таблицу, а затем извлекают и анализируют документ на более детальном уровне. Этот детальный уровень может включать в себя выявление иерархии документа, имен элементов и атрибутов, пространств имен и инструкций по обработке.

Граничная таблица также может служить форматом хранения XML-документов, если сопоставление с другими реляционными форматами не является логическим, а поле ntext не предоставляет достаточно структурных сведений.

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

В представленной ниже таблице описывается структура граничной таблицы.

Ниже приведены значения, которые могут быть представлены в этом столбце для указания типа узла.

1 = Узел элемента

2 = Узел атрибута

3 = Узел текста

4 = Узел раздела CDATA

5 = Узел ссылки на сущность

6 = Узел сущности

7 = Узел инструкции по обработке

8 = Узел комментария

9 = Узел документа

10 = Узел типа документа

11 = Узел фрагмента документа

12 = Узел нотации

Использование предложения WITH для указания существующей таблицы

Можно использовать предложение WITH, чтобы указать имя существующей таблицы. Чтобы сделать это, просто укажите имя существующей таблицы, схема которой может быть использована OPENXML для создания набора строк.

Использование предложения WITH для указания схемы

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

Можно указать шаблон столбца, применив аргумент ColPattern в SchemaDeclaration. Указанный шаблон столбца используется для сопоставления столбца набора строк с узлом XML, определенным rowpattern, а также используется для определения типа сопоставления.

Если colPattern не указан для столбца, столбец набора строк сопоставляется с XML-узлом с тем же именем, в зависимости от сопоставления, указанного параметром флагов . Однако если аргумент ColPattern задан как часть указания схемы в предложении WITH, он переопределяет сопоставление, указанное аргументом flags .

Сопоставление между столбцами набора строк и узлами XML

В инструкции OPENXML можно при необходимости указать тип сопоставления, например атрибутивное или элементное, между столбцами набора строк и узлами XML, определенными посредством rowpattern. Эти данные используются в преобразовании между узлами XML и столбцами набора строк.

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

Используя аргумент flags Указание сопоставления посредством аргумента flags предполагает соответствие имен, в котором узлы XML сопоставляются с соответствующими столбцами набора строк, имеющими то же имя.
Используя аргумент ColPatternColPattern, выражение XPath, указывается как часть SchemaDeclaration в предложении WITH. Сопоставлением, указанным в ColPattern , перекрывается сопоставление, указанное аргументом flags . ColPattern можно использовать для указания типа сопоставления, такого как атрибутивный или элементный, который переопределяет или расширяет сопоставление по умолчанию, указанное аргументом flags. ColPattern указывается в следующих ситуациях:
- Имя столбца в наборе строк отличается от имени атрибута или элемента, с которым оно сопоставлено. В этом случае ColPattern используется для определения имени элемента или атрибута в формате XML, с которым сопоставлен столбец набора строк.
- Необходимо сопоставить атрибут метасвойства со столбцом. В этом случае ColPattern используется для определения метасвойства, с которым сопоставлен столбец набора строк. Дополнительные сведения о том, как использовать метасвойства, см. в разделе Определение метасвойств в инструкции OPENXML.
Оба аргумента: и flags , и ColPattern , являются необязательными. Если сопоставление не указано, предполагается использование атрибутивного сопоставления, которое является значением по умолчанию для параметра flags .

Сопоставление с атрибутами

При присвоении параметру flags в OPENXML значения 1 (XML_ATTRIBUTES) указывается атрибутивное сопоставление. Если аргумент flags содержит XML_ ATTRIBUTES, демонстрируемый набор строк предоставляет или потребляет строки, где каждый элемент XML представлен в виде строки. Атрибуты XML сопоставляются с атрибутами, определенными в schemaDeclaration или предоставляемыми предложением TableName предложения WITH на основе соответствия имени. Соответствие имен означает, что атрибуты XML, имеющие определенное имя, хранятся в столбце набора строк с тем же именем.

Если имя столбца отличается от имени атрибута, с которым оно сопоставлено, должен быть указан аргумент ColPattern .

Если XML-атрибут имеет квалификатор пространства имен, имя столбца в наборе строк должно также иметь квалификатор.

Сопоставление с элементом

При присвоении параметру flags в OPENXML значения 2 (XML_ELEMENTS) указывается элементное сопоставление. Это похоже на сопоставление с атрибутами , за исключением следующих различий:
- Соответствие имен в примере сопоставления: при сопоставлении столбца с элементом XML с тем же именем выбираются простые подэлементы, если не указан шаблон на уровне столбца. В процессе получения, если подэлемент является составным, поскольку содержит дополнительные подэлементы, столбцу присваивается значение NULL. При этом значения атрибутов подэлементов пропускаются.
- В случае с несколькими подэлементами, имеющими одно и то же имя, возвращается первый узел.
См. также
- sp_xml_preparedocument (Transact-SQL)
- sp_xml_removedocument (Transact-SQL)
- OPENXML (Transact-SQL)
- XML-данные (SQL Server)
XML, XQuery и тройная печаль с производительностью

Поездка в Днепропетровск на встречу Dnepr SQL User Group, хронический недосып последние пару дней, но приятный бонус по приезду в Харьков… Зимняя погодка, которая мотивирует на написание чего-то интересного…

Уже давно в планах было рассказать про «подводные камни» при работе с XML и XQuery, которые могут приводить к каверзным проблемам с производительностью.

Для тех кто часто использует SQL Server, XQuery и любит парсить значения из XML рекомендуется ознакомиться с нижеследующим материалом…

Для начала сгенерируем тестовый XML на котором будем проводить эксперименты:
```
USE AdventureWorks2012 GO IF OBJECT_ID('tempdb.dbo.##temp') IS NOT NULL DROP TABLE ##temp GO SELECT val = ( SELECT [@obj_id] = o.[object_id] , [@obj_name] = o.name , [@sch_name] = s.name , ( SELECT i.name, i.column_id, i.user_type_id, i.is_nullable, i.is_identity FROM sys.all_columns i WHERE i.[object_id] = o.[object_id] FOR XML AUTO, TYPE ) FROM sys.all_objects o JOIN sys.schemas s ON o.[schema_id] = s.[schema_id] WHERE o.[type] IN ('U', 'V') FOR XML PATH('obj'), ROOT('objects') ) INTO ##temp DECLARE @sql NVARCHAR(4000) = 'bcp "SELECT * FROM ##temp" queryout "D:\sample.xml" -S ' + @@servername + ' -T -w -r -t' EXEC sys.xp_cmdshell @sql IF OBJECT_ID('tempdb.dbo.##temp') IS NOT NULL DROP TABLE ##temp 
```
Для тех, у кого xp_cmdshell отключена нужно выполнить:
```
EXEC sp_configure 'show advanced options', 1 GO RECONFIGURE GO EXEC sp_configure 'xp_cmdshell', 1 GO RECONFIGURE GO 
```
В итоге по указанному пути у нас будет создан файл с такой вот структурой:

Теперь начнем заборные эксперименты…

Как наиболее эффективно загрузить данные из XML? Наверное, не нужно открывать файл блокнотом, копировать содержимое и вставлять в переменную… Думаю, что правильнее будет воспользоваться OPENROWSET:
```
DECLARE @xml XML SELECT @xml = BulkColumn FROM OPENROWSET(BULK 'D:\sample.xml', SINGLE_BLOB) x SELECT @xml 
```
Но тут есть забавный подвох. Как оказалось, совмещение операций загрузки и парсинга значений из XML может приводить к существенному снижению производительности. Допустим нам нужно получить значения obj_id из ранее созданного файла:
```
;WITH cte AS ( SELECT x = CAST(BulkColumn AS XML) FROM OPENROWSET(BULK 'D:\sample.xml', SINGLE_BLOB) x ) SELECT t.c.value('@obj_id', 'INT') FROM cte CROSS APPLY x.nodes('objects/obj') t(c) 
```
На моей машине этот запрос выполняется очень долго:
```
(495 row(s) affected) Table 'Worktable'. Scan count 0, logical reads 20788, . lob logical reads 7817781, . lob read-ahead reads 1022368. SQL Server Execution Times: CPU time = 53688 ms, elapsed time = 53911 ms. 
```
Попробуем разделить загрузку и парсинг:
```
DECLARE @xml XML SELECT @xml = BulkColumn FROM OPENROWSET(BULK 'D:\sample.xml', SINGLE_BLOB) x SELECT t.c.value('@obj_id', 'INT') FROM @xml.nodes('objects/obj') t(c) 
```
Все отработало очень быстро:
```
(1 row(s) affected) Table 'Worktable'. Scan count 0, logical reads 7, . lob logical reads 2691, . lob read-ahead reads 344. SQL Server Execution Times: CPU time = 15 ms, elapsed time = 51 ms. (495 row(s) affected) SQL Server Execution Times: CPU time = 47 ms, elapsed time = 125 ms. 
```
Так в чем же была проблема? Давайте проанализируем план выполнения:

Как оказалось, проблема кроется в преобразовании типов, поэтому старайтесь изначально передавать в функцию nodes параметр в типе XML.

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

Для наглядности сказанного покажу, что в данном запросе функция value будет выполнена дважды:
```
SELECT t.c.value('@obj_id', 'INT') FROM @xml.nodes('objects/obj') t(c) WHERE t.c.value('@obj_id', 'INT') < 0 
```
```
(404 row(s) affected) SQL Server Execution Times: CPU time = 116 ms, elapsed time = 120 ms. 
```
Данный нюанс может снижать производительность, поэтому рекомендуется сокращать вызовы функций:
```
SELECT * FROM ( SELECT 'INT') FROM @xml.nodes('objects/obj') t(c) ) t WHERE t.id < 0 
```
```
(404 row(s) affected) SQL Server Execution Times: CPU time = 62 ms, elapsed time = 74 ms. 
```
Как вариант можно фильтровать еще так:
```
SELECT t.c.value('@obj_id', 'INT') FROM @xml.nodes('objects/obj[@obj_id < 0]') t(c) 
```
```
(404 row(s) affected) SQL Server Execution Times: CPU time = 110 ms, elapsed time = 119 ms. 
```
но говорить о существенно выигрыше не приходится. Хотя QueryCost говорит об обратном:

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

И самый интересный пример на закуску… Есть еще одна ОЧЕНЬ важная особенность при парсинге из XML. Выполним запрос:
```
SELECT t.c.value('../@obj_name', 'SYSNAME') , t.c.value('@name', 'SYSNAME') FROM @xml.nodes('objects/obj/*') t(c) 
```
и посмотрим на время выполнения, которое может устроить только тех, кто уже никуда не торопится:
```
(5273 row(s) affected) SQL Server Execution Times: CPU time = 66578 ms, elapsed time = 66714 ms. 
```
Почему это происходит? SQL Server сервер имеет проблемы в операциях чтения родительских узлов из дочерних (если проще говорить, то SQL Server тяжело «смотреть назад»):

Как же нам в таком случае быть? Все очень просто… начинать чтение с родительских узлов и вычитывать дочерние с помощью CROSS/OUTER APPLY:
```
SELECT t.c.value('@obj_name', 'SYSNAME') , t2.c2.value('@name', 'SYSNAME') FROM @xml.nodes('objects/obj') t(c) CROSS APPLY t.c.nodes('*') t2(c2) 
```
```
(5273 row(s) affected) SQL Server Execution Times: CPU time = 156 ms, elapsed time = 184 ms. 
```
Еще интересно рассмотреть ситуацию, когда нам нужно посмотреть на 2 уровня выше. Проблема со чтением родительского элемента у меня не воспроизвелась:
```
USE AdventureWorks2012 GO DECLARE @xml XML SELECT @xml = ( SELECT [@obj_name] = o.name , [columns] = ( SELECT i.name FROM sys.all_columns i WHERE i.[object_id] = o.[object_id] FOR XML AUTO, TYPE ) FROM sys.all_objects o WHERE o.[type] IN ('U', 'V') FOR XML PATH('obj') ) SELECT t.c.value('../../@obj_name', 'SYSNAME') , t.c.value('@name', 'SYSNAME') FROM @xml.nodes('obj/columns/*') t(c) 
```
Еще хотел упомянуть об одной интересной особенности. Проблем с чтением родительских элементов OPENXML не имеет:
```
DECLARE @xml XML , @idoc INT SELECT @xml = BulkColumn FROM OPENROWSET(BULK 'D:\sample.xml', SINGLE_BLOB) x EXEC sys.sp_xml_preparedocument @idoc OUTPUT, @xml SELECT * FROM OPENXML(@idoc, '/objects/obj/*') WITH ( name SYSNAME '../@obj_name', col SYSNAME '@name' ) EXEC sys.sp_xml_removedocument @idoc 
```
```
(5273 row(s) affected) SQL Server Execution Times: CPU time = 47 ms, elapsed time = 137 ms. 
```
Но не нужно теперь думать, что OPENXML имеет явные преимущества над XQuery. У OPENXML тоже хватает косяков. Например, если мы забываем вызывать sp_xml_removedocument, то могут возникать сильные утечки памяти.

Все тестировалось на SQL Server 2012 SP3 (11.00.6020).

Если хотите поделиться этой статьей с англоязычной аудиторией:
XML, XQuery & Perfomance Issues
Похожие публикации: