Extracting image column to the file

Hello all,
how can I extract image strored in DB in to file?
Will something like this work?
DECLARE @.cmd VARCHAR(2048)
SET @.cmd = 'bcp select history_image from recipient where recipient_id = 1
queryout c:\test\625954.jpg '
EXEC master..xp_cmdshell @.cmd, NO_OUTPUTYou want to use TextCopy.
e.g.
--OJ: TEXTCOPY example
-- Loading txt files into db
--create tb to hold data
create table tmp(fname varchar(100),txt text default '')
go
declare @.sql varchar(255),
@.fname varchar(100),
@.path varchar(50)
--specify desired folder
set @.path='c:\test'
set @.sql='dir ' + @.path + '*.txt /c /b'
--insert filenames into tb
insert tmp(fname)
exec master..xp_cmdshell @.sql
--loop through and insert file contents into tb
declare cc cursor
for select fname from tmp
open cc
fetch next from cc into @.fname
while @.@.fetch_status=0
begin
set @.sql='textcopy /s"dev" /d"tempdb" /t"tmp" /c"txt" /w"where
fname=''' + @.fname + '''"'
set @.sql=@.sql + ' /f"' + @.path + @.fname + '" /i'
print @.sql
exec master..xp_cmdshell @.sql,no_output
fetch next from cc into @.fname
end
close cc
deallocate cc
go
select * from tmp
go
drop table tmp
go
-oj
"Gene" <Gene@.discussions.microsoft.com> wrote in message
news:DDDCCF4D-23B1-4348-9740-47A7738C9E36@.microsoft.com...
> Hello all,
> how can I extract image strored in DB in to file?
> Will something like this work?
> DECLARE @.cmd VARCHAR(2048)
> SET @.cmd = 'bcp select history_image from recipient where recipient_id = 1
> queryout c:\test\625954.jpg '
> EXEC master..xp_cmdshell @.cmd, NO_OUTPUT

Extract values from XML in a Strored Proc

I pass an xml to a Stored Proc similar to below.
How do I extaract the id and value from below XML?
DECLARE @.piMnemonicIDs xml
SET @.piMnemonicIDs =
'<MnemonicIDs><id>1</id><value>Costs</value><id>2</id><value>MarketPowerInfluence</value><value>IndustryStanding</value><value>SupplierRelations</value></MnemonicIDs>'
SELECT T.MnemonicIDs.value('/value', 'VARCHAR(50)'),
T.MnemonicIDs.value('/id', 'VARCHAR(50)')
FROM @.piMnemonicIDs.nodes('/MnemonicIDs') AS T(MnemonicIDs)
You probably want:
DECLARE @.piMnemonicIDs xml
SET @.piMnemonicIDs =
'<MnemonicIDs>
<id>1</id><value>Costs</value>
<id>2</id><value>MarketPowerInfluence</value><value>IndustryStanding</value>
<value>SupplierRelations</value>
</MnemonicIDs>'
SELECT T.MnemonicIDs.value('.', 'VARCHAR(50)'),
T.MnemonicIDs.value('(for $v in . return ../id[. << $v])[last()]',
'VARCHAR(50)')
FROM @.piMnemonicIDs.nodes('/MnemonicIDs/value') AS T(MnemonicIDs)
However, if you can change the XML structure to something like:
DECLARE @.piMnemonicIDs xml
SET @.piMnemonicIDs =
'<MnemonicIDs>
<id>1</id><value>Costs</value>
</MnemonicIDs>
<MnemonicIDs>
<id>2</id><value>MarketPowerInfluence</value>
</MnemonicIDs>
<MnemonicIDs>
<id>2</id><value>IndustryStanding</value>
</MnemonicIDs>
<MnemonicIDs>
<id>2</id><value>SupplierRelations</value>
</MnemonicIDs>'
then the query becomes simpler:
SELECT T.MnemonicIDs.value('value[1]', 'VARCHAR(50)'),
T.MnemonicIDs.value('id[1]', 'VARCHAR(50)')
FROM @.piMnemonicIDs.nodes('/MnemonicIDs') AS T(MnemonicIDs)
Best regards
Michael
"C" <C@.discussions.microsoft.com> wrote in message
news:BCFF9712-A442-42E9-9F7F-E686F65137D0@.microsoft.com...
>I pass an xml to a Stored Proc similar to below.
> How do I extaract the id and value from below XML?
>
> DECLARE @.piMnemonicIDs xml
> SET @.piMnemonicIDs =
> '<MnemonicIDs><id>1</id><value>Costs</value><id>2</id><value>MarketPowerInfluence</value><value>IndustryStanding</value><value>SupplierRelations</value></MnemonicIDs>'
> SELECT T.MnemonicIDs.value('/value', 'VARCHAR(50)'),
> T.MnemonicIDs.value('/id', 'VARCHAR(50)')
> FROM @.piMnemonicIDs.nodes('/MnemonicIDs') AS T(MnemonicIDs)
>

