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