Tsql Root Child Node Output Xml

Question

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>

Can we add this part of the query to the query? I should be able to add as much as I want here. May I add as you did.

(
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      
 
 
      ),  







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  
        --WHERE CategoryId_1 IS NOT NULL  
        ----AND CategoryName LIKE '%>%'  
        --AND IsActive = 1  
        --AND SmallImageUrl IS NOT NULL  
    FOR XML PATH('MerchantItem'), ROOT('MerchantItems')  
    )  
       
       
   
    SELECT REPLACE(REPLACE(REPLACE(@XmlContent,'>','>'),'<','<'), '&', '-') AS ProductsXml  
       
    SELECT TOP 1 XmlData AS ProductsXml FROM ProductListXml  

Answer 1

Hi @Ali Yılmaz ,

Welcome to Microsoft Q&A!

We recommend that you post CREATE TABLE statements for your table(ProductList) together with INSERT statements with sample data.

You could also try with below and check whether it is working.

(  
Select '<!--[CDATA[İşlemci Tipi]]-->' as description, + Metarial as [values]  
from ProductList  
for XML PATH('spec'),ROOT('specs')      
),  

Best regards,
Melissa

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.