new to xml do I need to install iis server

Question

Post reply

new to xml do I need to install iis server

marketwiz88

SSC-Addicted

Points: 406
More actions
August 2, 2005 at 4:06 pm

#165566

Hi,
I have a need to insert all records in a xml document into a sql server temp table. I would like to get some help on opening a xml document on a workstation so that it can be load into the database table. I saw some example on BOL about the openxml, but nerver understand where it read the actual document or where the folder of the document is specify. I do not have IIS install on my SQL Server 2000. Do I need to install this? Any help would really appreciate it. All I want is for our applicaiton to only a xml file from any workstation and read into a table so I can validate and move the data around.

Thanks
Louis Lam

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

bledu SSChampion Points: 12335 More actions · Answer 1

You do not need to install iis for this.

you can just pass your xml file as a parameter of type ntext and then query it.

using the example in BOL on "Use a simple SELECT statement with OPENXML"

****************************************************

create a stored procedure

create proc sp_getCustomerData

@doc ntext

as

DECLARE @idoc int

BEGIN

--Create an internal representation of the XML document.

EXEC sp_xml_preparedocument @idoc OUTPUT, @doc

-- Execute a SELECT statement that uses the OPENXML rowset provider.

INSERT INTO Contacts

(ContID,ContName)

SELECT CustomerID,ContactName

FROM OPENXML (@idoc, '/ROOT/Customer',1)

WITH (CustomerID varchar(10),

ContactName varchar(20))

EXEC sp_xml_removedocument @idoc

END

***************************************************

something like this should suffice, and then call your stored procedure from your app and pass the xml file

Everything you can imagine is real.

marketwiz88 SSC-Addicted Points: 406 More actions · Answer 2

Thanks for you advice. But is there a way to read the whole xml file without copying all the content to a store procedure like you describe? Our develper doesn't want to do this way because there might be while card character in the xml content that could potentially cause problem.

For example, I want to be able to do something like this.

execute sp_getCustomerData 'c:\contacts.xml'

and let SQL Server read the contacts file and then I will do the insert to the correct table.

Thanks

Louis Lam