Convert MSXML DOMDocument to be used as value for SqlDbType.Xml ADO.NET parameter

Question

I have a stored procedure that accepts an xml parameter (SqlDbType.Xml) that is being called from some .NET/ ADO.NET code. I have tested this through a test harness using a System.XML.XMLReader to read some test XML from a file;

Dim xmlParam As SqlParameter = New SqlParameter("@xml", SqlDbType.Xml)
xmlParam.Value = New SqlTypes.SqlXml(XmlReader.Create(txtXMLFile.Text))
.Parameters.Add(xmlParam)

In the application this code is going into, the actual XML is in a VB6 object, in an MSXML2.DOMDocument40 object (the VB6 project references MSXML4) I have worked out how to marshal the MSXML.DOMDocument40 across to .NET from VB6 - in fact the .NET project already references MSXML4 so that is all fine. Now all I need to do is get this converted properly so it can be passed into the stored proc.

System.XML.XMLReader has a number of overloads that take stream objects, I'm wondering if I can create a stream on top of the MSXML object? Or if I can convert the MSXML to a .NET XML type, which can then be used?

Obviously performance will be a consideration but for the moment I just need to work out how to do it any way!

Answer 1

The better idea is to not mix MSXML and .NET at all.

If you must, then get the xml string from the MSXML object, and either create an XmlReader over the string, or else load the string into an XmlDocument.

Answer 2

The MSXML DOM will save to anything the implements the COM IStream interface so if you can find an implementation of that that maps to a .NET stream you should be able to acheive this fairly efficiently.