取决于您要执行的操作…如果您知道要抓取2个“ Address_Components”，则可以按如下方式修改查询：
;WITH XMLNAMESPACES(DEFAULT 'urn:com.workday.report/Countries_and_Their_Address_Components_Summary')
select
xmldata.[ISO], xmldata.[Component 1], xmldata.[Component 2], xmldata.[required]
from @inputxml
cross apply (
select
[ISO] = xmldata.value('(Country/ID)[3]', 'VARCHAR(MAX)'),
[Component 1] = xmldata.value('(Address_Components/Address_Component/ID)[2]', 'VARCHAR(MAX)'),
[Component 2] = xmldata.value('(Address_Components[2]/Address_Component/ID)[2]', 'VARCHAR(MAX)'),
[required] = xmldata.value('(Address_Components/required)[1]', 'INT')
from x.nodes('/Report_Data/Report_Entry') Z1(xmldata)
) xmldata
结果看起来像这样：
ISO Component 1 Component 2 required
----- ------------------------- ------------------------- -----------
AFG ADDRESS_LINE_1_LOCAL ADDRESS_LINE_2_LOCAL 0
但是，如果可以有任意数量的“ Address_Components”，并且想要将它们捕获到单独的记录中，则可以像下面这样重写查询：
;WITH XMLNAMESPACES(DEFAULT 'urn:com.workday.report/Countries_and_Their_Address_Components_Summary')
select
[ISO] = Report_Entry.x.value('(Country/ID)[3]', 'VARCHAR(MAX)')
, [Component] = Address_Components.x.value('(Address_Component/ID)[2]', 'VARCHAR(MAX)')
, [required] = Address_Components.x.value('(required)[1]', 'INT')
from @inputxml
cross apply x.nodes('/Report_Data/Report_Entry') Report_Entry(x)
cross apply Report_Entry.x.nodes('./Address_Components') Address_Components (x)
结果看起来像这样：
ISO Component required
----- ------------------------- -----------
AFG ADDRESS_LINE_1_LOCAL 0
AFG ADDRESS_LINE_2_LOCAL 0
602392714
清零编程群