SQL XML嵌套元素

Question

我正在创建一个查询来发送批量电子邮件应用程序XML，它包含组装相应电子邮件所需的所有内容。我似乎无法让嵌套元素正常工作。

我试图让XML，看起来像

<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> 

到目前为止，我已经得到最接近的是：

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') 

导致：

<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> 

Answer 1

只需添加分隔符组之间。

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')