Help Using COALESCE

Question

Hi,

I have the below code.

SELECT
AccountNumber AS [Key],
( SELECT
'Phone' AS "ITContact/ITConnectionType",
MainPhone AS "ITContact/ConnectionSequence",
NULL,
'Fax' AS "ITContact/ITConnectionType",
Fax AS "ITContact/ConnectionSequence",
NULL,
'EMail' AS "ITContact/ITConnectionType",
EmailAddress1 AS "ITContact/ConnectionSequence"
FROM Accounts
WHERE AccountNumber = ?
FOR XML PATH('ItContacts') ,
TYPE
)
FROM Accounts
WHERE AccountNumber = ?
FOR XML PATH('BusinessPartner')

If there is no data in the source columns for "MainPhone", "Fax" or "EmailAddress1", then my XML output will be as follows:

<BusinessPartner>
  <Key>1487</Key>
  <ItContacts>
    <ITContact>
      <ITConnectionType>Phone</ITConnectionType>
    </ITContact>
    <ITContact>
      <ITConnectionType>Fax</ITConnectionType>
    </ITContact>
    <ITContact>
      <ITConnectionType>EMail</ITConnectionType>
    </ITContact>
  </ItContacts>
</BusinessPartner>

I'd like the "ITConnectionType" sections to only appear if the corresponding "ConnectionSequence" fields contained data. Can I use COALESCE for this? I'm struggling with getting the correct syntax.

Any help would be appreciated.

Many Thanks,

---

James Bratley

Answer 1

You need to extract the contact data into a separate table (expression):

WITH    Normalized
          AS ( SELECT   AccountNumber AS [Key] ,
                        'Phone' AS [Type] ,
                        MainPhone AS [Value]
               FROM     Accounts
               WHERE    NOT MainPhone IS NULL
               UNION ALL
               SELECT   AccountNumber ,
                        'Fax' ,
                        Fax
               FROM     Accounts
               WHERE    NOT Fax IS NULL
               UNION ALL
               SELECT   AccountNumber ,
                        'EMail' ,
                        EmailAddress1
               FROM     Accounts
               WHERE    NOT EmailAddress1 IS NULL
             )
    SELECT  *
    FROM    Normalized
    WHERE   AccountNumber = '?'

Answer 2

Thanks for the help Stefan. I'm a bit confused with your example. Would this still allow for the use of FOR XML, and would it be able to keep my existing XML structure with <Key> contained in the root and the contact data stored in <ITContacts>? I can't figure it out from your code.

---

James Bratley

Answer 3

Hello James ,

  You may have to CASE statement. Try something as below

  Note : SQL not test.Please consider this as a Pseudocode.

SELECT
AccountNumber AS [Key],
( SELECT
CASE 
WHEN MainPhone IS NULL THEN NULL
ELSE 'Phone' 
END  AS "ITContact/ITConnectionType",
MainPhone AS "ITContact/ConnectionSequence",
NULL,
CASE 
WHEN Fax IS NULL THEN NULL
ELSE 'Fax' 
END 
 AS "ITContact/ITConnectionType",
Fax AS "ITContact/ConnectionSequence",
NULL,
CASE 
WHEN EmailAddress1 IS NULL THEN NULL
ELSE 'EMail' 
END 
AS "ITContact/ITConnectionType",
EmailAddress1 AS "ITContact/ConnectionSequence"
FROM Accounts
WHERE AccountNumber = ?
FOR XML PATH('ItContacts') ,
TYPE
)
FROM Accounts
WHERE AccountNumber = ?
FOR XML PATH('BusinessPartner')

---

Best Regards Sorna

Answer 4

Thanks Sorna, that works perfectly :)

---

James Bratley