CODEWITHSUNDEEP
sqlsql-serverxmlxquery
Brent Heritier
Brent Heritier

Reputation: 81

SQL XML Nested Elements

I am creating a query to send a bulk email app XML of everything it needs to assemble the appropriate email. I can't seem to get the nested elements to work correctly.

I am trying to get XML that looks like

    <EmailsToBeSent>
        <EmailToBeSent>
            <ReferenceId>1</ReferenceId>
                <Tags>
                    <Tag>
                        <Name>ToAddress</Name>
                        <Value>[email protected]</Value>
                    </Tag>
                    <Tag>
                        <Name>CCAddress</Name>
                        <Value>[email protected]</Value>
                    </Tag>
                    <Tag>
                        <Name>FromAddress</Name>
                        <Value>[email protected]</Value>
                    </Tag>
                </Tags>
            <SomethingElse>'asASas'</SomethingElse>                
        </EmailToBeSent>
    </EmailsToBeSent>

So far the closest I have gotten is:

SELECT  
    ReferenceId,
    (SELECT
         'ToAddress' AS "Tag/Name",
         ToAddress AS "Tag/Value",
         'CCAddress' AS "Tag/Name",
         CCAddress AS "Tag/Value",
         'FromAddress' AS "Tag/Name",
         FromAddress AS "Tag/Value"
     FROM 
         EmailTable AS ET
     WHERE 
         ET.ReferenceId = RT.ReferenceId
     FOR XML PATH('Tags'), TYPE), 
    'asASas' AS SomethingElse
FROM 
    RefTable AS RT
FOR XML PATH('EmailToBeSent'), ROOT('EmailsToBeSent')

Which results in:

<EmailsToBeSent>
    <EmailToBeSent>
        <ReferenceId>1</ReferenceId>
        <Tags>
            <Tag>
                <Name>ToAddress</Name>
                <Value>[email protected]</Value>
                <Name>CCAddress</Name>
                <Value>[email protected]</Value>
                <Name>FromAddress</Name>
                <Value>[email protected]</Value>
            </Tag>
        </Tags>
        <SomethingElse>'asASas'</SomethingElse>                
    </EmailToBeSent>
</EmailsToBeSent>

Upvotes: 2

Views: 796

Answers (1)

Alex Kudryashev
Alex Kudryashev

Reputation: 9460

Just add separators between groups.

SELECT  ReferenceId
    , (SELECT 'ToAddress'     AS "Tag/Name"
              , ToAddress     AS "Tag/Value"
              , null          AS "separator"
              , 'CCAddress'   AS "Tag/Name"
              , CCAddress     AS "Tag/Value"
              , null          AS "separator"
              , 'FromAddress' AS "Tag/Name"
              , FromAddress   AS "Tag/Value"
        FROM EmailTable AS ET
        WHERE ET.ReferenceId = RT.ReferenceId
        FOR XML PATH('Tags'),TYPE
        )
    , 'asASas' AS SomethingElse

FROM RefTable AS RT
FOR XML PATH('EmailToBeSent'), ROOT('EmailsToBeSent')

Upvotes: 3

Related Questions