Запрос к множественным элементам в XML в MSSQL

Имеется конструкция вида:
... ... 1 ... ... ... ... ... ... ... ... ... ... ... ... 2 ... ... ... ... ... ... ... ... ... ... ... ...
Количество вложенных lot, как и количество вложенных requirement, не известно. Я понимаю, как получить элементы в purchseDoc
SELECT column.value('(purchaseDoc/id) [1]', 'integer') AS 'id' FROM table
Понимаю, как разможить элемент lot с привязкой к purchaseDoc
SELECT t.column.value('(purchaseDoc/id)[1]', 'integer') AS Id, nodes.setting.value('lotNumber[1]', 'varchar(100)'), nodes.setting.value('lotObjectInfo[1]', 'varchar(100)') FROM table t CROSS APPLY t.column.nodes('purchaseDoc/lots/lot/.[1]') nodes(setting)
Получаю после данного запроса таблицу вида:
id | lotNumber | lotObjectInfo
Но не понимаю, как мне сделать так, чтобы еще дальше углубиться, чтобы разбить requirement с привязкой как к lot, так и purchaseDoc, то есть чтобы я получил таблицу вида:
id | purchaseNumber | lotNumber | code | name | content

Ответ

CROSS APPLY делаем по самым вложенным элементам. lotNumber получаем через путь к предкам.
SELECT #t.col.value('(purchaseDoc/id)[1]', 'integer') AS Id, #t.col.value('(purchaseDoc/purchaseNumber)[1]', 'integer') AS purchaseNumber, nodes.setting.value('../../lotNumber[1]', 'varchar(100)') AS lotNumber, nodes.setting.value('code[1]', 'varchar(100)') AS code, nodes.setting.value('name[1]', 'varchar(100)') AS [name], nodes.setting.value('content[1]', 'varchar(100)') AS content FROM #t CROSS APPLY #t.col.nodes('purchaseDoc/lots/lot/requirements/requirement/.[1]') nodes(setting)