title: XML to SQL - Selecting multiple nodes with the same name
body: <p>I have working code that can select a few values from an XML file. The problem is that I have multiple nodes with the same name.</p>
  
  <p>Here is a snippet of the XML:</p>
  
  <pre><code>&lt;wd:Report_Data xmlns:wd="urn:com.workday.report/Countries_and_Their_Address_Components_Summary"&gt;
    &lt;wd:Report_Entry&gt;
      &lt;wd:Country wd:Descriptor="Afghanistan"&gt;
        &lt;wd:ID wd:type="WID"&gt;db69b722446c11de98360015c5e6daf6&lt;/wd:ID&gt;
        &lt;wd:ID wd:type="ISO_3166-1_Alpha-2_Code"&gt;AF&lt;/wd:ID&gt;
        &lt;wd:ID wd:type="ISO_3166-1_Alpha-3_Code"&gt;AFG&lt;/wd:ID&gt;
        &lt;wd:ID wd:type="ISO_3166-1_Numeric-3_Code"&gt;4&lt;/wd:ID&gt;
      &lt;/wd:Country&gt;
      &lt;wd:Address_Format_Type wd:Descriptor="Basic"&gt;
        &lt;wd:ID wd:type="WID"&gt;4516bf435611423ea4ee72fa842572a0&lt;/wd:ID&gt;
      &lt;/wd:Address_Format_Type&gt;
      &lt;wd:Local&gt;1&lt;/wd:Local&gt;
      &lt;wd:Address_Components&gt;
        &lt;wd:Address_Component wd:Descriptor="Address Line 1 - Local"&gt;
          &lt;wd:ID wd:type="WID"&gt;12d859b8df024175a111da2e088250fb&lt;/wd:ID&gt;
          &lt;wd:ID wd:type="Address_Component_Type_ID"&gt;ADDRESS_LINE_1_LOCAL&lt;/wd:ID&gt;
        &lt;/wd:Address_Component&gt;
        &lt;wd:Order&gt;a&lt;/wd:Order&gt;
        &lt;wd:Required&gt;0&lt;/wd:Required&gt;
      &lt;/wd:Address_Components&gt;
      &lt;wd:Address_Components&gt;
        &lt;wd:Address_Component wd:Descriptor="Address Line 2 - Local"&gt;
          &lt;wd:ID wd:type="WID"&gt;85a6ab9412c44dd9a71a7e4760bf17fb&lt;/wd:ID&gt;
          &lt;wd:ID wd:type="Address_Component_Type_ID"&gt;ADDRESS_LINE_2_LOCAL&lt;/wd:ID&gt;
        &lt;/wd:Address_Component&gt;
        &lt;wd:Order&gt;b&lt;/wd:Order&gt;
        &lt;wd:Required&gt;0&lt;/wd:Required&gt;
      &lt;/wd:Address_Components&gt;
  </code></pre>
  
  <p>My SQL is the following:</p>
  
  <pre><code>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
  </code></pre>
  
  <p>Where I can't get what I need is the [Component 2]. I want to basically select ALL of the "Address_Component_Type_ID" in the file, but they are all named the same and under other nodes that are named the same. How can I specify in my SQL to grab all of the Component Types? Thank you for looking!</p>