Re: Display XML Output from SQL Server

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

Thanks for your response Terry,

Now I got that the SQLXML returned in your scenario is not a well-formed
xml document but a xml fragment(this is not what I would expect :P). IMO,
it is always recommended to return a valid xml document so that we don't
need to make the document reconstructing in application layer. For example,
you can wrap those fragment nodes with a "<root> ....</root>" root element.

Anyway, for your scenario that you've returned a XML fragment, I think we
may need to first parse it as Node and add it into the XmlDocument(which
has initially created a Root Element/DocumentElement). For example,
suppose I use SQLXML to return the following xml fragment:

====================
<Categories>
<CategoryID>1</CategoryID>
<CategoryName>Beverages</CategoryName>
<Description>Soft drinks, coffees, teas, beers, and ales</Description>
</Categories>
<Categories>
<CategoryID>2</CategoryID>
<CategoryName>Condiments</CategoryName>
<Description>Sweet and savory sauces, relishes, spreads, and
seasonings</Description>
</Categories>
====================

Then, in our page's codebehind, we change to use the below code to handle
the xml:

========================
..........................

XmlReader xdr = comm.ExecuteXmlReader();


XmlDocument doc = new XmlDocument();

//create a root element first
doc.AppendChild(doc.CreateElement("root"));



while(!xdr.EOF)
{
XmlNode node = doc.ReadNode(xdr);

doc.DocumentElement.AppendChild(node);

}


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");

...............
====================

Also, it is possible that the code need modification according the
complexity of your returned XML fragment and our recommendation is always
return a valid XML document at database layer so that our application can
directly use DOM api to load it and process it.

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.)


.

Quantcast