For xml type generating null element tags in sql s

Question

Hi i tried the below but it doesn't generate xml element for empty/null values. Could someone point me the mistake i am making. Xml Output i expect

  <Board>
    <BoardId>1</BoardId>
    <Title>Introduction to modal popup control</Title>
    <Desc>The ModalPopup extender allows you to display content in an element that</Desc>
  </Board>
  <Comment>
    <CommentId>1</CommentId>
    <Comment>Typing a comment and sending it is such a neat stuff</Comment>
    <Date>2011-03-25T15:24:43</Date>
  </Comment>
  <Comment>
    <CommentId>2</CommentId>
    <Comment>Board comment 123 123 123</Comment>
    <Date></Date>
  </Comment>
</Board>

Notice the empty date element in the last comment tag element. Actual output got,

  <Board>
    <BoardId>1</BoardId>
    <Title>Introduction to modal popup control</Title>
    <Desc>The ModalPopup extender allows you to display content in an element that</Desc>
  </Board>
  <Comment>
    <CommentId>1</CommentId>
    <Comment>Typing a comment and sending it is such</Comment>
    <Date>2011-03-25T15:24:43</Date>
  </Comment>
  <Comment>
    <CommentId>2</CommentId>
    <Comment>Board comment 123 123 123</Comment>
  </Comment>
</Board>

notice that date tag is not present at all. Here is the sql code

SELECT
Board.BoardId  'Board/BoardId',
Board.BoardTitle 'Board/Title',
ISNULL(Board.BoardDesc,'')  'Board/Desc',
(
SELECT 
Comments.CommentId 'CommentId',
Comments.Comment 'Comment',
isnull(Comments.CreatedOn,'') 'Date'
from us_boardcomments comments where comments.boardId = board.boardid
for xml path('Comment'),type
)
FROM US_Board Board where boardId = '1'
for xml path('Board')

2. Also if i add OrderBy or GroupBy or Distict i get error.

More Info: The xml acts as DataSource to DataSet which is read using ReadXml method of the DataSet class.

Answer 1

I actually don't see exactly what you see. I get the value 1900-01-01T00:00:00 when CreatedOn is null.

I guess this behaviour is because CreatedOn is datetime. If the XML is validated against a XSD, empty tags for xs:dateTime is not allowed.

To get what you want you can do like this.

isnull(convert(varchar(19), Comments.CreatedOn, 126), '') 'Date',

Result (for me) is <Date /> when CreatedOn is null

Edit 1 Empty Comment tag with empty tags, when there are no comments

SELECT
Board.BoardId  'Board/BoardId',
Board.BoardTitle 'Board/Title',
ISNULL(Board.BoardDesc,'')  'Board/Desc',
(
SELECT 
isnull(convert(varchar(10), Comments.CommentId), '') 'CommentId',
isnull(Comments.Comment, '') 'Comment',
isnull(convert(varchar(19), Comments.CreatedOn, 126),'') 'Date'
from US_Board Board2
  left outer join us_boardcomments comments
    on comments.boardId = Board2.boardid
  where Board2.boardid = Board.boardid
for xml path('Comment'),type
)
FROM US_Board Board where boardId = '1'
for xml path('Board')

Result

<Board>
  <Board>
    <BoardId>1</BoardId>
    <Title>Introduction to modal popup control</Title>
    <Desc>The ModalPopup extender allows you to display content in an element that</Desc>
  </Board>
  <Comment>
    <CommentId />
    <Comment />
    <Date />
  </Comment>
</Board>