Return result as XML using FOR XML in SQL Server

AliYilmaz
 
on Sep 09, 2021 02:01 AM
286 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
AliYilmaz
 
on Sep 09, 2021 04:52 AM

Hi,

I'm trying to use it like this but it shows special character. how can i solve this

'<![CDATA[' +CategoryName+ ']]>' as merchantItemCategoryName,

 

<merchantItemCategoryName>&lt;![CDATA[ERKEK > Jeans]]></merchantItemCategoryName>

 

dharmendr
 
on Sep 09, 2021 07:57 AM

Share the query.

AliYilmaz
 
on Sep 09, 2021 08:12 AM

Hi,

I Shared the query

        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 Size description for XML PATH('spec'), type
			)AS specs,
			[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 stockStatus,
			OriginalPrice as priceEft,
			DiscountedPrice as pricePlusTax,
			ProductUrl as product_url,
			SmallImageUrl as small_image,
			MediumImageUrl as itemUrlMobile,
			LargeImageUrl as itemImageUrl,
			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
		--WHERE CategoryId_1 IS NOT NULL 
		----AND CategoryName LIKE '%>%'
		--AND IsActive = 1
		--AND SmallImageUrl IS NOT NULL
	FOR XML PATH('MerchantItem'), ROOT('MerchantItems')
	)

 

dharmendr
 
on Sep 11, 2021 02:32 AM

Hi AliYilmaz,

You need to replace the &gt; and &lt; 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;','<')