Adding one node to SQL XML Explicit results



I would like to add one node only to the bottom of my elements which is the result of an SQL query (for xml explicit).


Here is my query:



SELECT 1 AS Tag,
NULL AS Parent,
ROW_NUMBER() OVER
(
ORDER BY c.SubCategoryID ASC
)AS [Customers!1!RowNumber!ELEMENT],
c.SubCategoryID AS [Customers!1!SubCategoryID!ELEMENT],
c.Name AS [Customers!1!Name!ELEMENT],
x.Description AS [Customers!1!Description!ELEMENT],
x.Image AS [Customers!1!Image!ELEMENT]
FROM (SELECT TOP(3) * FROM tSubCategory) c
CROSS APPLY
(
SELECT TOP(3) *
FROM tProduct p
WHERE c.SubCategoryID = p.SubCategoryID
ORDER BY p.ProductID DESC
) x
FOR XML EXPLICIT;


Here is my result:



<Customers>
<RowNumber>1</RowNumber>
<SubCategoryID>1</SubCategoryID>
<Name>John</Name>
<Description>xtz</Description>
<Image>ips.png</Image>
</Customers>
<Customers>
<RowNumber>2</RowNumber>
<SubCategoryID>1</SubCategoryID>
<Name>Alex</Name>
<Description>sdgfgsg</Description>
<Image>ne2.jpg</Image>
</Customers>


At the bottom of the above result I would like to add one node only:



<Test>
<Test>1</Test>
</Test>


How would I do that? Also how can I enclose all of my above results into something like:



<AllResults>
</AllResults>

No comments:

Post a Comment