对于在SQL Server 2005 Express 2005中生成空元素标记的xml类型 [英] For xml type generating null element tags in sql server 2005 express 2005

问题描述

我尝试了以下操作，但它不会为空/空值生成xml元素.有人可以指出我犯的错误.我期望的Xml输出

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>

请注意，根本没有日期标签.这是sql代码

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.另外，如果我添加OrderBy或GroupBy或Distict，也会收到错误消息.

更多信息:xml充当DataSet的DataSource，可以使用DataSet类的ReadXml方法读取它.

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

推荐答案

我实际上看不到您所看到的确切信息.当 CreatedOn为空时，我得到的值是 1900-01-01T00:00:00 .

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

我猜这是因为CreatedOn是datetime.如果XML已针对XSD进行了验证，则xs:dateTime的空标签是不允许的.

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',

当 CreatedOn为null

结果(对我而言)为<日期/>

编辑1 空评论标签，如果没有评论，则为空标签

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')

结果

<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>

这篇关于对于在SQL Server 2005 Express 2005中生成空元素标记的xml类型的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！