CODEWITHSUNDEEP
sql-serverxml
Caynadian
Caynadian

Reputation: 757

SQL Server 2008 XML Empty Node

I am trying to get some data returned in XML format from a 2005 and 2008R2 SQL Servers. I have two tables - a master and a detail. I want the XML to give the master information and within that the detail information (if it exists) for each master record queried. If no detail information exists, it should return an empty node NOT nothing. For example, when detail records exist, I want this:

<masters>
  <master>
    <flda>value</flda>
    <fldb>value</fldb>
    <details>
      <detail>
        <dtla>value</dtla>
      </detail>
    </details>
  </master>
</masters>

When NO detail records exist, I want this:

<masters>
  <master>
    <flda>value</flda>
    <fldb>value</fldb>
    <details />
  </master>
</masters>

I have the following query that gets the data correctly when there are detail records but I don't know how to force an empty node when the details are absent:

select m.flda, m.fldb,
       (select d.dtla
        from detail d
        where (d.mastid = m.id)
        for xml path('detail'), type) as 'details'
from master m
where (m.id = @id)
for xml path('master'), root('masters');

Upvotes: 1

Views: 3458

Answers (1)

Caynadian
Caynadian

Reputation: 757

I had asked the same question on another site and got an answer there (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/Q_28071909.html) from "PortletPaul". For those without an account there, the solution is to use the ISNULL directive as follows:

select m.flda, m.fldb,
   isnull((select *
           from detail d
           where (d.mastid = m.id)
           for xml path('detail'), type),'') as 'details'
from master m
where (m.id = @id)
for xml path('master'), root('masters');

This will give you something like the following:

<masters>
  <master>
    <flda>value</flda>
    <fldb>value</fldb>
    <details></details>
  </master>
</masters>

It's not the <details /> that I wanted but it's close enough.

Upvotes: 3

Related Questions