CODEWITHSUNDEEP
sqlsql-server
user979331
user979331

Reputation: 11951

SQL FOR XML FOR PATH Column Value

I am trying to get a column value as an XML Node like so:

FOR XML PATH(V_CONSTAT_ACTUAL_DATES.JOB_NUMBER), Type

but I get this error:

Incorrect syntax near 'V_CONSTAT_ACTUAL_DATES'. Expecting STRING or TEXT_LEN

is what I am trying to accomplish not possible?

Here is the full SQL query:

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>=GETDATE()

ORDER BY V_CONSTAT_ACTUAL_DATES.DATE_TO_END
FOR XML PATH(''), ROOT('Root')

I am trying to get column value of JOB_NUMBER as the node name

Here is an example of my current XML:

<KeyValue>
    <Key>FA50104</Key>
    <V_CONSTAT_PROJ_DATES.JOB_NUMBER>
      <baseStartFinishList>
        <baseLineStart>2016-06-06</baseLineStart>
      </baseStartFinishList>
      <closingDate>2016-05-26</closingDate>
      <DaysOfConstruction>237</DaysOfConstruction>
      <DaysToClosing>1</DaysToClosing>
      <notes> </notes>
      <ScheduleVariance>0</ScheduleVariance>
    </V_CONSTAT_PROJ_DATES.JOB_NUMBER>
  </KeyValue>

What I am trying to do is have the V_CONSTAT_PROJ_DATES.JOB_NUMBER as the Key Value (FA50104)

Upvotes: 0

Views: 311

Answers (1)

Serg
Serg

Reputation: 22811

You need to create outer element 'by hand'. Simple example:

with t as (
select 
      1 as a, 2 as b, 3 as c,  4 as d, 'k100' as [key]
)
select [key],
cast ('<'+ [key] + '>' +
  cast(
    (select 
         (select a as g1, b as g2 for xml path ('grp'), type)
        , (select c as g1, d as g2 for xml path ('grp'), type)
    for xml path (''))
    as varchar(max))
    + '</'+ [key] + '>'
 as xml) xmlcol
 from t

Upvotes: 1

Related Questions