title: SQL FOR XML FOR PATH Column Value
body: <p>I am trying to get a column value as an XML Node like so:</p>
  
  <pre><code>FOR XML PATH(V_CONSTAT_ACTUAL_DATES.JOB_NUMBER), Type
  </code></pre>
  
  <p>but I get this error:</p>
  
  <blockquote>
    <p>Incorrect syntax near 'V_CONSTAT_ACTUAL_DATES'. Expecting STRING or TEXT_LEN</p>
  </blockquote>
  
  <p>is what I am trying to accomplish not possible?</p>
  
  <p>Here is the full SQL query:</p>
  
  <pre><code>SELECT
  (
  SELECT V_CONSTAT_ACTUAL_DATES.JOB_NUMBER AS 'Key',
  (
      SELECT
          (
  
  
          SELECT
          (
  
          SELECT
          (
  
          SELECT
          CONVERT(date, V_CONSTAT_ACTUAL_DATES.ID120) AS 'actualFinish',
          CONVERT(date, V_CONSTAT_BASE_DATES.ID120) AS 'baseLineStart'
          FOR XML PATH(''), Type
          )
          FOR XML PATH('baseStartFinishList'), Type
          )
          FOR XML PATH(''), Type
  
  
  
  
  ),
  
          (
          SELECT CONVERT(date, V_CONSTAT_ACTUAL_DATES.DATE_TO_END) AS 'closingDate'
          FOR XML PATH(''), Type
          ),
          (
          SELECT DATEDIFF(dd,V_CONSTAT_BASE_DATES.ID67,V_CONSTAT_ACTUAL_DATES.DATE_TO_END)-1 AS 'DaysOfConstruction'
          FOR XML PATH(''), Type
          ),
          (
          SELECT DATEDIFF(dd,GETDATE(),V_CONSTAT_ACTUAL_DATES.DATE_TO_END) AS 'DaysToClosing'
          FOR XML PATH(''), Type
          ),
          (
              SELECT
              IsNull(V_CONSTAT_ACTUAL_DATES.IDNOTES2, ' ') AS 'notes'
              FOR XML PATH(''), Type
          ),
          (
          SELECT DATEDIFF(dd,V_CONSTAT_BASE_DATES.ID187,V_CONSTAT_PROJ_DATES.ID187) AS 'ScheduleVariance'
          FOR XML PATH(''), Type
          )
  FOR XML PATH('V_CONSTAT_PROJ_DATES.JOB_NUMBER'), Type
  )
  FOR XML PATH('KeyValue'), Type
  )
  FROM   
              ((V_CONSTAT_PROJ_DATES V_CONSTAT_PROJ_DATES INNER JOIN V_CONSTAT_ACTUAL_DATES V_CONSTAT_ACTUAL_DATES 
          ON 
              V_CONSTAT_PROJ_DATES.JOB_NUMBER=V_CONSTAT_ACTUAL_DATES.JOB_NUMBER) 
          INNER JOIN 
              V_CONSTAT_BASE_DATES V_CONSTAT_BASE_DATES 
          ON 
              (V_CONSTAT_ACTUAL_DATES.JOB_NUMBER=V_CONSTAT_BASE_DATES.JOB_NUMBER) AND (V_CONSTAT_PROJ_DATES.JOB_NUMBER=V_CONSTAT_BASE_DATES.JOB_NUMBER)) 
          INNER JOIN 
              V_CONSTAT_SCH_DATES V_CONSTAT_SCH_DATES 
          ON 
              ((V_CONSTAT_BASE_DATES.JOB_NUMBER=V_CONSTAT_SCH_DATES.JOB_NUMBER) AND (V_CONSTAT_PROJ_DATES.JOB_NUMBER=V_CONSTAT_SCH_DATES.JOB_NUMBER)) 
              AND (V_CONSTAT_ACTUAL_DATES.JOB_NUMBER=V_CONSTAT_SCH_DATES.JOB_NUMBER)
  
  
  
  WHERE V_CONSTAT_ACTUAL_DATES.AREA_DESC = 'Fairgrounds Phase 5' AND V_CONSTAT_ACTUAL_DATES.DATE_TO_END&gt;=GETDATE()
  
  ORDER BY V_CONSTAT_ACTUAL_DATES.DATE_TO_END
  FOR XML PATH(''), ROOT('Root')
  </code></pre>
  
  <p>I am trying to get column value of JOB_NUMBER as the node name</p>
  
  <p>Here is an example of my current XML:</p>
  
  <pre><code>&lt;KeyValue&gt;
      &lt;Key&gt;FA50104&lt;/Key&gt;
      &lt;V_CONSTAT_PROJ_DATES.JOB_NUMBER&gt;
        &lt;baseStartFinishList&gt;
          &lt;baseLineStart&gt;2016-06-06&lt;/baseLineStart&gt;
        &lt;/baseStartFinishList&gt;
        &lt;closingDate&gt;2016-05-26&lt;/closingDate&gt;
        &lt;DaysOfConstruction&gt;237&lt;/DaysOfConstruction&gt;
        &lt;DaysToClosing&gt;1&lt;/DaysToClosing&gt;
        &lt;notes&gt; &lt;/notes&gt;
        &lt;ScheduleVariance&gt;0&lt;/ScheduleVariance&gt;
      &lt;/V_CONSTAT_PROJ_DATES.JOB_NUMBER&gt;
    &lt;/KeyValue&gt;
  </code></pre>
  
  <p>What I am trying to do is have the V_CONSTAT_PROJ_DATES.JOB_NUMBER as the Key Value (FA50104)</p>