SQL Server xml parent child from same table. xml not sorting

az31mfrm 于 2023-04-28 发布在 SQL Server

关注(0)|答案(1)|浏览(158)

I have a table Messages in SQL Server with the following columns:

ID,
DISPLAY_ACCT, 
parent_id, 
message_body,
time stamp

The message_body column contains XML and the parent_id links to the same table using id. This creates a parent child relationship. I need to aggregate the xml per name creating the following structure

<all_messages>
 <main_message timestamp="2023-04-01" id="12">
   <message_body> XML IN FIELD </message_body>
   <replies>
     <reply timestamp="2023-04-02" id="23">
       <message_body> XML IN FIELD </message_body>
     </reply>
     <reply timestamp="2023-04-01" id="22">
       <message_body> XML IN FIELD </message_body>
     </reply>
   </replies>
 </main_message>
</all_messages>

So the resulting record would be

DISPLAY_ACCT
111C111

Message
XML from above

I've tried

SELECT  
    DISPLAY_ACCT, 
    CAST(CAST((SELECT id as '@ID', 
                 timestamp AS '@timestamp', 
                 cast(msg_body as xml) MESSAGE_BODY 
            FROM [MESSAGES] AS [REPLY] 
               WHERE [REPLY].[parent_id] = [m1].[ID] 
               ORDER BY timestamp DESC FOR XML AUTO, TYPE, ELEMENTS ) AS [REPLIES] 
               FROM (select timestamp, 
          message_body
         from [MESSAGES]) [m1] 
        WHERE [m1].[parent_id] IS NULL AND [m1].[NAME] = [m2].[NAME] 
      ORDER BY timestamp FOR XML PATH ('MAIN_MESSAGE'), TYPE, ELEMENTS 
             ) AS varchar(max))  as varchar(max)) as xml_messages 
            FROM  [MESSAGES]  [m2]          
        GROUP BY DISPLAY_ACCT

which works for the most part but it won't sort by timestamp desc

sql-server

来源：https://stackoverflow.com/questions/76111206/sql-server-xml-parent-child-from-same-table-xml-not-sorting

1条答案

按热度按时间

jjhzyzn01#

Your current query is self-joining a number of times, which is unnecessary. You only need one join. You can use STRING_AGG to aggregate (there is no XML_AGG unfortunately).

SELECT
  m.DISPLAY_ACCT,
  CAST(
    '<all_messages>' + 
    STRING_AGG(x.xml, '') WITHIN GROUP (ORDER BY m.timestamp DESC) +
    '</all_messages>'
  AS xml)
FROM Messages m
CROSS APPLY (
    SELECT
      m.timestamp AS [@timestamp],
      m.id AS [@id],
      m.message_body,
      (
        SELECT
          r.timestamp AS [@timestamp],
          r.id AS [@id],
          r.message_body
        FROM Messages r
        WHERE r.parent_id = m.id
        ORDER BY
          r.timestamp DESC
        FOR XML PATH('reply'), ROOT('replies'), TYPE
      )
    FOR XML PATH('main_message')
) x(xml)
WHERE m.parent_id IS NULL
GROUP BY
  m.DISPLAY_ACCT;

db<>fiddle

If you need recursive replies then you would need a recursive CTE here.

赞(0）回复(0）举报 2023-04-28

我来回答

SQL Server xml parent child from same table. xml not sorting

1条答案

相关问题

热门标签

最新问答