Hi,
I want to get xml output in t sql. I can do plain xml. But how can I do it as root node.
It looks like the picture I want to make.
<specs>
<spec><desc></desc></spec>
<spec><desc></desc></spec>
<spec><desc></desc></spec>
</specs>
DECLARE @XmlContent AS NVARCHAR(MAX)
SET @XmlContent = (
SELECT
CASE WHEN CHARINDEX('_',ItemGroupId) > 0
THEN SUBSTRING(ItemGroupId, 1, CHARINDEX('_',ItemGroupId) - 1)
ELSE ItemGroupId
END merchantItemId,
(
SELECT Barcode AS ean for XML PATH('eans'), type
),
ProductName AS itemTitle,
CategoryCode as merchantItemCategoryId,
'<!--[CDATA[' +CategoryName+ ']]-->' as merchantItemCategoryName,
Brand as brand,
(
Select '<!--[CDATA[İşlemci Tipi]]-->' as description, + Metarial as [values] for XML PATH('spec'), TYPE
),
(
Select '<!--[CDATA[İşlemci Tipi]]-->' as description, + Metarial as [values]
for XML PATH('spec'), TYPE
),
[Length] as [length],
ProductName + '-' + ItemGroupId as title,
Inventory as stockStatus,
OriginalPrice as priceEft,
DiscountedPrice as pricePlusTax,
'<!--[CDATA['+ ProductUrl+']]-->' as itemUrl,
SmallImageUrl as small_image,
'<!--[CDATA['+MediumImageUrl+']]-->' as itemUrlMobile,
'<!--[CDATA['+LargeImageUrl+']]-->' as itemImageUrl,
'<!--[CDATA[Ürün 3 Gün içerisinde stoklarımızda olacaktır]]-->' as stockDetail,
'5.00' as shippingFee,
'<!--[CDATA[16:00 a kadar verilen siparişler aynı gün gonderilir]]-->' as shippingDetail
FROM ProductList
FOR XML PATH('MerchantItem'), ROOT('MerchantItems')
)
SELECT REPLACE(REPLACE(REPLACE(@XmlContent,'>','>'),'<','<'), '&', '-') AS ProductsXml
SELECT TOP 1 XmlData AS ProductsXml FROM ProductListXml