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