使用 FOR XML 从 SQL Server 2008 R2 以 <element /> 形式返回空字段或空字段

Return empty or null fields as <element /> from SQL Server 2008 R2 with FOR XML

提问by J Brune

I am running a query from SQL Server 2008 R2 using FOR XML PATH. My only issue is that I want ALL elements to appear, even if they are NULL and I want empty (or null) elements to return as

我正在使用FOR XML PATH. 我唯一的问题是我希望所有元素都出现，即使它们是 NULL 并且我希望空（或 null）元素返回为

<MyElement />

Not as

不像

<MyElement></MyElement>

采纳答案by Mikael Eriksson

You can query the field in a sub-query in the filed list, using for xml, create both versions of empty element.

您可以在字段列表的子查询中查询字段，使用for xml，创建两个版本的空元素。

declare @T table
(
  ID int identity primary key,
  Name nvarchar(10)
)

insert into @T(Name)
select 'Name 1' union all
select null union all
select 'Name 2'

select ID,
       (select Name as '*' for xml path(''), type) as Name,
       (select Name as '*' for xml path('Name'), type)
from @T
for xml path('row')

Result:

结果：

<row>
  <ID>1</ID>
  <Name>Name 1</Name>
  <Name>Name 1</Name>
</row>
<row>
  <ID>2</ID>
  <Name></Name>
  <Name />
</row>
<row>
  <ID>3</ID>
  <Name>Name 2</Name>
  <Name>Name 2</Name>
</row>

回答by gngolakia

Have look at following Example

看看下面的例子

DECLARE @t TABLE (
    id INT, Name1 VARCHAR(20), 
    Value1 VARCHAR(20), Name2 VARCHAR(20), 
    Value2 VARCHAR(20))

INSERT INTO @t (id, name1, value1, name2, value2)
SELECT 1, 'PrimaryID', NULL, 'LastName', 'Abiola' UNION ALL
SELECT 2, 'PrimaryID', '200', 'LastName', 'Aboud'


SELECT
(
    SELECT 
        name1 AS 'Parameter/Name',
        value1 AS 'Parameter/Value'
    FROM @t t2 WHERE t2.id = t.id 
    FOR XML PATH(''), ELEMENTS XSINIL, TYPE
),
(
    SELECT 
        name2 AS 'Parameter/Name',
        value2 AS 'Parameter/Value'
    FROM @t t2 WHERE t2.id = t.id 
    FOR XML PATH(''), TYPE
)
FROM @t t
FOR XML PATH('T2Method')

You will get output like following

你会得到如下输出

<T2Method>
  <Parameter xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
    <Name>PrimaryID</Name>
    <Value xsi:nil="true" />
  </Parameter>
  <Parameter>
    <Name>LastName</Name>
    <Value>Abiola</Value>
  </Parameter>
</T2Method>

<T2Method>
  <Parameter xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
    <Name>PrimaryID</Name>
    <Value>200</Value>
  </Parameter>
  <Parameter>
    <Name>LastName</Name>
    <Value>Aboud</Value>
  </Parameter>
</T2Method>

Note the "value" element in the first "paremeter" element. The value is NULL and still an element is generated. Note the addition of a special attribute "xsi:nil" to indicate that the element is empty.

请注意第一个“参数”元素中的“值”元素。该值为 NULL 并且仍然生成一个元素。请注意添加了一个特殊属性“xsi:nil”以指示该元素为空。