p2p.wrox.com Forums

Need to download code?

View our list of code downloads.


  Return to Index  

sql_server_xml thread: Re: saving binary data in xml using stored procedure


Message #1 by meiharwong@h... on Mon, 1 Apr 2002 06:02:36
> Check out the following article and see if it helps:

Importing XML into SQL Server 2000 (check the Q&A section)
http://www.PerfectXML.com/Articles/xml/importxmlsql.asp#binary

Best wishes,
Darshan Singh
http://www.PerfectXML.com


>From: meiharwong@h...
>Reply-To: "SQL Server XML" <sql_server_xml@p...>
>To: "SQL Server XML" <sql_server_xml@p...>
>Subject: [sql_server_xml] saving binary data in xml using stored procedure
>Date: Fri, 29 Mar 2002 07:48:49
>
>Hi,
>
>I have an xml file which contains some binary data of picture. The problem
>is that i do not know how to write the stored procedure to receive this
>xml file in order to save it to the database. Anyone who knows how to
>write the stored procedure, please help me.. thanx u.
>
>Michelle
>---
>Change your mail options at http://p2p.wrox.com/manager.asp or
>to unsubscribe send a blank email to 
>

Hi,

Thanks Darshan. I really learn a lot of ways to insert binary data into 
the database from the web site you gave me. But i still have some problem.

I have an xml file that contains a few elements that hold text and a 
element that hold binary data( a image file ). Now the problem is that can 
we pass texts and binary data along to the stored procedure. It seems that 
the data couldn't save to the database. Help me please. Thank you

''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
This is the stored procedure
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

CREATE PROCEDURE InsertRecord
	@sXML VARCHAR(8000)  <--------What data type should i use here
AS

DECLARE @iDoc INT

EXEC sp_xml_preparedocument @iDoc OUTPUT, @sXML

INSERT INTO EPF 
SELECT * FROM OPENXML(@iDOC, '/JPN', 3)
WITH  ( 
	ICNumber  char(15) 'ICNumber',
 	[Name] char(30) 'Name',
	DateOfBirth datetime 'DateOfBirth',
	Nationality char(30) 'Nationality',
        Photo image 'Photo'
      )
EXEC sp_xml_removedocument @iDoc
GO

''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
The xml file
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

<JPN>
<ICNumber>123</ICNumber>
<Name>Mathew2002</Name>
<DateOfBirth>6/6/2000</DateOfBirth>
<Nationality>England</Nationality>
<Photo xmlns:dt="urn:schemas-microsoft-com:datatypes" dt:dt="bin.base64">--
-BINARY DATA-----</Photo>
</JPN>

'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Or do i have to drop off or extract the photo portion(element) and save 
the binary data(image) by using other methods.

Michelle
_________________________________________________________________
MSN Photos is the easiest way to share and print your photos: 
http://photos.msn.com/support/worldwide.aspx


  Return to Index