我有可以从XML文件中选择一些值的工作代码。问题是我有多个具有相同名称的节点。

这是XML的代码段：

<wd:Report_Data xmlns:wd="urn:com.workday.report/Countries_and_Their_Address_Components_Summary">
  <wd:Report_Entry>
    <wd:Country wd:Descriptor="Afghanistan">
      <wd:ID wd:type="WID">db69b722446c11de98360015c5e6daf6</wd:ID>
      <wd:ID wd:type="ISO_3166-1_Alpha-2_Code">AF</wd:ID>
      <wd:ID wd:type="ISO_3166-1_Alpha-3_Code">AFG</wd:ID>
      <wd:ID wd:type="ISO_3166-1_Numeric-3_Code">4</wd:ID>
    </wd:Country>
    <wd:Address_Format_Type wd:Descriptor="Basic">
      <wd:ID wd:type="WID">4516bf435611423ea4ee72fa842572a0</wd:ID>
    </wd:Address_Format_Type>
    <wd:Local>1</wd:Local>
    <wd:Address_Components>
      <wd:Address_Component wd:Descriptor="Address Line 1 - Local">
        <wd:ID wd:type="WID">12d859b8df024175a111da2e088250fb</wd:ID>
        <wd:ID wd:type="Address_Component_Type_ID">ADDRESS_LINE_1_LOCAL</wd:ID>
      </wd:Address_Component>
      <wd:Order>a</wd:Order>
      <wd:Required>0</wd:Required>
    </wd:Address_Components>
    <wd:Address_Components>
      <wd:Address_Component wd:Descriptor="Address Line 2 - Local">
        <wd:ID wd:type="WID">85a6ab9412c44dd9a71a7e4760bf17fb</wd:ID>
        <wd:ID wd:type="Address_Component_Type_ID">ADDRESS_LINE_2_LOCAL</wd:ID>
      </wd:Address_Component>
      <wd:Order>b</wd:Order>
      <wd:Required>0</wd:Required>
    </wd:Address_Components>

我的SQL如下：

declare @inputxml table (x xml)

insert @inputxml
select x
from OPENROWSET(BULK 'C:\ParallelTool\addcomp.xml', SINGLE_BLOB) As T(x)

;WITH XMLNAMESPACES(DEFAULT 'urn:com.workday.report/Countries_and_Their_Address_Components_Summary')
    select 
        xmldata.[ISO], xmldata.[Component 1], xmldata.[Component 2], xmldata.[Required]
    into dbo.WD
    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/Address_Component/ID)[2]', 'VARCHAR(MAX)'),
            [Required] = xmldata.value('(Address_Components/Required)[1]', 'INT')
        from x.nodes('/Report_Data/Report_Entry') Z1(xmldata)
    ) xmldata

我无法获得所需的地方是[组件2]。我想基本上选择文件中“
Address_Component_Type_ID”的全部，但是它们都被命名为相同的名称，并且在其他节点下也被命名为相同的名称。如何在SQL中指定以获取所有组件类型？感谢您的光临！