SQL to generate an XML

Question

I got stuck with an xml creation challenge.

My challenge is to generate an xml from a data table with below definition

Table marketingCampaign

    id      INT
    campaign_code
    campaign_start_date
    activity_code
    target_email_address
    comms

This xml will be forwarded to a 3rd party application and expecting the following format, below.

<xml  ....>
    <marketCampaign type='C'>
        <campaign>
            <campaign_code/>
            <campaign_start_date/>
        <campaign>
    </marketCampaign> 
    <marketCampaign type='A'>
        <activity>
            <activity_code/> 
            <campaign_code/>
        <activity>
    </marketCampaign> 
    <marketCampaign type='M'>
        <message>
            <activity_code/> 
            <target_email_address/>
        <message>
    </marketCampaign> 
    </xml>

I want to limit this using plain SQL scripts (if possible), so I have used the 'FOR XML PATH' functionality. I have tried different combination to achieve above formatting without writing multiple sql statement with no success. I have managed to generate below xml but the 3rd party apps rejected this formatting.

<xml  ....>
<marketCampaign type='C'>
    <campaign>
        <campaign_code/>
        <campaign_start_date/>
        <activity>
            <activity_code/> 
            <campaign_code/>
            <message>
                <activity_code/> 
                <target_email_address/>
            <message>
        </activity>
    <campaign>
</marketCampaign> 
</xml>

My question is it possible to accomplish the above xml format with a single SQL, what is the most efficient approach to achieve this?

I can write a lengthy script to generate the xml format as per the 3rd party requirements, however I am trying to avoid that option if possible.

UPDATE

Below is the current SQL I tried

SELECT DISTINCT
    'C' AS "@resultsType",
    c.campaign_code AS "campaign/campaign_code",
    CONVERT(DATE, campaign_start_date, 102) AS "campaign/campaign_start_date",
    CONVERT(DATE, GETDATE(), 102) AS "campaign/authorizeDate", 
    'A' AS "marketCampaign/@resultsType",
    c.activity_code AS "activity/code",
    c.campaign_code AS "activity/campaign_code",
    'M' AS "marketCampaign/@resultsType",
    c.activity_code AS "message/activityCode",
    c.target_email_address AS "message/target_email_address",
    c.comms AS "message/fileText"
FROM
    dev.campaign.marketting_data c
FOR XML PATH('marketCampaign'), ROOT('xml')

The thing is I do not want to repeat "resultType=C" multiple times, unless its a new set of campaign_code, same rules applies to the activity_code.

XML is just text so you can just use text functions to stick it all together as required — Nick.Mc, Mar 11 '19 at 23:51
I see the format is a little different. Please post the `FOR XML` T-SQL that you used. — Nick.Mc, Mar 11 '19 at 23:55
You could do some wonky select, I guess... Such as the following: `SELECT (SELECT '@type' = 'C', (SELECT campaign_code, campaign_start_date FOR XML PATH('campaign'), TYPE) FOR XML PATH ('marketCampaign'), TYPE), (SELECT '@type' = 'A', etc...) FROM myTable FOR XML PATH ('xml');` — ZLK, Mar 12 '19 at 00:18
@Nick.McDermaid I have updated my original post with the sample code I currently done. — fo2bug, Mar 12 '19 at 00:19
Note: That could also be written like `SELECT (SELECT '@type' = 'C', 'campaign/campaign_code' = campaign_code, 'campaign/campaign_start_date' = campaign_start_date FOR XML PATH ('marketCampaign'), TYPE), (SELECT '@type' = 'A', 'activity/activity_code' = activity_code... etc) FROM mytable FOR XML PATH ('xml');` if that formatting is preferred. — ZLK, Mar 12 '19 at 00:28
XML is hierarchical by design. SQL isn't hierarchical, so the code will always get messier the deeper the levels become. The code will become horribly complex if the requirements change (maintainability is important). So *use a better tool*, such as [Python ElementTree](https://pymotw.com/2/xml/etree/ElementTree/create.html), or .NET [LINQ to XML](https://stackoverflow.com/questions/2076442/best-net-method-to-create-an-xml-doc). — bruceskyaus, Mar 12 '19 at 05:40
Please provide sample data and expected output (best is a [mcve]). — Shnugo, Mar 12 '19 at 09:18
@Nick.McDermaid I think it is rather dangerous to claim that *XML is just text so you can just use text functions*... XML is much more than *text with some fancy extras*. It is a *text based* container though... I've seen so many hacky text-based solutions fail (after passing all tests) because of escaped characters, weird control characters or forbidden namings etc. — Shnugo, Mar 12 '19 at 09:21
That's a fair comment. I agree it shouldn't just be treated as text and generated with text-only functions. However occasionally that is a pragmatic solution. — Nick.Mc, Mar 12 '19 at 10:30

score 1 · Accepted Answer · answered Mar 13 '19 at 23:02

Quick Update

I used one of the suggestions and it seems to do the trick. I did try LINQ to XML however my client might have some challenge maintaining the code so I tried to keep it simple.

Much appreciated all for your help.

I used the SQL script below.

  SELECT
    -- Campaign
    ( SELECT 
          '@Type'                           = 'C'
         ,'campaign/campaign_code'          = mc.campaign_code
         ,'campaign/camapaign_start_date'   = mc.camapaign_start_date
         FOR XML PATH('marketCampaign'), TYPE),
    -- Activity
    (SELECT
         '@Type'                = 'A'
        ,'activity/activity_code'   = mc.activity_code
        ,'activity/campaign_code'   = mc.campaign_code
        FOR XML PATH('marketCampaign'), TYPE),
    -- Message
    (SELECT
        '@Type'                  = 'I'
        ,'message/activity_code'         = mc.activity_code
        ,'message/target_email_address'  = ISNULL(mc.comms_supporter_id,'')
        ,'message/email_content'         =  '===TEST ===='
        ,'message/creationDate'          = format( DATEADD(month, -3,  getdate()), 'dd/MM/yyyy')  
        ,'message/time'                  = '00:00:00'
        FOR XML PATH('marketCampaign'), TYPE)
 FROM marketingCampaign mc
FOR XML PATH(''), ROOT('xml')

SQL to generate an XML

1 Answers1