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

#sql #sql_server #xml #запрос

Имеется конструкция вида:


    ...
    ...
    
        
            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

    

Ответы

Ответ 1

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)


Ответ 2

Запрос для получения данных из переменной




declare @XML xml

SELECT @XML = 
'

    1
    100
    
        
            1
            lotObjectInfo_1
            customerRequirements_1
            purchaseObjects_1
            
                
                    111
                    name111
                    content111
                
                
                    112
                    name112
                    content112
                
                
                    113
                    name113
                    content113
                
            
        
        
            2
            lotObjectInfo_2
            customerRequirements_2
            purchaseObjects_2
            
                
                    211
                    name211
                    content211
                
                
                    212
                    name212
                    content212
                
                
                    213
                    name213
                    content213
                
            
        
    


    9
    900
    
        
            91
            lotObjectInfo_91
            customerRequirements_91
            purchaseObjects_91
            
                
                    9111
                    name9111
                    content9111
                
                
                    9112
                    name9112
                    content912
                
                
                    9113
                    name9113
                    content9113
                
            
        
        
            92
            lotObjectInfo_92
            customerRequirements_92
            purchaseObjects_92
            
                
                    9211
                    name9211
                    content9211
                
                
                    9212
                    name9212
                    content9212
                
                
                    9213
                    name9213
                    content9213
                
            
        
    

'

  
  

SELECT 
  y.requirement.value('(../../../.././id)[1]', 'int') as id,
  y.requirement.value('(../.././lotNumber)[1]', 'int') as lotNumber,
    y.requirement.value('(code)[1]', 'INT') AS code,
    y.requirement.value('(name)[1]', 'varchar(100)') AS name,
    y.requirement.value('(content)[1]', 'varchar(100)') AS content
FROM @xml.nodes('.') as g(r)
   CROSS APPLY     @xml.nodes('/purchaseDoc/lots/lot/requirements/requirement') y(requirement)
GO



id | lotNumber | code | name     | content    
:- | --------: | ---: | :------- | :----------
1  |         1 |  111 | name111  | content111 
1  |         1 |  112 | name112  | content112 
1  |         1 |  113 | name113  | content113 
1  |         2 |  211 | name211  | content211 
1  |         2 |  212 | name212  | content212 
1  |         2 |  213 | name213  | content213 
9  |        91 | 9111 | name9111 | content9111
9  |        91 | 9112 | name9112 | content912 
9  |        91 | 9113 | name9113 | content9113
9  |        92 | 9211 | name9211 | content9211
9  |        92 | 9212 | name9212 | content9212
9  |        92 | 9213 | name9213 | content9213