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><Root>China</Root><OrderID>1</OrderID><OrderDate>2012-04-18T09:43:12.720</OrderDate></row>
<row><Root>USA</Root><OrderID>2</OrderID><OrderDate>2012-04-18T09:43:12.720</OrderDate></row>
No comments:
Post a Comment