title: Reading xml data in SQL
body: <p>I am reading data from xml in Sql.</p>
  
  <p>Here is my xml :</p>
  
  <pre><code>Declare @MainXml XML = 
  '&lt;?xml version="1.0" encoding="utf-8"?&gt;
    &lt;result&gt;
     &lt;details&gt;
       &lt;admin&gt;
          &lt;code&gt;555&lt;/code&gt;
       &lt;/admin&gt;
      &lt;claimhistory&gt;
          &lt;claim id="1" number="100"&gt;
              &lt;account&gt;Closed&lt;/account&gt;           
          &lt;/claim&gt;        
          &lt;claim id="2" number="200"&gt;         
              &lt;account&gt;Closed&lt;/account&gt;                       
           &lt;/claim&gt;        
       &lt;/claimhistory&gt;
   &lt;/details&gt;
  &lt;/result&gt;'
  </code></pre>
  
  <p>Reading data like this:</p>
  
  <pre><code>select 
      C.X.value('(admin/code)[1]', 'varchar(max)') as Code,
      A.X.value('@id', 'varchar(max)') as Id,
      A.X.value('@number', 'varchar(max)') as No,            
      A.X.value('(account)[1]', 'varchar(max)') as Status
  from 
      @MainXml.nodes('result/details') as C(X)
  cross apply 
      C.X.nodes('claimhistory/claim') as A(X)
  </code></pre>
  
  <p>This is returning:</p>
  
  <pre><code>Code  Id  No  Status 
  ---------------------
  555   1   100  Closed
  555   2   200  Closed
  </code></pre>
  
  <p>Stored procedure contains above code. </p>
  
  <p>Here datatable variable is used as an input for Stored Procedure. It contains id and name.</p>
  
  <pre><code> Declare @dtValue As [dbo].[DataTableDetails]
   Insert Into @dtValue(Requested_Id, Name) Values(1, 'Tim');
   Insert Into @dtValue(Requested_Id, Name) Values(2, 'Joe');
  </code></pre>
  
  <p>I want to add these names to select query based on matching Id of an xml to input.</p>
  
  <p>Expected output - </p>
  
  <pre><code>Code  Id  No   Status   Name
  ----------------------------
  555   1   100  Closed   Tim
  555   2   200  Closed   Joe
  </code></pre>
  
  <p>Currently - after inserting the selected records from xml, I am using update query But table contains over a million records so it is effecting performance now.</p>
  
  <p>Please suggest me.</p>
  
  <p><strong>Edited:</strong></p>
  
  <p>Tried with Join - [added below line in select query]</p>
  
  <pre><code>Select 
      C.X.value('(admin/code)[1]', 'varchar(max)') as Code,
      A.X.value('@id', 'varchar(max)') as Id,
      A.X.value('@number', 'varchar(max)') as No,            
      A.X.value('(account)[1]', 'varchar(max)') as Status,
      CA.Name
  from 
      @MainXml.nodes('result/details') as C(X)
  cross apply 
      C.X.nodes('claimhistory/claim') as A(X)
  join 
      @dtValue CA on CA.Requested_Id = A.X.value('@id', 'varchar(max)')
  </code></pre>