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
No comments:
Post a Comment