CODEWITHSUNDEEP
xmlt-sqlsql-server-2012for-xml-pathselect-for-xml
FMFF
FMFF

Reputation: 1718

Using FOR XML PATH to output Multiple Sibling Elements

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.

Upvotes: 1

Views: 4971

Answers (1)

Gottfried Lesigang
Gottfried Lesigang

Reputation: 67311

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;

Upvotes: 2

Related Questions