Using FOR XML PATH to output Multiple Sibling Elements

Question

Using FOR XML PATH to output Multiple Sibling Elements

4.6k Views Asked by FMFF At 19 April 2025 at 13:59

From the database, I'm trying to generate an XML that looks like the following, for each row in the query result:

<Message>
<Header>
  <MessageName>MyMessage</MessageName>
  <TimeStamp>2017-03-31T14:32:48</TimeStamp>
  <TrackingNum>1</TrackingNum>
</Header>
<Body>
  <Activity>
    <GUID>SomeGUID</GUID>
    <OrderNum>3242432<OrderNum/>
    <OrderDate>20160331143248</OrderDate>
  </Activity>
</Body>
</Message>

The query I have looks like this:

SELECT 
(SELECT DISTINCT top 20
        [GUID],
        OrderNum,
        OrderDate,
    FROM TableA 
    For XML PATH('Activity'), Type
            ) as Body 
    For XML path('Message'),  TYPE

This query outputs all rows enclosed within <Message><Body> tag, just one cell with the XML of everything. I don't know where to include the info for the <Header> part.

The query above produces the following, which is incorrect/incomplete:

 <Message>
    <Body>
        <Activity>
            <GUID>sdlksdkljsdkl</GUID>
            <OrderNum>23423423</OrderNum>
            <OrderDate>20160431143248</OrderDate>
        </Activity>   
        <Activity>
            <GUID>sdfsdfsw4erw</GUID>
            <OrderNum>45560900</OrderNum>
            <OrderDate>20160531143248</OrderDate>
        </Activity>   
        <Activity>
            <GUID>retertertwew</GUID>
            <OrderNum>873409384</OrderNum>
            <OrderDate>20160631143248</OrderDate>
        </Activity>   
    </Body>
</Message>

How do we get the Header Part appended before the <Body> tag, while splitting the output into multiple rows? Thank you for any help.

EDIT: Mock data for the Body/Activity element :

 GUID       OrderNum           OrderDate      
 AAAA       1000               2017-04-13 12:00
 BBBB       2000               2017-04-13 12:00
 CCCC       3000               2017-04-13 12:00

I'm trying to get each row in their own xml as in the first XML block above.

Original Q&A

There are 1 best solutions below

**Shnugo** · Accepted Answer

Just add the Header as an XML-typed column:

DECLARE @tbl TABLE([GUID] UNIQUEIDENTIFIER,OrderNum BIGINT, OrderDate DATETIME);
INSERT INTO @tbl VALUES(NEWID(),1111111,{ts'2001-01-01 01:00:00'})
                      ,(NEWID(),2222222,{ts'2002-02-02 02:00:00'})
                      ,(NEWID(),3333333,{ts'2003-03-03 03:00:00'});

SELECT 
   (
    SELECT 'SomeMessageName' AS MessageName
          ,GETDATE() AS [TimeStamp]
          ,1 AS TraxingNum
    FOR XML PATH('Header'),TYPE 
   )
  ,(
    SELECT DISTINCT top 20
        [GUID],
        OrderNum,
        OrderDate
    FROM @tbl AS TableA 
    For XML PATH('Activity'), Type
   ) as Body 
For XML path('Message'),TYPE;

The result

<Message>
  <Header>
    <MessageName>SomeMessageName</MessageName>
    <TimeStamp>2017-04-13T10:20:09</TimeStamp>
    <TraxingNum>1</TraxingNum>
  </Header>
  <Body>
    <Activity>
      <GUID>79DE31D2-4727-4880-BB11-72564AE707B0</GUID>
      <OrderNum>3333333</OrderNum>
      <OrderDate>2003-03-03T03:00:00</OrderDate>
    </Activity>
    <Activity>
      <GUID>A9410295-5C78-4EC8-A904-8CB1D10563CF</GUID>
      <OrderNum>2222222</OrderNum>
      <OrderDate>2002-02-02T02:00:00</OrderDate>
    </Activity>
    <Activity>
      <GUID>3192B87D-7C1A-4B95-A477-E10D2FC2845F</GUID>
      <OrderNum>1111111</OrderNum>
      <OrderDate>2001-01-01T01:00:00</OrderDate>
    </Activity>
  </Body>
</Message>

Hints

In your example your dates are like this 20160531143248. You should use ISO8601 within XML always. Otherwise you will get extra pain, when you try to read this.
Using TOP without an ORDER BY is - uhm - at least dangerous... (you see, that the order in my result is not the expected order?)

UPDATE

According to your comments I think you are searching for one of these (test them with my mock-up table from above):

SELECT 
      'SomeMessageName' AS [Header/MessageName]
      ,GETDATE() AS [Header/TimeStamp]
      ,1 AS [Header/TraxingNum]
      ,[GUID] AS [Body/GUID]
      ,OrderNum AS [Body/OrderNum]
      ,OrderDate AS [Body/OrderDate]
FROM @tbl AS TableA
FOR XML PATH('Message'),TYPE;

SELECT 
      (SELECT
      'SomeMessageName' AS [Header/MessageName]
      ,GETDATE() AS [Header/TimeStamp]
      ,1 AS [Header/TraxingNum]
      ,[GUID] AS [Body/GUID]
      ,OrderNum AS [Body/OrderNum]
      ,OrderDate AS [Body/OrderDate]
      FOR XML PATH('Message'),TYPE)
FROM @tbl AS TableA;

Using FOR XML PATH to output Multiple Sibling Elements

There are 1 best solutions below

Hints

UPDATE

Related Questions in XML

Related Questions in T-SQL

Related Questions in SQL-SERVER-2012

Related Questions in FOR-XML-PATH

Related Questions in SELECT-FOR-XML

Trending Questions

Popular # Hahtags

Popular Questions