title: Import XML with Attribute to SQL Server Table
body: <p>I can't get the value for the XML attribute 'Country' into my table.</p>
  
  <p>What am I doing wrong? </p>
  
  <p>Here is my XML:</p>
  
  <pre><code>&lt;?xml version="1.0" encoding="utf-8"?&gt;
  &lt;CustomerDetails&gt;
        &lt;PersonalInfo Country="USA"&gt;
            &lt;CustID&gt;1001&lt;/CustID&gt;
            &lt;CustLastName&gt;Smith&lt;/CustLastName&gt;
            &lt;DOB&gt;2011-05-05T09:25:48.253&lt;/DOB&gt;
            &lt;Address&gt;
              &lt;Addr1&gt;100 Smith St.&lt;/Addr1&gt;
              &lt;City&gt;New York&lt;/City&gt;
            &lt;/Address&gt; 
        &lt;/PersonalInfo&gt;   
  &lt;/CustomerDetails&gt;
  </code></pre>
  
  <p>Here is my SQL:</p>
  
  <pre><code>Drop table #Cust
  CREATE TABLE #Cust
      (CustID INT, CustLastName VARCHAR(10)
               , DOB DATETIME, Addr1 VARCHAR(100), City VARCHAR(10), Country VARCHAR(20))
  insert into #Cust
  select
     c3.value('CustID[1]','int'),
     c3.value('CustLastName[1]','varchar(10)'),
     c3.value('DOB[1]','DATETIME'),
     c3.value('(Address/Addr1)[1]','VARCHAR(100)'),
     c3.value('(Address/City)[1]','VARCHAR(10)'),
     c3.value('Country[1]','VARCHAR(20)')
  from
  (
     select 
        cast(c1 as xml)
     from 
        OPENROWSET (BULK 'C:\Users\wattronts\Documents\XMLImportTest.xml',SINGLE_BLOB) as T1(c1)
  )as T2(c2)
  cross apply c2.nodes('/CustomerDetails/PersonalInfo') T3(c3)
  
  Select * from #Cust
  </code></pre>
  
  <p>Thanks for your help.</p>