XML into byte ??

[XGaMeS]

I want to store xml file in a ntext datatype in sql server 2000, someone have any idea how to do this ? By the way it's an XMLDocument who need to be stored and they are no more than 3-4k. I have no idea on how to do that.. please help me !:eek: I am a somehow a newbie

[Chris Quick]

You could convert the document into a string and then store it in the database. May not be the most compact way of storing it, however.

[Christian Graus]

XGaMeS wrote: I want to store xml file in a ntext datatype in sql server 2000 Erm... what is XML, if it's not text ? Christian I have drunk the cool-aid and found it wan and bitter. - Chris Maunder

[XGaMeS]

And any clues on how to conevert it and pass it as a parameters ? I am...

[Chris Quick]

Okay, here's a senario that may help. Create your stored procedure to handle actually commiting the data to the database

CREATE PROCEDURE InsertXMLData
{
@XMLData ntext
}

AS
INSERT INTO MyDemoTable (XMLData) VALUES (@XMLData);
GO

To use this procedure do the following:

Private Sub InsertXML(ByVal XMLData as XMLDocument)
     ' create a sqlconnection 
     Dim SQLCon as New SQLConnection("server=...")
     
     ' create a sql command
     Dim SQLCon as New SQLCommand("InsertXMLData", SQLCon)
     
     ' Set the command type and add parameters
     SQLCon.CommandType = CommandType.StoredProcedure
     SQLCon.Parameters.Add("@XMLData", XMLData.OuterXML.ToString)
       
     ' Variable to store records added.
     Dim RecordsAdded as Integer      
       
     ' Execute a non-query
     Try
        SQLCon.Open
        RecordsAdded = SQLCmd.ExecuteNonQuery
     Catch sqlex as SQLException
        RecordsAdded = -1
     Catch ex as Exception
        RecordsAdded = -1
     Finally
        SQLCon.Close
     End Try
End Sub

You will want to make sure to research more about the System.Data.SQLClient namespace for more information on using parameters with SQL Commands. Hope this is useful!

[Dave Kreskowiak]

There's nothing to convert. You just open the file and read it into a string.

Dim sr As StreamReader = New StreamReader("C:\\myPath\\TestFile.xml")
Dim content As String
content = sr.ReadToEnd()
sr.Close()

Then you just pass the string to your SQLCommand as you would any other SQLParameter. RageInTheMachine9532 "...a pungent, ghastly, stinky piece of cheese!" -- The Roaming Gnome

[Christian Graus]

*sigh* Thank you. Christian I have drunk the cool-aid and found it wan and bitter. - Chris Maunder