title: SQL Server Assign XML Variable from Row Data with Namespaces
body: <p>So I have an entire XML file stored in a single row/column of a table.  I need to assign that to a variable and specify WITH NAMESPACES.  I can't come up with any syntax that works though.  </p>
  
  <pre><code>DECLARE @xmlText xml
  
   set  @xmlText = (select rowdata    
   FROM dbo.Staging with (nolock)    
   WHERE     
    FileId = @FileID AND    
    ClientID = @ClientID) 
  </code></pre>
  
  <p>Will I somehow have to contrive a sql statement for execution?  If I do though, I'm not sure how I'd get that assigned back to a local variable since it would be on another spid.  I'm stumped!</p>
  
  <hr>
  
  <p>I will be using xquery :)</p>
  
  <p>This passed syntax check...</p>
  
  <pre><code>;WITH XMLNAMESPACES('http://xxx' AS p) 
  (select @xmlText = rowdata    
   FROM dbo.Staging with (nolock)    
   WHERE     
    FileId = @FileID AND    
    ClientID = @ClientID) 
  
  
  SELECT      
  MFRRequestID = Y.i.value('(@id)[1]', 'int'),     
  RequestStatus = Y.i.value('Status[1]', 'varchar(10)') ,     
  AccountNumber = Y.i.value('p:AccountNumber[1]', 'varchar(10)'),    
  ReferralDate = CIF.value('(ReferralDate)[1]', 'datetime'),     
  SuspenseBalance = CIF.value('(SuspenseBalance)[1]', 'varchar(25)') 
  FROM      @xmltext.nodes('/p:OrderRequest/p:RequestItems/p:MFRRequest') 
  AS Y(i) CROSS APPLY     i.nodes('p:CaseInformation') AS Tbl(CIF) 
  </code></pre>
  
  <p>But I am getting the error...</p>
  
  <blockquote>
    <p>XQuery [nodes()]: The name "p" does not denote a namespace.</p>
  </blockquote>
  
  <p>This is related to a question I asked yesterday.  I basically got this exact thing working but instead of assigning the @xmltext variable from a table (which is what I need to do for the final production code), I copied and pasted the xml and assigned it as a hardcoded string just so I could work out the xquery syntax.  Sorry for the confusion, this is all pretty new to me.</p>