There is a design of the form:

<purchaseDoc> <id>...</id> <purchaseNumber>...</purchaseNumber> <lots> <lot> <lotNumber>1</lotNumber> <lotObjectInfo>...</lotObjectInfo> <customerRequirements>...</customerRequirements> <purchaseObjects>...</purchaseObjects> <requirements> <requirement> <code>...</code> <name>...</name> <content>...</content> </requirement> <requirement> <code>...</code> <name>...</name> <content>...</content> </requirement> <requirement> <code>...</code> <name>...</name> <content>...</content> </requirement> </requirements> </lot> <lot> <lotNumber>2</lotNumber> <lotObjectInfo>...</lotObjectInfo> <customerRequirements>...</customerRequirements> <purchaseObjects>...</purchaseObjects> <requirements> <requirement> <code>...</code> <name>...</name> <content>...</content> </requirement> <requirement> <code>...</code> <name>...</name> <content>...</content> </requirement> <requirement> <code>...</code> <name>...</name> <content>...</content> </requirement> </requirements> </lot> </lots> </purchaseDoc>

The number of nested lots, like the number of nested requirement , is not known. I understand how to get items in purchseDoc :

 SELECT column.value('(purchaseDoc/id) [1]', 'integer') AS 'id' FROM table

I understand how to loosen the lot element with reference to 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)

I receive after this request a table of the form

 id | lotNumber | lotObjectInfo

But I don’t understand how I can do it in order to go even further to break the requirement with reference to both the lot and purchaseDoc , that is, so that I get a table of the form:

 id | purchaseNumber | lotNumber | code | name | content

    2 answers 2

    CROSS APPLY do the most nested elements.
    lotNumber get through the path to the ancestors.

     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)
    • #t.col.nodes('purchaseDoc/lots/lot/requirements/requirement/.[1]') but with this we shouldn’t take only the first lot that can be? - Vladimir Afanasyev

    Request to get data from variable

     declare @XML xml SELECT @XML = ' <purchaseDoc> <id>1</id> <purchaseNumber>100</purchaseNumber> <lots> <lot> <lotNumber>1</lotNumber> <lotObjectInfo>lotObjectInfo_1</lotObjectInfo> <customerRequirements>customerRequirements_1</customerRequirements> <purchaseObjects>purchaseObjects_1</purchaseObjects> <requirements> <requirement> <code>111</code> <name>name111</name> <content>content111</content> </requirement> <requirement> <code>112</code> <name>name112</name> <content>content112</content> </requirement> <requirement> <code>113</code> <name>name113</name> <content>content113</content> </requirement> </requirements> </lot> <lot> <lotNumber>2</lotNumber> <lotObjectInfo>lotObjectInfo_2</lotObjectInfo> <customerRequirements>customerRequirements_2</customerRequirements> <purchaseObjects>purchaseObjects_2</purchaseObjects> <requirements> <requirement> <code>211</code> <name>name211</name> <content>content211</content> </requirement> <requirement> <code>212</code> <name>name212</name> <content>content212</content> </requirement> <requirement> <code>213</code> <name>name213</name> <content>content213</content> </requirement> </requirements> </lot> </lots> </purchaseDoc> <purchaseDoc> <id>9</id> <purchaseNumber>900</purchaseNumber> <lots> <lot> <lotNumber>91</lotNumber> <lotObjectInfo>lotObjectInfo_91</lotObjectInfo> <customerRequirements>customerRequirements_91</customerRequirements> <purchaseObjects>purchaseObjects_91</purchaseObjects> <requirements> <requirement> <code>9111</code> <name>name9111</name> <content>content9111</content> </requirement> <requirement> <code>9112</code> <name>name9112</name> <content>content912</content> </requirement> <requirement> <code>9113</code> <name>name9113</name> <content>content9113</content> </requirement> </requirements> </lot> <lot> <lotNumber>92</lotNumber> <lotObjectInfo>lotObjectInfo_92</lotObjectInfo> <customerRequirements>customerRequirements_92</customerRequirements> <purchaseObjects>purchaseObjects_92</purchaseObjects> <requirements> <requirement> <code>9211</code> <name>name9211</name> <content>content9211</content> </requirement> <requirement> <code>9212</code> <name>name9212</name> <content>content9212</content> </requirement> <requirement> <code>9213</code> <name>name9213</name> <content>content9213</content> </requirement> </requirements> </lot> </lots> </purchaseDoc> ' 

    


     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