Column '': the last step in the path can't be applied to XML data type or CLR type 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 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 "text()",

    OrderID,

    OrderDate

FROM tblOrder FOR XML PATH

We will get error message:

Column '': the last step in the path can't be applied to XML data type or CLR type in FOR XML PATH.

Cause:  We cannot specify columns like

1. text()

2. comment()

3. node()

4. processing-instruction(name)

In the columns of data type XML or CLR

Solution:  To use them we have to type cast it.

SELECT

    CAST(Location AS VARCHAR(100)) "text()",

    OrderID,

    OrderDate

FROM tblOrder FOR XML PATH

Output:

<row>&lt;Root&gt;China&lt;/Root&gt;<OrderID>1</OrderID><OrderDate>2012-04-18T09:43:12.720</OrderDate></row>

<row>&lt;Root&gt;USA&lt;/Root&gt;<OrderID>2</OrderID><OrderDate>2012-04-18T09:43:12.720</OrderDate></row>