In SQL 2000, most of the XML documentation involves using IIS to pass the data in, or Visual Basic. However, you can also simply pass the XML code itself as an ntext parameter, and just load it in as though it were a recordset. See the below example on the Northwind database.
Terms of Agreement:
By using this article, you agree to the following terms...
You may use
this article in your own programs (and may compile it into a program and distribute it in compiled format for languages that allow it) freely and with no charge.
You MAY NOT redistribute this article (for example to a web site) without written permission from the original author. Failure to do so is a violation of copyright laws.
You may link to this article from another website, but ONLY if it is not wrapped in a frame.
You will abide by any additional copyright restrictions which the author may have placed in the article or article's description.
create procedure dbo.up_Insert_Company(@xmldata ntext)
as
begin
declare @FileHandle int -- Filehandle to the xml stuff
/* convert the xml data into a recordset */
exec dbo.sp_xml_preparedocument @FileHandle output, @xmldata
insert into dbo.categories
select * from openxml(@FileHandle, '/record')
with dbo.categories
end
I was getting a null value error. Adding CategoryID to CategoryName and Description did the trick... (If this comment was disrespectful, please report it.)
In this case we are passing the entire XML string as a parameter to the stored procedure . But can we in any way pass the reference to the XML file (say its path )? (If this comment was disrespectful, please report it.)
2/4/2005 8:41:59 PM:
This is great! XML as a parameter to a stored procedure! What does it cost in cpu usage? (If this comment was disrespectful, please report it.)
Great Idea, would aded: exec sp_xml_removedocument @FileHandle --to the end of the procedure to release the dom (If this comment was disrespectful, please report it.)
hi Mr. Brandon, Thxs for sharing this to us. Right now, I wanted to make a simple apps, that extract Data from a SQL Server, then export that Data as XML, then load that data in another machine (PC - still using a SQL Server). Can you help me with this? by making a sample application and submit it here in PSC? Thanks you so much (If this comment was disrespectful, please report it.)
Add Your Feedback
Your feedback will be posted below and an email sent to
the author. Please remember that the author was kind enough to
share this with you, so any criticisms must be stated politely, or they
will be deleted. (For feedback not related to this particular article, please
click here instead.)
By 7 Users
5/30/2001 12:28:54 PM: 
