Tuesday, 13 August 2013

Column as content with FOR XML PATH query in SQL Server

Column as content with FOR XML PATH query in SQL Server

I have a SQL query to get data from SQL Server 2012:
select dbo.TRIM(cfProj.cfProjId) as "cfProjId",
dbo.TRIM(cfProj.cfAcro) as "cfAcro",
(SELECT cfLangCode as "@cfLangCode", cfTrans as "@cfTrans",
cfProjTitle.cfTitle
FROM dbo.cfProjTitle
WHERE dbo.cfProjTitle.cfProjId = dbo.cfProj.cfProjId
FOR XML PATH('cfTitle'), type)
from cfProj FOR XML PATH('cfProj')
This SQL returns data structure like:
<cfProj>
<cfProjId>00001111</cfProjId>
<cfAcro>222</cfAcro>
<cfTitle cfLangCode="ru" cfTrans="h">
<cfTitle>some title here</cfTitle>
</cfTitle>
</cfProj>
But I want get XML structure without second nested "cfTitle" element:
<cfProj>
<cfProjId>00001111</cfProjId>
<cfAcro>222</cfAcro>
<cfTitle cfLangCode="ru" cfTrans="h">some title here</cfTitle>
</cfProj>
Difference in this line: <cfTitle cfLangCode="ru" cfTrans="h">some title
here</cfTitle>
Any ideas how I can get desirable result?

No comments:

Post a Comment