Return result as XML using FOR XML in SQL Server

AliYilmaz
 
on Sep 09, 2021 02:01 AM
408 Views

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

 

Download FREE API for Word, Excel and PDF in ASP.Net: Download
dharmendr
 
on Sep 11, 2021 02:32 AM

Hi AliYilmaz,

You need to replace the > and < from the xml string in the select query.

Example

DECLARE @XmlContent AS NVARCHAR(MAX) 
SET @XmlContent = (
    SELECT
        EmployeeID,
		FirstName,
		'<![CDATA[' +PostalCode+ ']]>' as merchantItemCategoryName
    FROM Employees
	WHERE EmployeeID = 1
FOR XML PATH('MerchantItem'), ROOT('MerchantItems')
)

SELECT REPLACE(REPLACE(@XmlContent,'&gt;','>'),'&lt;','<')