Блог

У меня есть одна таблица в следующем формате.

Я хочу данные в формате XML для каждого сотрудника, используя SQL-запрос, как показано ниже

 <Employee>
  <EmployeeID>1</EmployeeID>
  <FirstName>Jon</FirstName>
  <LastName>Snow</LastName>
  <City>North of the wall</City>
  <Age>28</Age>
  <Gender>Male</Male>
</Employee>

<Employee>
  <EmployeeID>2</EmployeeID>
  <FirstName>Arya</FirstName>
  <LastName>Stark</LastName>
  <City>Winterfell</City>
  <Age>14</Age>
  <Gender>Female</Male>
</Employee>
  

Я попытался использовать запрос,

 SELECT  DISTINCT EmployeeID ,(  
    SELECT   
    EmployeeID,  
    (SELECT    PDFKey ,   
        Replace(Value,char(CAST(0x001A as int)),'') as Value  FROM TempData EE WHERE  E.EmployeeID =EE.EmployeeID FOR XML PATH(''),   
    TYPE, ELEMENTS)   
    AS Employee  
    FROM   
    TempData E WHERE o.EmployeeID =E.EmployeeID  
    GROUP BY EmployeeID  
    FOR XML AUTO, ELEMENTS  
    ) AS XMLtag FROM TempData O 
  

Но он предоставляет данные в формате, подобном,

  <E>
 <EmployeeID>1</EmployeeID>
 <Employee>
    <PDFKey>FirstName</PDFKey>
    <Value>Jon</Value>
    <PDFKey>LastName</PDFKey>
    <Value>Snow</Value>
    <PDFKey>City</PDFKey>
    <Value>North of the wall</Value>
    <PDFKey>Age</PDFKey>
    <Value>28</Value>
    <PDFKey>Gender</PDFKey>
    <Value>Male</Value>
  </Employee>
  </E>
  

И я хочу, чтобы на каждого сотрудника приходилось по одной строке, в которой будет 2 столбца: EmployeeID и XMLdata.

Пожалуйста, обратитесь к изображению ниже для ожидаемого результата, введите описание изображения здесь

Вы можете использовать PIVOT ДЛЯ XML-ПУТИ:

 SELECT  *
FROM Employee
PIVOT (
    MAX([Value]) FOR PDFKey IN ([FirstName],[LastName],[City],[Age],[Gender])
) as u
FOR XML PATH('Employee'), TYPE
  

Вывод:

 <Employee>
  <EmployeeID>1</EmployeeID>
  <FirstName>Jon</FirstName>
  <LastName>Snow</LastName>
  <City>North of the wall</City>
  <Age>28</Age>
  <Gender>Male</Gender>
</Employee>
<Employee>
  <EmployeeID>2</EmployeeID>
  <FirstName>Arya</FirstName>
  <LastName>Stark</LastName>
  <City>Winterfell</City>
  <Age>14</Age>
  <Gender>Female</Gender>
</Employee>
  

После поворота вы получите таблицу, подобную этой:

 EmployeeID  FirstName   LastName    City                Age Gender
1           Jon         Snow        North of the wall   28  Male
2           Arya        Stark       Winterfell          14  Female
  

После этого вы добавляете ДЛЯ XML с режимом PATH

В режиме ПУТИ имена столбцов или псевдонимы столбцов обрабатываются как выражения XPath. Эти выражения указывают, как значения отображаются в XML. Каждое выражение XPath является относительным выражением XPath, которое предоставляет тип элемента., такой как атрибут, элемент и скалярное значение, а также имя и иерархию узла, который будет сгенерирован относительно элемента строки.

Также я добавил ТИП

Поддержка SQL Server для xml (Transact-SQL) позволяет при необходимости запросить, чтобы результат запроса FOR XML был возвращен как тип данных xml, указав директиву TYPE.

Редактировать

 ;WITH cte AS (
SELECT  *
FROM Employee
PIVOT (
    MAX([Value]) FOR PDFKey IN ([FirstName],[LastName],[City],[Age],[Gender])
) as u
)

SELECT  e.EmployeeID,
        (SELECT *
        FROM cte
        WHERE EmployeeID = e.EmployeeID
        FOR XML PATH('Employee'), TYPE) as XMLData
FROM cte e
  

Вывод:

 EmployeeID  XMLData
1           <Employee><EmployeeID>1</EmployeeID><FirstName>Jon</FirstName><LastName>Snow</LastName><City>North of the wall</City><Age>28</Age><Gender>Male</Gender></Employee>
2           <Employee><EmployeeID>2</EmployeeID><FirstName>Arya</FirstName><LastName>Stark</LastName><City>Winterfell</City><Age>14</Age><Gender>Female</Gender></Employee>