title: Extract attribute from XML in SQL Server
body: <p>Given this XML:</p>
  
  <pre><code>DECLARE @input XML = '&lt;Data&gt;
    &lt;Tab Name="UserData"&gt;
      &lt;Row&gt;
        &lt;BeneficialOwnerName&gt;Fred Flintstone&lt;/BeneficialOwnerName&gt;
        &lt;TaxID&gt;111-11-1111&lt;/TaxID&gt;
      &lt;/Row&gt;
      &lt;Row&gt;
        &lt;BeneficialOwnerName&gt;Barney Rubble&lt;/BeneficialOwnerName&gt;
        &lt;TaxID&gt;222-22-2222&lt;/TaxID&gt;
      &lt;/Row&gt;
    &lt;/Tab&gt;
    &lt;Tab Name="OtherData"&gt;
      &lt;Row&gt;
        &lt;BeneficialOwnerName&gt;Bugs Bunny&lt;/BeneficialOwnerName&gt;
        &lt;TaxID&gt;333-33-3333&lt;/TaxID&gt;
      &lt;/Row&gt;
      &lt;Row&gt;
        &lt;BeneficialOwnerName&gt;Road Runner&lt;/BeneficialOwnerName&gt;
        &lt;TaxID&gt;444-44-4444&lt;/TaxID&gt;
      &lt;/Row&gt;
    &lt;/Tab&gt;  
  &lt;/Data&gt;'
  </code></pre>
  
  <p>I'd like to return this:</p>
  
  <pre><code>Name     BeneficialOwnerName    TaxID
  UserData  Fred Flintstone       111-11-1111
  UserData  Barney Rubble         222-22-2222
  OtherData Bugs Bunny            333-33-3333
  OtherData Road Runner           444-44-4444
  </code></pre>
  
  <p>My SQL looks like this:</p>
  
  <pre><code>SELECT
    Name = XCol.value('@Name','varchar(25)'),   
    BeneficialOwnerName = XCol.value('BeneficialOwnerName[1]','varchar(25)'),
    TaxID = XCol.value('TaxID[1]','varchar(25)')
  FROM 
    @input.nodes('/Data/Tab/Row') AS XTbl(XCol)
  </code></pre>
  
  <p>But depending on how I tweak the FROM clause I get either two rows of Tab names or 4 rows of data with the tab name Null</p>
  
  <p>What do I need to do to get the data shown?</p>
  
  <p>Thanks</p>
  
  <p>Carl</p>