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         [email protected]     Joe         To
1,        [email protected]     Max         From
1,        [email protected]     NULL        To
1,        [email protected]  Secret      Bcc

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

<to>
   <displayName>Joe</displayName>
   <address>[email protected]</address>
</to>
<from>
   <displayName>Bob</displayName>
   <address>[email protected]</address>
</from>
<to>
   <address>[email protected]</address>
</to>
<bcc>
   <displayName>Secret</displayName>
   <address>[email protected]</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.

muhmud · Accepted Answer · 2013-04-10 15:28:55Z

1

with data as (
    select * from (
    values (1, '[email protected]', 'Joe', 'To', 1),
            (1, '[email protected]', 'Bob', 'From', 2),
            (1, '[email protected]', null, 'To', 3),
            (1, '[email protected]', '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('')

answered Apr 10, 2013 at 15:28

muhmud

4,6242 gold badges20 silver badges24 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Jay Over a year ago

I ended up not using the Pivot but this answered my question. Thanks!

Community · Accepted Answer · 2017-05-23 12:26:27Z

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>[email protected]</address>
</to>
<from>
   <displayName>Bob</displayName>
   <address>[email protected]</address>
</from>
<to>
   <address>[email protected]</address>
</to>
<bcc>
   <displayName>Secret</displayName>
   <address>[email protected]</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
-------------------------------- --------------------------------------------------
[email protected]                    to
[email protected]                    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
-------------------------------- -------------------------------- 
[email protected]                    Joe                              to
[email protected]                    Bob                              from
[email protected]                    NULL                             to
[email protected]                 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

Collectives™ on Stack Overflow

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

2 Answers 2

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related