title: OpenXML Import XML file to SQL server table
body: <p>I am trying to set up an SQL job to import an XML file into an SQL Server table. Using OPENXML, I can't seem to select the specific data I need from the file. Here's my code and XML data. I am trying to select Facility and Entity_Code but when I run the code, these fields appear as blank. </p>
  
  <p>I would like to transfer these fields into their own table.</p>
  
  <p>Thanks in advance.</p>
  
  <pre class="lang-sql prettyprint-override"><code>Declare @x xml
  
  select @x=p 
  from OPENROWSET(Bulk'\\vmirsdh01\fast_data\Small.xml', SINGLE_BLOB) as T(P)
  
  Select @x
  
  Declare @hdoc int
  
  EXEC sp_xml_preparedocument @hdoc OUTPUT, @x
  
  Select *
  FROM OPENXML (@hdoc,'/Report/Tablix1/Details_Collection/Details',0)
  with(Facility nvarchar(255) '@Facility',
  Entity_Code nvarchar(255) '@Entity_Code')
  
  exec sp_xml_removedocument @hdoc
  </code></pre>
  
  <p>'************ XML</p>
  
  <pre class="lang-xml prettyprint-override"><code>&lt;?xml version="1.0" encoding="utf-8"?&gt;&lt;Report xsi:schemaLocation="T-Report https://csre.xxx.com%2FDevelopment%20Folder%2FIand%2FT-Report&amp;amp;rs%3ACommand=Render&amp;amp;rs%3AFormat=XML&amp;amp;rs%3ASessionID=4keav12uayp33ve3uczpgmfr&amp;amp;rc    %3ASchema=True" Name="T-Report" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns="T_Report"&gt;
  &lt;Tablix1&gt;
  &lt;Details_Collection&gt;&lt;Details Facility="Fxx" Tool_Type="Base Build" Entity_Code="EquiP1" /&gt;&lt;/Details_Collection&gt;
  &lt;/Tablix1&gt;
  &lt;/Report&gt;
  </code></pre>
  
  <p>Here is an executable version</p>
  
  <pre class="lang-sql prettyprint-override"><code>Declare @x xml
  
  select @x='&lt;?xml version="1.0" encoding="utf-8"?&gt;&lt;Report xsi:schemaLocation="T-Report https://csre.xxx.com%2FDevelopment%20Folder%2FIand%2FT-Report&amp;amp;rs%3ACommand=Render&amp;amp;rs%3AFormat=XML&amp;amp;rs%3ASessionID=4keav12uayp33ve3uczpgmfr&amp;amp;rc    %3ASchema=True" Name="T-Report" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns="T_Report"&gt;
  &lt;Tablix1&gt;
  &lt;Details_Collection&gt;&lt;Details Facility="Fxx" Tool_Type="Base Build" Entity_Code="EquiP1" /&gt;&lt;/Details_Collection&gt;
  &lt;/Tablix1&gt;
  &lt;/Report&gt;'
  
  Declare @hdoc int
  
  EXEC sp_xml_preparedocument @hdoc OUTPUT, @x
  
  Select *
  FROM OPENXML (@hdoc,'/Report/Tablix1/Details_Collection/Details',0)
  with(Facility nvarchar(255) '@Facility',
  Entity_Code nvarchar(255) '@Entity_Code')
  
  exec sp_xml_removedocument @hdoc
  </code></pre>