title: Need to update multiple nodes in a XML string using values from an SQL query
body: <p>I have a SQL stored procedure local variable @DocList (Declare @DocList XML)
  which contains the follwing XML data:</p>
  
  <pre><code>&lt;JobList ListItems="7"&gt;
    &lt;Job JobFriendlyName="EMAIL INVOICES"&gt;
      &lt;DocumentList&gt;
        &lt;Document Doc="1" ID="5280301.2019050148902.00020" Date="05-03-2019" Status="NEW" /&gt;
        &lt;Document Doc="2" ID="5280301.2019050148902.00022" Date="05-03-2019" Status="NEW" /&gt;
        &lt;Document Doc="3" ID="5280301.2019050148902.00023" Date="05-03-2019" Status="NEW" /&gt;
        &lt;Document Doc="4" ID="5280301.2019050104301.00055" Date="05-02-2019" Status="NEW" /&gt;
        &lt;Document Doc="5" ID="5280301.2019050104301.00056" Date="05-02-2019" Status="NEW" /&gt;
      &lt;/DocumentList&gt;
    &lt;/Job&gt;
    &lt;Job JobFriendlyName="INVOICES"&gt;
      &lt;DocumentList&gt;
        &lt;Document Doc="6" ID="5280300.2019050148901.00001" Date="05-03-2019" Status="NEW" /&gt;
        &lt;Document Doc="7" ID="5280300.2019050148901.00002" Date="05-03-2019" Status="NEW" /&gt;
      &lt;/DocumentList&gt;
    &lt;/Job&gt;
  &lt;/JobList&gt;
  </code></pre>
  
  <p>I also have an SQL table "DocAccess" which contains 0 or more rows with DocIDNumber the correlate to matching "ID" attribute values in the XML:</p>
  
  <pre><code>TABLE [tblDocAccess]
  (
      [Key] varachar(10),
      [DocIDNumber] [varchar](35),
      [DocLastOpenDtg] [smalldatetime]
  )
  </code></pre>
  
  
  
  <p>I want to apply query "select [DocIDNumber] from [tblDocAccess] where [Key] = {some arbitrary value}"
  against the XML in @DocList to modify attribute "Status" value from "NEW" to "OLD" for each node where
  attribute "ID" values matches a returned [DocIdNumber] values.</p>
  
  <p>I know I could create a cursor fror the select statement, then loop to locate/update any matching
  node/attribute value, but that does note seem efficient.  </p>
  
  <p>Any assistance of suggestions would be appreciated.</p>
  
  <p>===================================</p>
  
  <p>Follow-up question: Using the XML document shown above in @DocList, and another local variable, @SearchID varchar(35) which contains a value to search for,  how would I code the required {While ... Exists ... Set) logic to set  Status to "OLD" for the Document with ID matching the value in @SearchID.</p>
  
  <p>Please forgive my ignorance here.  I have been working with SQL for many years, but this is my first attempt to update an existing XML document.  </p>