How to use FOR XML in Subqueries?
13,990
You need to add the TYPE
modifier to your inner query:
SELECT
'hello' AS Node1
, (
SELECT TOP 2 SiteId
FROM [dbo].[Sites]
FOR XML PATH('Site'), TYPE
) AS Sites
FOR XML PATH('ResultDetails')
Author by
The Light
I love thinking and writing .NET applications and have over 13 years experience + MCPD, MCTS, MCAD, MCP.
Updated on August 13, 2022Comments
-
The Light almost 2 years
I'd like to return the below xml:
<ResultDetails> <Node1>hello</Node1> <Sites> <Site><SiteId>1</SiteId></Site> <Site><SiteId>2</SiteId></Site> </Sites> </ResultDetails>
I wrote the below code but doesn't work:
SELECT 'hello' AS Node1, (SELECT TOP 2 SiteId FROM [dbo].[Sites] FOR XML PATH('Site')) AS Sites FOR XML PATH('ResultDetails')
but it returns:
<ResultDetails> <row> <Node1>hello</Node1> <Sites><Site><siteId>102</siteId></Site><Site><siteId>1</siteId></Site></Sites> </row> </ResultDetails>
What sql should I write?
thanks,