question

AliYlmaz-8572 avatar image
0 Votes"
AliYlmaz-8572 asked YitzhakKhabinsky-0887 commented

Tsql Root Child Node Output Xml

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


132584-xmls.png


sql-server-generalsql-server-transact-sql
xmls.png (166.3 KiB)
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

While asking a question, you need to provide a minimal reproducible example:
(1) DDL and sample data population, i.e. CREATE table(s) plus INSERT T-SQL statements.
(2) What you need to do, i.e. logic and your code attempt implementation of it in T-SQL.
(3) Desired output, based on the sample data in the #1 above.
(4) Your SQL Server version (SELECT @@version;).
All within the question, no images.

0 Votes 0 ·

1 Answer

MelissaMa-msft avatar image
0 Votes"
MelissaMa-msft answered MelissaMa-msft commented

Hi @AliYlmaz-8572,

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.

· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi,

So how do we add a second column? And will I be able to see the

<specs>

<spec> <description> <value> </value> </description> </spec>

<spec> <description> <value> </value> </description> </spec>

</spec>

output I posted in the picture.

0 Votes 0 ·

Hi @AliYlmaz-8572,

Thanks for your update.

132912-spec.png

Per my understanding, above snapshot is your expected output.

It is still recommended to provide CREATE TABLE statements for ProductList together with INSERT statements with sample data, and your latest code.

Since all the descriptions and values are different, are they from different columns or rows?

Best regards,
Melissa



0 Votes 0 ·
spec.png (94.6 KiB)