Search code examples
sql-serverxmlt-sqlxquery

tsql for xml path insert mutliple the same named rows


Lets say I have table T with one column A.

What I would like to achieve as result is xml like this :

<not>
   <mes>not important what include</mes>
   <A>1</A>
   <A>2</A>
   <A>3</A>
   <A>4</A>
   ...
</not> 

Was trying something similar to :

    SELECT 
        'important' AS [mes],
        (select A as [A] from T)- of course that part is incorrect but don't know how to handle it
    FROM T2         
    FOR XML PATH ('not'); 

Please advice.

Updated QUERY

SET @SQL = '
WITH XMLNAMESPACES (''https://something..'' as ns)  
SELECT 
    Q.DocumentType AS [@type],
    Q.ReferenceNo AS [@ref],
    Q.Id AS [@id],
    D.DocId AS [@docId],
    N.NotId AS [@notId],
    CONVERT(char(10), N.CreationDate, 126) AS [@notdate],
    ''mes'' AS [mes/@content],
    @mes2 AS [mes/content],
    [mes] = ''important'' ,
    (select A from '+ Cast(@TableName as VARCHAR(60))+' FOR XML PATH (''''), type)
    
FROM
    [DB].[dbo].[tab1] AS Q 
    LEFT JOIN [DB].[dbo].[tab2] AS D ON Q.ID=D.ID
    LEFT JOIN [DB].[dbo].[tab3] AS N ON D.ID=N.DocId
WHERE 
    Q.ID='+ Cast(@Id as varchar(15))+'

FOR XML PATH (''not'')'; 

execute (@SQL);

Solution

  • Perhaps this will help

    -- Just a DEMONSTRATIVE Table Variable
    --------------------------------------------
    Declare @YourTable Table ([A] varchar(50))  
    Insert Into @YourTable Values 
     (1)
    ,(2)
    ,(3)
    ,(4)
     
    
    SELECT  [mes] = 'important' 
           ,( Select A from @YourTable For XML Path(''),type )
        FOR XML PATH ('not'); 
    

    Results

    <not>
      <mes>important</mes>
      <A>1</A>
      <A>2</A>
      <A>3</A>
      <A>4</A>
    </not>