Extract values from XML in a Strored Proc

I pass an xml to a Stored Proc similar to below.
How do I extaract the id and value from below XML?
DECLARE @.piMnemonicIDs xml
SET @.piMnemonicIDs =
'<MnemonicIDs><id>1</id><value>Costs</value><id>2</id><value>MarketPowerInfl
uence</value><value>IndustryStanding</value><value>SupplierRelations</value>
</MnemonicIDs>'
SELECT T.MnemonicIDs.value('/value', 'VARCHAR(50)'),
T.MnemonicIDs.value('/id', 'VARCHAR(50)')
FROM @.piMnemonicIDs.nodes('/MnemonicIDs') AS T(MnemonicIDs)You probably want:
DECLARE @.piMnemonicIDs xml
SET @.piMnemonicIDs =
'<MnemonicIDs>
<id>1</id><value>Costs</value>
<id>2</id><value>MarketPowerInfluence</value><value>IndustryStanding</value>
<value>SupplierRelations</value>
</MnemonicIDs>'
SELECT T.MnemonicIDs.value('.', 'VARCHAR(50)'),
T.MnemonicIDs.value('(for $v in . return ../id[. << $v])[last()]',
'VARCHAR(50)')
FROM @.piMnemonicIDs.nodes('/MnemonicIDs/value') AS T(MnemonicIDs)
However, if you can change the XML structure to something like:
DECLARE @.piMnemonicIDs xml
SET @.piMnemonicIDs =
'<MnemonicIDs>
<id>1</id><value>Costs</value>
</MnemonicIDs>
<MnemonicIDs>
<id>2</id><value>MarketPowerInfluence</value>
</MnemonicIDs>
<MnemonicIDs>
<id>2</id><value>IndustryStanding</value>
</MnemonicIDs>
<MnemonicIDs>
<id>2</id><value>SupplierRelations</value>
</MnemonicIDs>'
then the query becomes simpler:
SELECT T.MnemonicIDs.value('value[1]', 'VARCHAR(50)'),
T.MnemonicIDs.value('id[1]', 'VARCHAR(50)')
FROM @.piMnemonicIDs.nodes('/MnemonicIDs') AS T(MnemonicIDs)
Best regards
Michael
"C" <C@.discussions.microsoft.com> wrote in message
news:BCFF9712-A442-42E9-9F7F-E686F65137D0@.microsoft.com...
>I pass an xml to a Stored Proc similar to below.
> How do I extaract the id and value from below XML?
>
> DECLARE @.piMnemonicIDs xml
> SET @.piMnemonicIDs =
> '<MnemonicIDs><id>1</id><value>Costs</value><id>2</id><value>MarketPowerIn
fluence</value><value>IndustryStanding</value><value>SupplierRelations</valu
e></MnemonicIDs>'
> SELECT T.MnemonicIDs.value('/value', 'VARCHAR(50)'),
> T.MnemonicIDs.value('/id', 'VARCHAR(50)')
> FROM @.piMnemonicIDs.nodes('/MnemonicIDs') AS T(MnemonicIDs)
>