For xml type generating null element tags in sql server 2005 express 2005
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 x开发者_高级运维ml 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.
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>
精彩评论