Generate nested XML Output with Root Child Node in SQL Server

AliYilmaz
 
on Sep 13, 2021 12:23 AM
687 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.

<specs>
    <spec><desc></desc></spec>
    <spec><desc></desc></spec>
    <spec><desc></desc></spec>
</specs>

https://i.hizliresim.com/op0c2rc.png

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

 

Download FREE API for Word, Excel and PDF in ASP.Net: Download
dharmendr
 
on Sep 16, 2021 07:28 AM

Hi AliYilmaz,

Refer below query.

SQL

SELECT CustomerID,Name,'<!--[CDATA['+ Country +']]-->' AS 'Country',
(
    SELECT 
        (SELECT '<!--[CDATA[Country]]-->' AS description, + Country AS [values] for XML PATH('spec'), TYPE ),
        (SELECT '<!--[CDATA[Name]]-->' AS description, + Name AS [values] for XML PATH('spec'), TYPE )
    FOR XML PATH ('specs'), TYPE
)
FROM Customers FOR XML RAW ('Customer'), ROOT('Customers'), ELEMENTS

XML Output

<Customers>
  <Customer>
    <CustomerID>1</CustomerID>
    <Name>John Hammond</Name>
    <Country>&lt;!--[CDATA[United States]]--&gt;</Country>
    <specs>
      <spec>
        <description>&lt;!--[CDATA[Country]]--&gt;</description>
        <values>United States</values>
      </spec>
      <spec>
        <description>&lt;!--[CDATA[Name]]--&gt;</description>
        <values>John Hammond</values>
      </spec>
    </specs>
  </Customer>
  <Customer>
    <CustomerID>2</CustomerID>
    <Name>Mudassar Khan</Name>
    <Country>&lt;!--[CDATA[India]]--&gt;</Country>
    <specs>
      <spec>
        <description>&lt;!--[CDATA[Country]]--&gt;</description>
        <values>India</values>
      </spec>
      <spec>
        <description>&lt;!--[CDATA[Name]]--&gt;</description>
        <values>Mudassar Khan</values>
      </spec>
    </specs>
  </Customer>
  <Customer>
    <CustomerID>3</CustomerID>
    <Name>Suzanne Mathews</Name>
    <Country>&lt;!--[CDATA[France]]--&gt;</Country>
    <specs>
      <spec>
        <description>&lt;!--[CDATA[Country]]--&gt;</description>
        <values>France</values>
      </spec>
      <spec>
        <description>&lt;!--[CDATA[Name]]--&gt;</description>
        <values>Suzanne Mathews</values>
      </spec>
    </specs>
  </Customer>
  <Customer>
    <CustomerID>4</CustomerID>
    <Name>Robert Schidner</Name>
    <Country>&lt;!--[CDATA[Russia]]--&gt;</Country>
    <specs>
      <spec>
        <description>&lt;!--[CDATA[Country]]--&gt;</description>
        <values>Russia</values>
      </spec>
      <spec>
        <description>&lt;!--[CDATA[Name]]--&gt;</description>
        <values>Robert Schidner</values>
      </spec>
    </specs>
  </Customer>
</Customers>

 

dharmendr
 
on Sep 16, 2021 07:53 AM

Use this.

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  
				( SELECT '<!--[CDATA[Country]]-->' AS description, + Country AS [values] for XML PATH('spec'), TYPE ), 
				( SELECT '<!--[CDATA[Name]]-->' AS description, + Name AS [values] for XML PATH('spec'), TYPE ) 
            FOR XML PATH ('specs'), 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')
)