时间戳

首页 > 解决方案 > tsql for xml path 插入多个相同命名的行

sql-server - tsql for xml path 插入多个相同命名的行

问题描述

假设我有表 T，其中有一列 A。

我想要实现的结果是这样的xml：

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

正在尝试类似的东西：

    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');

请指教。

更新了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);

标签： sql-serverxmltsqlxquery

解决方案

也许这会有所帮助

-- 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');

结果

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

推荐阅读