insert binary data from xml

Hi, i have to "bulk" insert in my table some binary values (files) reading
from xml text..
i use this way:

create table test(MyKey varchar(32), FileData varbinary(MAX));
declare @xml xml;
set @xml =
'<?xml version="1.0" encoding="utf-8" ?>
<test>
<Row MyKey="1" FileData="BinaryDataInBase64FormatString" />
<Row MyKey="2" FileData="BinaryDataInBase64FormatString" />
<Row MyKey="3" FileData="BinaryDataInBase64FormatString" />
<Row MyKey="4" FileData="BinaryDataInBase64FormatString" />
<Row MyKey="5" FileData="BinaryDataInBase64FormatString" />
<Row MyKey="6" FileData="BinaryDataInBase64FormatString" />
<Row MyKey="7" FileData="BinaryDataInBase64FormatString" />
<Row MyKey="8" FileData="BinaryDataInBase64FormatString" />
<Row MyKey="9" FileData="BinaryDataInBase64FormatString" />
<Row MyKey="10" FileData="BinaryDataInBase64FormatString" />
</test>';

INSERT INTO test(MyKey,FileData)
SELECT
Row.value('@MyKey', 'varchar(32)') as MyKey,
Row.value('@FileData', 'varbinary(MAX)') as FileData
FROM @xml.nodes('/test/Row') AS myTable(Row);

this method work fine but in this way i load in memory all files data
(converted in base64).
I would like to use the file paths instead like:

declare @xml xml;
set @xml =
'<?xml version="1.0" encoding="utf-8" ?>
<test>
<Row MyKey="1" FileData="c:\File1.jpg" />
<Row MyKey="2" FileData="c:\File2.jpg" />
<Row MyKey="3" FileData="c:\File3.jpg" />
</test>';
INSERT INTO test(MyKey,FileData)
SELECT
Row.value('@MyKey', 'varchar(32)') as MyKey,
ConvertThisPathToVarBinary(Row.value('@FileData', 'varbinary(MAX)'))
as FileData
FROM @xml.nodes('/test/Row') AS myTable(Row);

there is a way?
Thanks

How about this?

--INSERT INTO test(MyKey,FileData)
SELECT
Row.value('@MyKey', 'varchar(32)') as MyKey
cast(Row.value('@FileData', 'varchar(MAX)') as varbinary(max))
as FileData
FROM @xml.nodes('/test/Row') AS myTable(Row);



"Gambero" wrote in message

> Hi, i have to "bulk" insert in my table some binary values (files) reading
> from xml text..
> i use this way:
>
> create table test(MyKey varchar(32), FileData varbinary(MAX));
> declare @xml xml;
> set @xml =
> '<?xml version="1.0" encoding="utf-8" ?>
> <test>
> <Row MyKey="1" FileData="BinaryDataInBase64FormatString" />
> <Row MyKey="2" FileData="BinaryDataInBase64FormatString" />
> <Row MyKey="3" FileData="BinaryDataInBase64FormatString" />
> <Row MyKey="4" FileData="BinaryDataInBase64FormatString" />
> <Row MyKey="5" FileData="BinaryDataInBase64FormatString" />
> <Row MyKey="6" FileData="BinaryDataInBase64FormatString" />
> <Row MyKey="7" FileData="BinaryDataInBase64FormatString" />
> <Row MyKey="8" FileData="BinaryDataInBase64FormatString" />
> <Row MyKey="9" FileData="BinaryDataInBase64FormatString" />
> <Row MyKey="10" FileData="BinaryDataInBase64FormatString" />
> </test>';
>
> INSERT INTO test(MyKey,FileData)
> SELECT
> Row.value('@MyKey', 'varchar(32)') as MyKey,
> Row.value('@FileData', 'varbinary(MAX)') as FileData
> FROM @xml.nodes('/test/Row') AS myTable(Row);
>
> this method work fine but in this way i load in memory all files data
> (converted in base64).
> I would like to use the file paths instead like:
>
> declare @xml xml;
> set @xml =
> '<?xml version="1.0" encoding="utf-8" ?>
> <test>
> <Row MyKey="1" FileData="c:\File1.jpg" />
> <Row MyKey="2" FileData="c:\File2.jpg" />
> <Row MyKey="3" FileData="c:\File3.jpg" />
> </test>';
> INSERT INTO test(MyKey,FileData)
> SELECT
> Row.value('@MyKey', 'varchar(32)') as MyKey,
> ConvertThisPathToVarBinary(Row.value('@FileData',
> 'varbinary(MAX)')) as FileData
> FROM @xml.nodes('/test/Row') AS myTable(Row);
>
> there is a way?
> Thanks
>