Import XML with Attribute to SQL Server Table

Question

I can't get the value for the XML attribute 'Country' into my table.

What am I doing wrong?

Here is my XML:

<?xml version="1.0" encoding="utf-8"?>
<CustomerDetails>
      <PersonalInfo Country="USA">
          <CustID>1001</CustID>
          <CustLastName>Smith</CustLastName>
          <DOB>2011-05-05T09:25:48.253</DOB>
          <Address>
            <Addr1>100 Smith St.</Addr1>
            <City>New York</City>
          </Address> 
      </PersonalInfo>   
</CustomerDetails>

Here is my SQL:

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

Thanks for your help.

Answer 1

Use @ to specify that you want an attribute.

T3.c3.value('@Country', 'varchar(50)')