FOR XML AUTO requires primary keys to create references for ''. Select primary keys, or use BINARY BASE64 to obtain binary data in encoded form if no primary keys exist.

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,

    Photo

FROM tblOrder

FOR XML AUTO

We will get error message:

FOR XML AUTO requires primary keys to create references for ''. Select primary keys, or use BINARY BASE64 to obtain binary data in encoded form if no primary keys exist.

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

Solution:

1.

SELECT

    OrderID,

    Location,

    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,

    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==" />