Oggi sul newsgroup microsoft.public.it.sql è apparsa una domanda di Daniele che chiedeva come importare in SQL Server 2005 una immagine memorizzata in formato Binary Base64 in un file XML.

Un paio di mesi fa scrissi l'articolo Leggere un file XML con SQL Server 2005 dove illustravo le grosse novità introdotte con SQL Server 2005 nella gestione dei dati in formato XML.
In quella occasione però non parlai dei dati LOB ma visto che l'argomento è affine ho provato a buttare giù un piccolo esempio:

USE tempdb;
GO

CREATE PROCEDURE dbo.up_ExportXML(
@ProductPhotoID int
)
AS
SELECT ProductPhotoID "@ProductPhotoID", ThumbNailPhoto "@ThumbNailPhoto"
FROM AdventureWorks.Production.ProductPhoto
WHERE ProductPhotoID = @ProductPhotoID
FOR XML PATH ('Product'), ROOT('Products'), BINARY BASE64;
GO

/* Esporto la foto del prodotto con ID 1 su file */
!!bcp "EXEC tempdb.dbo.up_ExportXML 1" queryout C:\Product.xml -T -c

/* Dichiaro la tabella dbo.Products */
CREATE TABLE dbo.Products(
ProductPhotoID int NOT NULL PRIMARY KEY,
ThumbNailPhoto varbinary(max)
);
GO

DECLARE @xmlDoc xml;

/* Carico l'intero file XML in una variabile XML */
SET @xmlDoc = (
      SELECT Q.BulkColumn
      FROM OPENROWSET(BULK 'C:\Product.xml', SINGLE_CLOB) AS Q
);

/* Valorizzo la tabella dbo.Products utilizzando i metodi nodes() e value() */
INSERT dbo.Products
SELECT
        myColumn.value('@ProductPhotoID', 'int')
      , myColumn.value('@ThumbNailPhoto', 'varbinary(max)')
FROM @xmlDoc.nodes('/Products/Product') AS myTable(myColumn);
GO

/* Vediamo */
SELECT *
FROM dbo.Products;
GO

/* Output:

ProductPhotoID ThumbNailPhoto
-------------- -----------------------------
1              0x47494638396150003100F70...

(1 row(s) affected)

*/

/* Pulizia */
DROP PROCEDURE dbo.up_ExportXML;
DROP TABLE dbo.Products;
!!del C:\Product.xml

Come potete vedere i nuovi metodi nodes() e value() del data type xml ci permettono di importare agevolmente anche dati binari memorizzati in file XML.