MS SQL XML - openxml - C# example please!

Author

openxml - C# example please!

2005-04-16, 3:23 am

I have created a stored procedure to use OPENXML to update my SQL2000
database using an external XML file.

What I need help on is:
How to pass the XML file as a parameter to the stored procedure.
Do I call SqlParameter ? If yes, besides the file name, it wants to know
the dbType and size.

Can someone post the snipped of code to do that ?

Thanks in advance,
LW

Michael Rys [MSFT]

2005-04-22, 3:24 am

You need to read the data from the file on the client and pass the content
as type NTEXT (assuming the data in the file is UTF-16 encoded, otherwise
you need to convert it first) to the stored proc via SqlParameter.

Best regards
Michael

"LW" <LW@discussions.microsoft.com> wrote in message
news:010A23E5-1301-4A3D-A998- 59D4709F0A74@microso
ft.com...
>I have created a stored procedure to use OPENXML to update my SQL2000
> database using an external XML file.
>
> What I need help on is:
> How to pass the XML file as a parameter to the stored procedure.
> Do I call SqlParameter ? If yes, besides the file name, it wants to know
> the dbType and size.
>
> Can someone post the snipped of code to do that ?
>
> Thanks in advance,
> LW

Vasant Raj

2005-05-04, 12:07 am

It's simple.
Just create a stored procedure which performs your XML manipulation and pass the XML to it.(XML string)

The code for that is given below:
//Name of stored procedure
this.objCon.CommandString = "InsertRecords";

//specifying the command type as stored procedure
this.objCon.SQLComm.CommandType = CommandType.StoredProcedure;

//Add paramater which is of Varchar.(use TEXT if file is large)
this.objCon.SQLComm.Parameters.Add("@strXML", SqlDbType.VarChar, 8000).Value = XmlString;

//execute the querry
this.objCon.SQLComm.ExecuteNonQuery();

//close the connection
this.objCon.Close();

Sponsored Links