Menu
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,
851
The trick to placing a subquery in the select clause is that the subquery must return a single value. This is why an aggregate function such as the SUM, COUNT, MIN, or MAX function is commonly used in the subquery.
You cannot include text, unitext, or image datatypes in subqueries. Subqueries cannot manipulate their results internally, that is, a subquery cannot include the order by clause, the compute clause, or the into keyword.
Use a join or a subquery anytime that you reference information from multiple tables. Joins and subqueries are often used together in the same query. In many cases, you can solve a data retrieval problem by using a join, a subquery, or both.
A subquery can be nested inside the WHERE or HAVING clause of an outer SELECT , INSERT , UPDATE , or DELETE statement, or inside another subquery.
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')
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
