Apr 18, 2013

FOR XML AUTO could not find the table owning the following column '' to create a URL address for it. Remove the column, or use the BINARY BASE64 mode, or create the URL directly using the 'dbobject/TABLE[@PK1="V1"]/@COLUMN' syntax.


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),
    Photo VARBINARY(MAX),
    OrderDate DATETIME DEFAULT(GETDATE())
)

INSERT INTO tblOrder(Location,Photo) VALUES('China',101),('USA',011011),('India',11111101),('UK',0),('India',110011)

If we will execute following sql query :

SELECT
    Location,
    CAST(Photo AS IMAGE) AS Photo
FROM tblOrder
FOR XML AUTO

We will get error message:

FOR XML AUTO could not find the table owning the following column '' to create a URL address for it. Remove the column, or use the BINARY BASE64 mode, or create the URL directly using the 'dbobject/TABLE[@PK1="V1"]/@COLUMN' syntax.

Cause:  To create an URL address of BLOB columns like IMAGE, VARBINARY we must have to select primary key column and we cannot type cast the column. Alternatively we can convert into BINARY BASE64 mode.

Solution:  Correct syntax is:

1.

SELECT
    OrderID,
    Location,
    Photo AS Photo
FROM tblOrder
FOR XML AUTO

Output:

<tblOrder OrderID="1" Location="China" Photo="dbobject/tblOrder[@OrderID='1']/@Photo" />
<tblOrder OrderID="2" Location="USA" Photo="dbobject/tblOrder[@OrderID='2']/@Photo" />
<tblOrder OrderID="3" Location="India" Photo="dbobject/tblOrder[@OrderID='3']/@Photo" />
<tblOrder OrderID="4" Location="UK" Photo="dbobject/tblOrder[@OrderID='4']/@Photo" />
<tblOrder OrderID="5" Location="India" Photo="dbobject/tblOrder[@OrderID='5']/@Photo" />

2.

SELECT
    OrderID,
    Location,
    CAST(Photo AS IMAGE) AS Photo
FROM tblOrder
FOR XML AUTO, BINARY BASE64

Output:

<tblOrder Location="China" Photo="AAAAZQ==" />
<tblOrder Location="USA" Photo="AAArAw==" />
<tblOrder Location="India" Photo="AKmKvQ==" />
<tblOrder Location="UK" Photo="AAAAAA==" />
<tblOrder Location="India" Photo="AAGtuw==" />

No comments:

Post a Comment