Aug 15, 2013

Attribute-centric column '' must not come after a non-attribute-centric sibling in XML hierarchy in FOR XML PATH.


I am creating a tblOrder table in sql server and inserting few records into it:

CREATE TABLE tblOrder(
    OrderID INT PRIMARY KEY,
    Location VARCHAR(20),
    OrderDate DATETIME DEFAULT(GETDATE())
)

INSERT INTO tblOrder(OrderID,Location) 
VALUES(1,'China'),(2,'USA')

If we will execute following sql queries :

SELECT
    Location,
    OrderID "@Country",
    OrderDate
FROM tblOrder FOR XML PATH

We will get error message:

Attribute-centric column '' must not come after a non-attribute-centric sibling in XML hierarchy in FOR XML PATH.

Cause:  In SELECT Clause OrderId column must come first since it is attribute centric.  

Solution: correct order will be:

SELECT
    OrderID "@Country",
    Location,
    OrderDate
FROM tblOrder FOR XML PATH

Output:

<row Country="1">
  <Location>China</Location>
  <OrderDate>2012-04-18T09:09:37.680</OrderDate>
</row>
<row Country="2">
  <Location>USA</Location>
  <OrderDate>2012-04-18T09:09:37.680</OrderDate>
</row>

No comments:

Post a Comment