How can I convert a column value to a xml element name using SQL and FOR XML

Question

I have the following table rows in MyTable:

MessageID EMailAddress      DisplayName RecipientType
1         joe@email.com     Joe         To
1,        Bob@email.com     Max         From
1,        Max@email.com     NULL        To
1,        Secret@email.com  Secret      Bcc

I am trying to convert this to XML that looks like the following:

<to>
   <displayName>Joe</displayName>
   <address>joe@email.com</address>
</to>
<from>
   <displayName>Bob</displayName>
   <address>Bob@email.com</address>
</from>
<to>
   <address>Max@email.com</address>
</to>
<bcc>
   <displayName>Secret</displayName>
   <address>Secret@email.com</address>
</bcc>

I am close but cannot figure out how to convert the RecipientType to the parent element for the other two columns

SELECT 
a.EmailAddress as address, 
a.DisplayName as displayName
FROM MyTable a
WHERE a.Id = 1
FOR XML PATH('address'), TYPE 

Is there a way to make the "FOR XML PATH('address')" be the RecipientType instead of a hard coded 'address'? Or is there another alternative?

I had also tried playing around with PIVOT but it didn't seem to help.

I know I could use dynamic SQL but I am trying to stay away from that.

Answer 1

Maybe this can get you started: I think you're going to need a element, regardless.

DECLARE @data XML;

SET @data = 

N'
<root>
<to>
   <displayName>Joe</displayName>
   <address>joe@email.com</address>
</to>
<from>
   <displayName>Bob</displayName>
   <address>Bob@email.com</address>
</from>
<to>
   <address>Max@email.com</address>
</to>
<bcc>
   <displayName>Secret</displayName>
   <address>Secret@email.com</address>
</bcc>
</root>';




SELECT 
      T.myAlias.value('(.)[1]', 'VARCHAR(32)') AS emailAddress
    , T.myAlias.value('fn:local-name(..)', 'nvarchar(50)') as t
FROM 
    @data.nodes('//address') AS T(myAlias)
where
    T.myAlias.value('fn:local-name(..)', 'nvarchar(50)') = 'to'
;

Results:

emailAddress                     t
-------------------------------- --------------------------------------------------
joe@email.com                    to
Max@email.com                    to

---

SELECT

      T.myAlias.value('(.)[1]', 'VARCHAR(32)') AS emailAddress
    , T.myAlias.value('(../displayName)[1]', 'VARCHAR(32)') AS displayName
    , T.myAlias.value('fn:local-name(..)', 'nvarchar(50)') as RecipientType
FROM 
    @data.nodes('//address') AS T(myAlias)

;


emailAddress                     displayName                      RecipientType
-------------------------------- -------------------------------- 
joe@email.com                    Joe                              to
Bob@email.com                    Bob                              from
Max@email.com                    NULL                             to
Secret@email.com                 Secret                           bcc

There's everything except the Id.

One issue, position() is not fully supported in Xml Shredding. :<

http://connect.microsoft.com/SQLServer/feedback/details/383888/fully-support-position-in-xquery

and also seen in this post:

Finding node order in XML document in SQL server

Answer 2

with data as (
    select * from (
    values (1, 'joe@email.com', 'Joe', 'To', 1),
            (1, 'Bob@email.com', 'Bob', 'From', 2),
            (1, 'Max@email.com', null, 'To', 3),
            (1, 'Secret@email.com', 'Secret', 'Bcc', 4)
    ) t(MessageID, EMailAddress, DisplayName, RecipientType, Ordering)
    where MessageID = 1
), pivoted as (
    select *
    from data pivot (max(EmailAddress) for RecipientType in ([To], [From], [Bcc])) t
)
select (case when [To] is not null then DisplayName else null end) as 'to/displayName', [To] as 'to/address',
        (case when [From] is not null then DisplayName else null end) as 'from/displayName', [From] as 'from/address',
        (case when [Bcc] is not null then DisplayName else null end) as 'bcc/displayName', [Bcc] as 'bcc/address'
from pivoted
order by Ordering
for xml path('')