Dec 15, 2013

FOR XML PATH error in column '' - '//' and leading and trailing '/' are not allowed in simple path expressions sql server


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

CREATE TABLE tblOrder(
    OrderID INT IDENTITY PRIMARY KEY,
    Location VARCHAR(50),
    OrderDate DATETIME DEFAULT(GETDATE())
)

INSERT INTO tblOrder(Location) VALUES('China'),('USA'),('India'),('UK'),('India')

If we will execute following sql queries :

1.

SELECT
    OrderID "@Id",
    Location "/Country/Location",
    OrderDate
FROM tblOrder FOR XML PATH

2.


SELECT
    OrderID "@Id",
    Location "//Country/Location",
    OrderDate
FROM tblOrder FOR XML PATH

3.

SELECT
    OrderID "@Id",
    Location "Country/Location/",
    OrderDate
FROM tblOrder FOR XML PATH
We will get error message:

FOR XML PATH error in column '' - '//' and leading and trailing '/' are not allowed in simple path expressions.

Cause:  It is due in incorrect XML path in column Location.

Solution: Correct XML path can be:

SELECT
    OrderID "@Id",
    Location "Country/Location",
    OrderDate
FROM tblOrder FOR XML PATH

Output:


<row Id="AA==">
  <Country>
    <Location>China</Location>
  </Country>
  <OrderDate>2012-04-18T00:09:56.953</OrderDate>
</row>
<row Id="AQ==">
  <Country>
    <Location>USA</Location>
  </Country>
  <OrderDate>2012-04-18T00:09:56.953</OrderDate>
</row>
<row Id="Cg==">
  <Country>
    <Location>India</Location>
  </Country>
  <OrderDate>2012-04-18T00:09:56.953</OrderDate>
</row>
<row Id="Cw==">
  <Country>
    <Location>UK</Location>
  </Country>
  <OrderDate>2012-04-18T00:09:56.953</OrderDate>
</row>
<row Id="ZA==">
  <Country>
    <Location>India</Location>
  </Country>
  <OrderDate>2012-04-18T00:09:56.953</OrderDate>
</row>

No comments:

Post a Comment