sql server - raw - Sql para xml: cómo evitar un campo específico se muestra como atributo?

select xml sql server example

Question

El punto de referencia para este objetivo es Control en elementos XML anidando usando FOR XML

Me gustaría que la salida cambie de

<security AccessLevel="5" />

a

<security>5<security/>

Básicamente, en lugar de mostrar AccessLevel como atributo, me gustaría que su valor se convierta en el valor de la security elemento. Cómo lograr tal resultado. Copio aquí el ejemplo de la publicación vinculada para mayor claridad:

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

Answer 1

Haría esto usando @ en alias name para generar los attributes en xml . Para obtener el nivel de accesslevel como elemento, simplemente no agregue @ al nombre de alias

Algo como esto

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

Resultado:

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