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>

*/

Rating: 1.0/10 (1 vote cast)

Rating: 0 (from 0 votes)

Order of Attribute-Centric Columns in FOR XML PATH matters, 1.0 out of 10 based on 1 rating

Be Sociable, Share!

Filed under: Issues/Troubleshooting, SQLXML

belle’s sql musings