Sql for xml:如何避免特定字段作为属性输出? [英] Sql for xml: how to avoid a specific field is output as attribute?

问题描述

这个问题的陈述点是 控制 XML 元素嵌套使用FOR XML

我希望输出从

<security AccessLevel="5" />

到

<security>5<security/>

基本上不是将 AccessLevel 显示为属性，我希望它的值成为元素 security 的值.如何达到这样的结果.为清楚起见，我将链接帖子中的示例复制到此处:

Basically instead of displaying AccessLevel as attribute I would like its value becomes the value of the element security. How to achieve such a result. I copy here the example from the linked post for clarity:

DECLARE @Employees table(  
    EmpID int NOT NULL,  
    Name nvarchar(50),  
    Surname nvarchar(50),  
    DateOfBirth date,
    DepartmentID int,
    AccessLevel int);
insert into  @Employees    values ('1', 'John','Doe','1980-01-31',100,5)
insert into  @Employees    values ('2', 'Mary','Rose','1971-02-27',102,3)
insert into  @Employees    values ('3', 'Luke','Perry','1995-12-01',104,1)

select
  employee.Name,
  employee.Surname,
  employee.DateOfBirth,
  department.DepartmentID, 
  security.AccessLevel  -- THIS IS THE INVOLVED FIELD
from @Employees employee
join @Employees department on department.DepartmentID = employee.DepartmentID
join @Employees security on security.AccessLevel = employee.AccessLevel
for xml auto

推荐答案

我会在别名中使用 @ 来生成 xml<中的 attributes/代码>.要将 accesslevel 作为元素，请不要将 @ 添加到别名

I would do this using @ in alias name to generate the attributes in xml. To get the accesslevel as element just don't add @ to alias name

类似的东西

SELECT NAME         AS [@Name],
       Surname      AS [@Surname],
       DateOfBirth  AS [@DateOfBirth],
       DepartmentID AS [department/@DepartmentID],
       AccessLevel  AS [department/security]
FROM   @Employees
FOR xml path('employee') 

结果:

<employee Name="John" Surname="Doe" DateOfBirth="1980-01-31">
  <department DepartmentID="100">
    <security>5</security>
  </department>
</employee>
<employee Name="Mary" Surname="Rose" DateOfBirth="1971-02-27">
  <department DepartmentID="102">
    <security>3</security>
  </department>
</employee>
<employee Name="Luke" Surname="Perry" DateOfBirth="1995-12-01">
  <department DepartmentID="104">
    <security>1</security>
  </department>
</employee>

这篇关于Sql for xml:如何避免特定字段作为属性输出?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！