Jul 14, 2013

Column '' has invalid data type for attribute-centric XML serialization in FOR XML PATH sql server


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

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

INSERT INTO tblOrder(OrderID,Location)
VALUES(1,'<Root>China</Root>'),
(2,'<Root>USA</Root>')

If we will execute following sql queries :
SELECT
    Location "@Country",
    OrderID,
    OrderDate
FROM tblOrder FOR XML PATH

We will get error message:

Column '' has invalid data type for attribute-centric XML serialization in FOR XML PATH.

Cause:  XML data type cannot be used as attribute.  

Solution: We have to type case into varchar.

SELECT 
    CAST(Location AS VARCHAR) "@Country",
    OrderID,
    OrderDate
FROM tblOrder FOR XML PATH

Output:

<row Country="&lt;Root&gt;China&lt;/Root&gt;">
  <OrderID>1</OrderID>
  <OrderDate>2012-04-18T00:31:55.590</OrderDate>
</row>
<row Country="&lt;Root&gt;USA&lt;/Root&gt;">
  <OrderID>2</OrderID>
  <OrderDate>2012-04-18T00:31:55.590</OrderDate>
</row>

No comments:

Post a Comment