Re: Display XML Output from SQL Server

But the problem remains that as soon as I run the
doc.Load(xdr);
I will get an error because xdr contains an xml fragment with no root node.

Error message
=========
This document already has a DocumentElement node


Example
======
to illustrate what I mean try running this:

Private Sub Page_Load(ByVal sender As Object, ByVal e As
System.EventArgs) Handles MyBase.Load
Dim sqlxml As String = "SELECT TOP 1 CustomerID, CompanyName,
ContactName FROM Customers FOR XML AUTO , Elements"
Dim cn As New SqlConnection("Data Source=(local); initial
catalog=Northwind; uid=Test")
Dim cmd As New SqlCommand
Dim docXML As New XmlDocument

cn.Open()
'Dim cmd As New
Microsoft.Data.SqlXml.SqlXmlCommand(cn.ConnectionString)

With cmd
.Connection = cn
'.Transaction = tr
.CommandType = CommandType.Text
.CommandText = sqlxml

Dim xdr As XmlReader = .ExecuteXmlReader()
docXML.Load(xdr)


Dim newPI As XmlProcessingInstruction
Dim PItext As String = "type='text/xsl' href='template.xslt'"

newPI = docXML.CreateProcessingInstruction("xml-style***",
PItext)

docXML.InsertBefore(newPI, docXML.DocumentElement)


'load the xml from xmlreader into xmldocument for further
processing
docXML.Save("C:\Test\test.xml")

xdr.Close()

End With
'tr.Commit()
cn.Close()

End Sub

=====================

you will find that this will work fine as long as only one customer is
returned. If you change sql to
select top 2......

then yo will get this error as soon as you try to execute
doc.Load(xdr);




"Steven Cheng[MSFT]" wrote:

Thanks for your followup Terry,

As for your new question, I think it can be done through the XmlDocument
class's methods. There is a "CreateProcessingInstrunction" method which can
help create a XML processing instruction (such as the XSLT link
instruction). We can simply create such a processing instruction and insert
it into the XmlDocument(load from XmlReader) and then use the
XmlDocument.Save method to flush it into file. For exapmle:

=============================
XmlReader xdr = comm.ExecuteXmlReader();

XmlDocument doc = new XmlDocument();

doc.Load(xdr);

//save the xmlstream into file(with xsl*** instruction

XmlProcessingInstruction newPI;
string PItext = "type='text/xsl' href='template.xslt'";
newPI = doc.CreateProcessingInstruction("xml-style***", PItext);

doc.InsertBefore(newPI,doc.DocumentElement);

doc.Save(@"d:\temp\output.xml");
...................
==========================

In addition, I would suggest you have a look at the .NET's XML processing
reference and you'll find many useful stuff there:

#XML Documents and Data
http://msdn2.microsoft.com/en-us/library/2bcctyt8.aspx

Hope this helps.

Regards,

Steven Cheng
Microsoft Online Community Support


==================================================

When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.

==================================================


This posting is provided "AS IS" with no warranties, and confers no rights.



Get Secure! www.microsoft.com/security
(This posting is provided "AS IS", with no warranties, and confers no
rights.)




.