Apr 18, 2013

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

No comments:

Post a Comment