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.
BEGIN
DECLARE @XmlContent AS NVARCHAR(MAX)
SET @XmlContent = (
SELECT
CASE WHEN CHARINDEX('_',ItemGroupId) > 0
THEN SUBSTRING(ItemGroupId, 1, CHARINDEX('_',ItemGroupId) - 1)
ELSE ItemGroupId
END Id,
Barcode AS product_code,
ProductName AS product_name,
CategoryCode as category_code,
CategoryName as category_name,
Brand as brand,
Size as size,
[Length] as [length],
ProductName + '-' + ItemGroupId as title,
--'Birbirinden şık ' + ProductName + ' modelleri LTB''de. En uygun ' + ProductName + ' fiyatları güvenilir online alışveirş imkanı ile shop.ltbjeans.com''da sizleri bekliyor.' as [description],
'in stock' as availability,
'1604' as google_product_category ,
'new' as condition ,
'no' as adult ,
CASE WHEN ItemGroupId LIKE '03%' THEN 'kids'
WHEN ItemGroupId LIKE '07%' THEN 'new born'
ELSE 'adult'
END as age_group ,
'' as color ,
Gender as gender ,
Metarial as metarial ,
IsActive as is_active,
Inventory as inventory,
OriginalPrice as original_price,
DiscountedPrice as discounted_price,
ProductUrl as product_url,
SmallImageUrl as small_image,
MediumImageUrl as medium_image,
LargeImageUrl as large_image,
Rating as rating,
OriginalPriceCurrency as original_price_currency,
DiscountedPriceCurrency as discount_price_currency,
SameDayDelivery as same_day_delivery,
FreeDelivery as free_delivery,
NumberOfComments as number_of_comments,
DiscountRatio as discount_ratio,
ItemGroupID as itemgroupid,
CategoryCodeForGoogle as google_pla
FROM ProductList
FOR XML PATH('MerchantItem'), ROOT('MerchantItems')
)
SELECT TOP 1 XmlData AS ProductsXml FROM ProductListXml
END