title: XQuery sql Join inserts empty node when join does not match
body: <p>I am using SQL Server 2012 and am looking a way to update my xml column by looking up values from another table.</p>
  <p>I have a the following schema:</p>
  <pre><code>USE tempdb;
  GO
  
  DROP TABLE IF EXISTS [dbo].[tblstepid];
  
  CREATE TABLE [dbo].[tblstepid](
      [stepid] [uniqueidentifier]  NOT NULL,
      [name] nvarchar(32) NOT NULL
  );
  
  INSERT INTO dbo.tblstepid ([stepid], name ) VALUES ('E36A3450-1C8F-44DA-B4D0-58E5BFE2A987','step1')
  
  INSERT INTO dbo.tblstepid ([stepid], name ) VALUES ('11D70A50-08AC-4767-A0D3-87717384FF45','step2')
  </code></pre>
  <hr />
  <pre><code>DROP TABLE IF EXISTS [dbo].[tblStepList];
  
  CREATE TABLE [dbo].[tblStepList](
      [ToDoId] [int] IDENTITY(1,1) NOT NULL,
      [Data] [xml] NOT NULL
  );
  
  INSERT INTO dbo.tblStepList ([Data]) VALUES
  (N'&lt;Steplist&gt;
    &lt;Step&gt;
      &lt;StepId&gt;e36a3450-1c8f-44da-b4d0-58e5bfe2a987&lt;/StepId&gt;
      &lt;Rank&gt;1&lt;/Rank&gt;
      &lt;IsComplete&gt;false&lt;/IsComplete&gt;
      &lt;TextReadingName&gt;bug-8588_Updated3&lt;/TextReadingName&gt;     
    &lt;/Step&gt;
    &lt;Step&gt;
      &lt;StepId&gt;4078c1b1-71ea-4578-ba61-d2f6a5126ba1&lt;/StepId&gt;
      &lt;Rank&gt;2&lt;/Rank&gt;
      &lt;TextReadingName&gt;reading1&lt;/TextReadingName&gt;
      &lt;TextReadingId&gt;12&lt;/TextReadingId&gt;
  
    &lt;/Step&gt;
  &lt;/Steplist&gt;');
  
  
  
  
  
      INSERT INTO dbo.tblStepList ([Data]) VALUES
  (N'&lt;Steplist&gt;
    &lt;Step&gt;
      &lt;StepId&gt;d9e42387-56e3-40a1-9698-e89c930d98d1&lt;/StepId&gt;
      &lt;Rank&gt;1&lt;/Rank&gt;
      &lt;IsComplete&gt;false&lt;/IsComplete&gt;
      &lt;TextReadingName&gt;bug-8588_Updated3&lt;/TextReadingName&gt;   
      &lt;TextReadingId&gt;0&lt;/TextReadingId&gt;  
    &lt;/Step&gt;
    &lt;Step&gt;
      &lt;StepId&gt;e5eaf947-24e1-4d3b-a92a-d6a90841293b&lt;/StepId&gt;
      &lt;Rank&gt;2&lt;/Rank&gt;
      &lt;TextReadingName&gt;reading1&lt;/TextReadingName&gt;
    &lt;/Step&gt;
  &lt;/Steplist&gt;')
  </code></pre>
  <hr />
  <p>Now I want to a update all the steps with matching step ids in the other table as follows:</p>
  <pre><code>UPDATE sl
  SET Data = (
      SELECT v.Step.query('
  &lt;Step&gt;{./*,    
              &lt;stepname&gt;{sql:column(&quot;sr.name&quot;)}&lt;/stepname&gt;
          }
  &lt;/Step&gt;
      ')
      FROM sl.Data.nodes('/Steplist/Step') v(Step)
       left JOIN tblStepID sr ON sr.StepId = v.Step.value('(StepId/text())[1]','uniqueidentifier')
      FOR XML PATH(''), ROOT('Steplist'), TYPE
  )
  FROM tblStepList sl;
  </code></pre>
  <p>And my result xml output has an empty  node which I do not want.
  How can I write my join to not add empty nodes for me and also not to set the data if nothing matches which means my second record in my table xml should never be touched?</p>
  <p>The problem I am trying to solve is that I am trying to update xml for a table with millions of records by joining from another table and I don't want the update statement to touch the data if the join does not match.</p>
  <pre><code>&lt;Steplist&gt;
    &lt;Step&gt;
      &lt;StepId&gt;e36a3450-1c8f-44da-b4d0-58e5bfe2a987&lt;/StepId&gt;
      &lt;Rank&gt;1&lt;/Rank&gt;
      &lt;IsComplete&gt;false&lt;/IsComplete&gt;
      &lt;TextReadingName&gt;bug-8588_Updated3&lt;/TextReadingName&gt;
      &lt;stepname&gt;step1&lt;/stepname&gt;
    &lt;/Step&gt;
    &lt;Step&gt;
      &lt;StepId&gt;4078c1b1-71ea-4578-ba61-d2f6a5126ba1&lt;/StepId&gt;
      &lt;Rank&gt;2&lt;/Rank&gt;
      &lt;TextReadingName&gt;reading1&lt;/TextReadingName&gt;
      &lt;TextReadingId&gt;12&lt;/TextReadingId&gt;
      &lt;stepname /&gt;
    &lt;/Step&gt;
  &lt;/Steplist&gt;
  </code></pre>
  <p>My expected output is as follows:</p>
  <pre><code>&lt;Steplist&gt;
    &lt;Step&gt;
      &lt;StepId&gt;e36a3450-1c8f-44da-b4d0-58e5bfe2a987&lt;/StepId&gt;
      &lt;Rank&gt;1&lt;/Rank&gt;
      &lt;IsComplete&gt;false&lt;/IsComplete&gt;
      &lt;TextReadingName&gt;bug-8588_Updated3&lt;/TextReadingName&gt;
      &lt;stepname&gt;step1&lt;/stepname&gt;
    &lt;/Step&gt;
    &lt;Step&gt;
      &lt;StepId&gt;4078c1b1-71ea-4578-ba61-d2f6a5126ba1&lt;/StepId&gt;
      &lt;Rank&gt;2&lt;/Rank&gt;
      &lt;TextReadingName&gt;reading1&lt;/TextReadingName&gt;
      &lt;TextReadingId&gt;12&lt;/TextReadingId&gt;
    &lt;/Step&gt;
  &lt;/Steplist&gt;
  </code></pre>