title: insert data from xml column into temp table
body: <p>I have a xml column that look like</p>
  
  <pre><code>SET @XMLData = '&lt;ArrayOfEntityNested xmlns:i="http://www.w3.org/2001/XMLSchema-instance"
                                       xmlns="http://schemas.datacontract.org/2004/07/Gbms.Dto.Bijak"&gt;
                     &lt;EntityNested&gt;
                         &lt;Id xmlns="http://schemas.datacontract.org/2004/07/Gbms.Dto"&gt;1&lt;/Id&gt;
                         &lt;Date xmlns="http://schemas.datacontract.org/2004/07/Gbms.Dto.VirginBijak"&gt;0001-01-01T00:00:00&lt;/Date&gt;
                         &lt;Description xmlns="http://schemas.datacontract.org/2004/07/Gbms.Dto.VirginBijak"&gt;deesc&lt;/Description&gt;
                         &lt;Number xmlns="http://schemas.datacontract.org/2004/07/Gbms.Dto.VirginBijak" i:nil="true" /&gt;
                     &lt;/EntityNested&gt;
                   &lt;/ArrayOfEntityNested&gt;'
  </code></pre>
  
  <p>I need insert data from the XML into a temp table.</p>
  
  <p><a href="http://www.venkateswarlu.co.in/SQLServer/How_to_read_data_from_XML_string.aspx" rel="nofollow">here</a></p>
  
  <p>For this I use from following code. But it's not working, and it's not inserting any data into temp table.</p>
  
  <pre><code>--Variables Decleration
  DECLARE @XMLData VARCHAR(MAX)
  DECLARE @idoc INT
  
  -- Creating Temporary Table
  CREATE TABLE #TEMP_TABLE
  (
      REC_ID INT IDENTITY(1,1),
      [Id] INT,
      [Date] VARCHAR(50),
      [Number] VARCHAR(50),
  );
  
  --Case 1
  SET @XMLData = '&lt;ArrayOfEntityNested xmlns:i="http://www.w3.org/2001/XMLSchema-instance"
                                       xmlns="http://schemas.datacontract.org/2004/07/Gbms.Dto.Bijak"&gt;
                     &lt;EntityNested&gt;
                        &lt;Id xmlns="http://schemas.datacontract.org/2004/07/Gbms.Dto"&gt;1&lt;/Id&gt;
                        &lt;Date xmlns="http://schemas.datacontract.org/2004/07/Gbms.Dto.VirginBijak"&gt;0001-01-01T00:00:00&lt;/Date&gt;
                        &lt;Number xmlns="http://schemas.datacontract.org/2004/07/Gbms.Dto.VirginBijak" i:nil="true" /&gt;
                     &lt;/EntityNested&gt;
                  &lt;/ArrayOfEntityNested&gt;
                                 '
  --Reading Data from XML and inserting into Temp Table
  EXECUTE sp_xml_preparedocument @idoc OUTPUT, @XMLData
  
  INSERT INTO #TEMP_TABLE
     SELECT * 
     FROM OpenXML(@idoc,'/ArrayOfEntityNested/EntityNested', 1)
          WITH #TEMP_TABLE
  
  EXECUTE sp_xml_removedocument @idoc
  
  --Displaying data from Temp Table
  SELECT * FROM #TEMP_TABLE
  DROP TABLE #TEMP_TABLE;
  </code></pre>
  
  <p>But that doesn't work, if xml format correct might look like :</p>
  
  <pre><code>SET @XMLData = '&lt;ArrayOfEntityNested&gt;
                     &lt;EntityNested&gt;
                        &lt;Id&gt;1&lt;/Id&gt;
                        &lt;Date&gt;0001-01-01T00:00:00&lt;/Date&gt;
                        &lt;Description&gt;deesc&lt;/Description&gt;
                        &lt;EmployeeId&gt;2&lt;/EmployeeId&gt;
                        &lt;IsDeleted&gt;false&lt;/IsDeleted&gt;
                        &lt;LoadingPermitTruckId&gt;7541&lt;/LoadingPermitTruckId&gt;
                      &lt;/EntityNested&gt;
                   &lt;/ArrayOfEntityNested&gt;'
  </code></pre>
  
  <p>then it works.</p>
  
  <p>Please help me.</p>