Conditional XML tags in XQUERY SQL Server

Question

I need to make the XML nodes disappear if the value is NULL or empty string.

Using the {$contact/Name} works just fine, but in this case I cannot do it.

Here is the code:

DECLARE @t TABLE 
  (  
    id int,  
    Name varchar(20),  
    OfficeNumber varchar(20),  
    MobilePhone varchar(20),  
    Faxnumber varchar(20)  
  )  

INSERT @t SELECT 1,'Liang', NULL, '654238','5478' 
INSERT @t SELECT 2,'Jia','123512','45689','12565478' 
INSERT @t SELECT 3,'Hui','9542654','123789','42165'

SELECT 
    (  
        SELECT 
            id AS [@id],  
            Name AS [Name],  
            OfficeNumber AS [phonenumber/@OfficePhone],  
            MobilePhone AS [phonenumber/@MobilePhone],  
            Faxnumber AS [phonenumber/@Faxnumber]  
        FROM @t AS A  
        FOR XML PATH('contact'),TYPE  
    ).query('
       <root>
       {
         for $contact in /contact
             return
                <contact id="{$contact/@id}">
                   {$contact/Name}
                    <PhoneNumber type="Office">{data($contact/phonenumber/@OfficePhone)}</PhoneNumber>
                    <PhoneNumber type="Mobile">{data($contact/phonenumber/@MobilePhone)}</PhoneNumber>
                    <PhoneNumber type="Fax">{data($contact/phonenumber/@Faxnumber)}</PhoneNumber>
                </contact>
       }
       </root>
    ')

Current output is:

<root>
  <contact id="1">
    <Name>Liang</Name>
    <PhoneNumber type="Office" />
    <PhoneNumber type="Mobile">654238</PhoneNumber>
    <PhoneNumber type="Fax">5478</PhoneNumber>
  </contact>
...
</root>

The desired output would be:

<root>
  <contact id="1">
    <Name>Liang</Name>
    <PhoneNumber type="Mobile">654238</PhoneNumber>
    <PhoneNumber type="Fax">5478</PhoneNumber>
  </contact>
...
</root>

I'm using SQL Server 2008 and 2012.

Gottfried Lesigang · Accepted Answer · 2019-05-22 09:18:12Z

Might be, that you can use something like this for an easy approach:

  SELECT 
         id AS [@id]
        ,Name AS [Name] 
        ,CASE WHEN OfficeNumber IS NOT NULL THEN 'Office' END AS [phonenumber/@type]  
        ,CASE WHEN OfficeNumber IS NOT NULL THEN OfficeNumber END AS [phonenumber]  
        ,''
        ,CASE WHEN MobilePhone IS NOT NULL THEN 'Mobile' END AS [phonenumber/@type]  
        ,CASE WHEN MobilePhone IS NOT NULL THEN MobilePhone END AS [phonenumber] 
        ,''
        ,CASE WHEN Faxnumber IS NOT NULL THEN 'Fax' END AS [phonenumber/@type]
        ,CASE WHEN Faxnumber IS NOT NULL THEN Faxnumber END AS [phonenumber]

    FROM @t AS A  
    FOR XML PATH('contact'),ROOT('root'),TYPE  ;

The idea:

You can add several elements with the same name, when you place a ,'' in between. This forces the engine to close one element and start the next. Otherwise you'd get an error.
The CASE WHEN will lead to a NULL-value for the whole element. NULL values are omitted by defualt.

the result

<root>
  <contact id="1">
    <Name>Liang</Name>
    <phonenumber type="Mobile">654238</phonenumber>
    <phonenumber type="Fax">5478</phonenumber>
  </contact>
  <contact id="2">
    <Name>Jia</Name>
    <phonenumber type="Office">123512</phonenumber>
    <phonenumber type="Mobile">45689</phonenumber>
    <phonenumber type="Fax">12565478</phonenumber>
  </contact>
  <contact id="3">
    <Name>Hui</Name>
    <phonenumber type="Office">9542654</phonenumber>
    <phonenumber type="Mobile">123789</phonenumber>
    <phonenumber type="Fax">42165</phonenumber>
  </contact>
</root>

Collectives™ on Stack Overflow

Conditional XML tags in XQUERY SQL Server

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related