SQL Server FOR XML PATH ROOT element with attribute (Path Mode symbol @ or / )

Question

I need output something like this

<Employee EmpID="4">
  <FirstName>Rob</FirstName>
  <LastName>Walters</LastName>
</Employee>
<Employee EmpID="168">
  <FirstName>Rob</FirstName>
  <MiddleName>T</MiddleName>
  <LastName>Caron</LastName>
</Employee>

But the SQL to do this:

SELECT e.EmployeeID AS "@EmpID",
   c.FirstName, 
   c.MiddleName, c.LastName
FROM Employee AS e 
INNER JOIN Contact AS c 
ON c.ContactID = e.ContactID
WHERE c.FirstName = 'Ross'
FOR XML PATH (''), ROOT ('Employees');

Return this error:

SQL Server Database Error: Row tag omission (empty row tag name) cannot be used with attribute-centric FOR XML serialization.

See the SQLFiddle:

SQLFiddle

SELECT (SELECT e.EmployeeID AS "@EmpID" FOR XML PATH ('EmployeeID'), TYPE), c.FirstName, c.MiddleName, c.LastName ... FOR XML PATH ('Employee'), ROOT ('Employees'); is probably what you're after. — ZLK
– ZLK, Commented Aug 16, 2017 at 22:11
Hey @ZLK Thanks, but is not that... the result from your code was: <Employee><Employee EmpID="256"/><FirstName>Ross</FirstName><MiddleName></MiddleName><LastName>Geller</LastName></Employee> SQLFiddle but i need <Employee EmpID="256"><FirstName>Ross</FirstName><MiddleName></MiddleName><LastName>Geller</LastName></Employee> — andersoal
– andersoal, Commented Aug 16, 2017 at 22:19
Uh, then all you should need to do is change for xml path ('') to for xml path ('Employee') in your current query. — ZLK
– ZLK, Commented Aug 16, 2017 at 22:43

andersoal · Accepted Answer · 2017-08-25 12:04:47Z

3

The best way to do is using the right code:

SELECT 
e.EmployeeID AS "@EmpID",
c.FirstName, 
c.MiddleName, c.LastName
FROM Employee AS e 
INNER JOIN Contact AS c 
ON c.ContactID = e.ContactID
WHERE c.FirstName = 'Ross'
FOR XML PATH ('Employee')
;

Like the right SQLFiddle: http://www.sqlfiddle.com/#!6/3a159/8/0

answered Aug 25, 2017 at 12:04

andersoal

1751 gold badge3 silver badges21 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

SQL Server FOR XML PATH ROOT element with attribute (Path Mode symbol @ or / )

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related