FOR XML AUTO cannot generate a URL address for binary data if a primary key is also binary

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

CREATE TABLE tblOrder(

    OrderID BINARY PRIMARY KEY,

    Location VARCHAR(50),

    Photo VARBINARY(MAX),

    OrderDate DATETIME DEFAULT(GETDATE())

)

INSERT INTO tblOrder(OrderID,Location,Photo) VALUES(0,'China',101),(1,'USA',011011),(10,'India',11111101),(11,'UK',0),(100,'India',110011)

If we will execute following sql query :

SELECT

    OrderID,

    Location,

    Photo

FROM tblOrder

FOR XML AUTO

We will get error message:

FOR XML AUTO cannot generate a URL address for binary data if a primary key is also binary.

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

Solution:

1.

SELECT

    OrderID,

    Location,

    Photo

FROM tblOrder

FOR XML AUTO, BINARY BASE64

Output:

<tblOrder OrderID="AA==" Location="China" Photo="AAAAZQ==" />

<tblOrder OrderID="AQ==" Location="USA" Photo="AAArAw==" />

<tblOrder OrderID="Cg==" Location="India" Photo="AKmKvQ==" />

<tblOrder OrderID="Cw==" Location="UK" Photo="AAAAAA==" />

<tblOrder OrderID="ZA==" Location="India" Photo="AAGtuw==" />

2. Changing the data type of primary key column to  int:

CREATE TABLE tblOrder(

    OrderID INT PRIMARY KEY,

    Location VARCHAR(50),

    Photo VARBINARY(MAX),

    OrderDate DATETIME DEFAULT(GETDATE())

)

SELECT

    OrderID,

    Location,

    Photo

FROM tblOrder

FOR XML AUTO