insert binary data from xml

Tech-Archive recommends: Fix windows errors by optimizing your registry

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

.

Relevant Pages

  • Re: insert binary data from xml
    ... Row.value('@MyKey', 'varchar(32)') as MyKey ... from xml text.. ... create table test, FileData varbinary); ... declare @xml xml; ...
    (microsoft.public.sqlserver.xml)
  • can openxml write multiple fields - 1 row?
    ... quantity attributes from the XML document. ... declare @doc varchar ... FROM OPENXML ... This routine only generates one int ...
    (microsoft.public.sqlserver.xml)
  • Re: XML performance extremely slow for no obvious reason
    ... But XML is also an issue, because there, too, ... the optimizer can't deal, but you can do less about it, I think. ... Though there's a nested loop join shown, ... declare @ids varbinary ...
    (microsoft.public.sqlserver.xml)
  • can openxml write multiple fields - 1 row?
    ... I got an xml routine from the Sql Server NG where I can ... delimiters in the string. ... Declare @iDoc Int ...
    (microsoft.public.sqlserver.xml)
  • Re: Shredding XML
    ... to determine which field values should be extracted from an XML doc ... The stored procedure works, but the process of dynamically gathering ... DECLARE keycolumns_cursor CURSOR LOCAL ... DECLARE @xPath varchar ...
    (microsoft.public.sqlserver.xml)