Feb 12, 2013

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>

No comments:

Post a Comment