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="<Root>China</Root>">
<OrderID>1</OrderID>
<OrderDate>2012-04-18T00:31:55.590</OrderDate>
</row>
<row Country="<Root>USA</Root>">
<OrderID>2</OrderID>
<OrderDate>2012-04-18T00:31:55.590</OrderDate>
</row>
No comments:
Post a Comment