Using FOR XML PATH to output Multiple Sibling Elements

Question

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.

Please create mock data for us so we can create the same table as you. — Hiren Thanki
– Hiren Thanki, Commented Apr 13, 2017 at 8:14

Gottfried Lesigang · Accepted Answer · 2017-04-13 22:15:30Z

2

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;

edited Apr 13, 2017 at 22:15

answered Apr 13, 2017 at 8:21

Gottfried Lesigang

67.6k9 gold badges60 silver badges124 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

FMFF Over a year ago

Point Taken re TOP without ORDER BY; I was typing the question in a hurry, and it is my mistake that I forgot; Sorry. Agree with you re-the date format as well.

FMFF Over a year ago

THank you. I'm trying to get only one Activity element within the Body element, and the next one should be in the next row. How do I force SQL Server to break it down to different rows? I hope this make sense.

Collectives™ on Stack Overflow

Using FOR XML PATH to output Multiple Sibling Elements

1 Answer 1

Hints

UPDATE

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Hints

UPDATE

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related