title: Insert XML file into SQL Server
body: <p>I'm trying to import an XML file into a SQL table. I found a few examples of code to do this, but I can't seem to get it to work. I've tried a few variations in my code but at this point I'm not sure if the issue is the XML file structure or my SQL. </p>
  
  <p>Below is the code I'm using as well as the XML file (truncated to one record).</p>
  
  <pre><code>CREATE TABLE workspace.dbo.tbt_SED_XMLwithOpenXML
  (
  Id INT IDENTITY PRIMARY KEY,
  XMLData XML,
  LoadedDateTime DATETIME
  )    
  
  INSERT INTO workspace.dbo.tbt_SED_XMLwithOpenXML(XMLData, LoadedDateTime)
  SELECT CONVERT(XML, BulkColumn) AS BulkColumn, GETDATE() 
  FROM OPENROWSET(BULK 'File.xml', SINGLE_BLOB) AS x;
  
  
  DECLARE @XML AS XML, @hDoc AS INT, @SQL NVARCHAR (MAX)    
  
  SELECT @XML = XMLData FROM workspace.dbo.tbt_SED_XMLwithOpenXML WHERE ID =     '1' -- The row to process    
  
  EXEC sp_xml_preparedocument @hDoc OUTPUT, @XML
  
  
  INSERT INTO workspace.dbo.tb_SED_Emails
  SELECT email
  FROM OPENXML(@hDoc, 'responseData/manifest/contact_data')
  WITH 
  (
  email [varchar](128) 'email'
  )
  
  
  EXEC sp_xml_removedocument @hDoc
  GO
  </code></pre>
  
  <p>XML File Example: </p>
  
  <pre><code>&lt;?xml version="1.0" encoding="utf-8"?&gt;
  &lt;methodResponse&gt;
  &lt;item&gt;
      &lt;methodName&gt;
          &lt;![CDATA[]]&gt;
      &lt;/methodName&gt;
      &lt;responseData&gt;
          &lt;manifest&gt;
              &lt;contact_data&gt;
                  &lt;email&gt;jason.kang@stanfordalumni.org&lt;/email&gt;
              &lt;/contact_data&gt;
          &lt;/manifest&gt;
      &lt;/responseData&gt;
      &lt;responseNum&gt;
          &lt;![CDATA[1]]&gt;
      &lt;/responseNum&gt;
      &lt;responseCode&gt;
          &lt;![CDATA[]]&gt;
      &lt;/responseCode&gt;
  &lt;/item&gt;
  &lt;/methodResponse&gt;
  </code></pre>