Using .NET to write to SQL2005 XML fields

Question

Using .NET to write to SQL2005 XML fields

David.Poole

SSC Guru

Points: 76001
More actions
May 30, 2006 at 8:01 am

#67441

What is the best way of writing XML into a SQL2005 XML field from a .NET app.
At present I am simply passing a string via Enterprise Library 2006 and under the hood it seems to work.
When I try and write an XML document larger than 8000 characters in the SQL Management Studio it truncates (as did the old SQL Query Analyser). I don't know if this is a feature of the management studio or a problem I will encounter in the real world.
I would appreciate any advice on this. matter
LinkedIn Profile

Viewing 5 posts - 1 through 4 (of 4 total)

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

Bill Scott SSCrazy Points: 2338 More actions · Answer 1

Sounds like you're running into the varchar size limit. Try writing the xml to a text blob and have the .net app fetch it from there.

David.Poole SSC Guru Points: 76001 More actions · Answer 2

Thanks but in the proc I my paramter is an XML type writing to a field of an XML type.

When I am declaring the SqlParameter in .NET what type should I use?

LinkedIn Profile

Shaun Stewart SSC Eights! Points: 890 More actions · Answer 3

Have you considered the varchar(MAX) datatype. This is certainly capable of storing more than 8000 characters

regards

Shaun

Quis custodiet ipsos custodes.

Eddie Wuerch SSChampion Points: 12535 More actions · Answer 4

Have you tried loading up a SqlTypes.SqlXml variable with your document and passing that as a parameter to a stored procedure?

The procedure:

CREATE PROC dbo.MySampleXMLProc(@XMLToInsert xml)  INSERT dbo.MyTable(MyXMLColumn)  VALUES @XMLToInsertRETURNGO

Note: I haven't tried pushing this past 8000 characters, I'd like to hear of this works for you.

'VB.Net 2005

'gin up an xml string. not necessary if you have an xml file
Dim x As String = "<x><data>lalala</data><data>lalala</data></data></x>"
Dim sr As System.IO.StringReader = New System.IO.StringReader(x)

'XML, SQLXML and Command objects for the insert

Dim xtr As System.Xml.XmlTextReader

Dim ParamXML As SqlTypes.SqlXmlDim MyCommand As SqlClient.SqlCommand

'Load up the text into a SqlXml object xtr = New System.Xml.XmlTextReader(sr)

ParamXML =

New SqlTypes.SqlXml(xtr)

'...and stuff it into the command MyCommand.CommandText = "dbo.MySampleXMLProc" MyCommand.Parameters.AddWithValue("@XMLToInsert", ParamXML) '...then fire the command MyCommand.ExecuteNonQuery()

-Eddie

Eddie Wuerch
MCM: SQL