Load XML souce file to a SP parameter

[viriyur@gmail.com]

I have a proc like this (T-SQL, SQL Server 2000):

Alter Proc ImportXML
@chvFullFileNam e varchar(200),
@txtInputXML text=''
AS

My question is how can I load the contents of the XMLFile into the
parameter called @txtInputXML ? Let us say my full file path is
C:\XML\SampleXM L.txt. I want to load this into parameter as one string

What do I use ? bulkcopy or something else ?

Your help would be much appreciated.

Thanks

[Erland Sommarskog]

Re: Load XML souce file to a SP parameter

(viriyur@gmail. com) writes:[color=blue]
> I have a proc like this (T-SQL, SQL Server 2000):
>
> Alter Proc ImportXML
> @chvFullFileNam e varchar(200),
> @txtInputXML text=''
> AS
>
> My question is how can I load the contents of the XMLFile into the
> parameter called @txtInputXML ? Let us say my full file path is
> C:\XML\SampleXM L.txt. I want to load this into parameter as one string
>
> What do I use ? bulkcopy or something else ?[/color]

From a client program read the file and then call the procedure.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

Books Online for SQL Server 2005 at

http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx

Books Online for SQL Server 2000 at

http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[viriyur@gmail.com]

Re: Load XML souce file to a SP parameter

Erland:

Thanks for the reply. Understand your point.
But do not want to use a client program or VB.
Is there a way I can load the file directly from SQL ?

Thanks again

[viriyur@gmail.com]

Re: Load XML souce file to a SP parameter

Erland:

Thanks for the reply. Understand your point.
But do not want to use a client program or VB.
Is there a way I can load the file directly from SQL ?

Thanks again

[Erland Sommarskog]

Re: Load XML souce file to a SP parameter

(viriyur@gmail. com) writes:[color=blue]
> Thanks for the reply. Understand your point.
> But do not want to use a client program or VB.
> Is there a way I can load the file directly from SQL ?[/color]

You could use BULK INSERT to load the file into a table. But then you will
not be able to get the XML document out of the table and into a variable,
because you cannot assign to text variables. (If the document is small
you can use nvarchar(4000) or varchar(8000).)

Depending on what you want to do, a better bet may be the XML bulk load
that comes with SQLXML 3.0, which is freely downloadable from Microsoft.
I have never used it myself, so I cannot assist with it.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

Books Online for SQL Server 2005 at

http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx

Books Online for SQL Server 2000 at

http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx