Order of Attribute-Centric Columns in FOR XML PATH matters

Error:

Attribute-centric column ‘@Title’ must not come after a  non-attribute-centric sibling in XML hierarchy in FOR XML PATH.

Reason:

You are having this issue if you have attribute- and element-centric columns in your FOR XML PATH query that are interleaved, as such:

SELECT
    EmployeeID       AS '@EmployeeID',            -- attribute
    FirstName        AS 'Details/FirstName',      -- nested element
    LastName         AS 'Details/LastName',       -- nested element
    Title            AS '@Title'                  -- attribute
FROM
    dbo.Employee
FOR XML PATH('Employee')

Resolution:

Order of attribute-centric columns in your FOR XML PATH query matters … if you have nested elements.

To resolve the error above, you must specify all attributes first, then specify the nested elements

SELECT
    EmployeeID       AS '@EmployeeID',            -- attribute
    Title            AS '@Title',                 -- attribute
    FirstName        AS 'Details/FirstName',      -- nested element
    LastName         AS 'Details/LastName'        -- nested element
FROM
    dbo.Employee
FOR XML PATH('Employee')
/*
Sample Output:

<Employee EmployeeID="1" Title="Production Technician - WC60">
  <Details>
    <FirstName>Guy</FirstName>
    <LastName>Gilbert</LastName>
  </Details>
</Employee>
*/
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s