title: import xml file into sql server table
body: <p>I'm looking for help reading an XML file into a SQL Server 2008 R2 data table. </p>
  
  <p>My XML  file looks like this:</p>
  
  <pre><code>&lt;?xml version="1.0" encoding="utf-8"?&gt;
      &lt;e_objects xmlns="http://www.blank.info/ns/2012/objects"&gt;
      &lt;item  item_id="41-FE-001"&gt;
      &lt;class display="true"&gt;
      &lt;class_name&gt;FEEDER&lt;/class_name&gt;
      &lt;/class&gt;
      &lt;/item&gt;
  &lt;/e_objects&gt;
  </code></pre>
  
  <p>My create table SQL looks like this:</p>
  
  <pre><code>CREATE TABLE [dbo].[handover_data](
    [item_id] [nchar](15) NULL,
    [class] [nchar](10) NULL,
   ) ON [PRIMARY]
   GO
  </code></pre>
  
  <p>This is the SQL I'm using. I can't get it to return any values:</p>
  
  <pre><code>SELECT xmldata.value('(item[@name="item_id"]/@value)[1])', 'NCHAR') AS item_id,
  xmldata.value('(class_name)', 'NCHAR') AS class       
  FROM ( 
  SELECT CAST(x AS XML)
  FROM OPENROWSET(BULK 'C:\xmlfile.xml',
  SINGLE_BLOB) AS T(x)) AS T(x)
  CROSS APPLY x.nodes('e_objects/*') AS X(xmldata);
  </code></pre>
  
  <p>Any help would be greatly appreciated. Thanks</p>