I have an asp.net (1.1) application that connects to a SQL server 2000 db. I
have a stored procedure in my db that out puts data in xml format.
What I need to be able to do is display that xml that I retrieve from my
stored proc and display it in a web page formatted as I want.
I have managed to get my xml to display as I wish if I manually run my
stored proc in QA and copy the xml into notepad and save it as Test.xml. I
reference my xsl file in this xml file and when I view this file in IE it
displays as I want it to.
What I need to know is how to do this programmatically. I am able to get my
xml data from the db. I just need help with transferring that data into a
page that I can display.
Thanks
18  3078 
Terry Holland wrote: I have an asp.net (1.1) application that connects to a SQL server 2000 db. I
have a stored procedure in my db that out puts data in xml format.
What I need to be able to do is display that xml that I retrieve from my
stored proc and display it in a web page formatted as I want.
I have managed to get my xml to display as I wish if I manually run my
stored proc in QA and copy the xml into notepad and save it as Test.xml. I
reference my xsl file in this xml file and when I view this file in IE it
displays as I want it to.
What I need to know is how to do this programmatically. I am able to get my
xml data from the db. I just need help with transferring that data into a
page that I can display.
ASP.NET has an XML control that allows you to apply a transformation, see
<http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpref/html/frlrfSystemWebUIWebControlsXmlClassTopic.asp>
I am not sure however it is well suited to help you with displaying the
query result. You might simply write the code for the transformation
yourself creating an XslTransform instance
<http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpref/html/frlrfSystemXmlXslXslTransformClassTopic.asp>
and using its Transform method.
The input for the Transform method could be an XPathDocument over an
XmlReader you get from executing the query
<http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpref/html/frlrfSystemDataSqlClientSqlCommandClassExecuteXmlR eaderTopic.asp>
--
Martin Honnen --- MVP XML http://JavaScript.FAQTs.com/
Ive looked at the examples you directed me to but Im still unclear.
I'll give an example of the type of thing Im trying to do and perhaps you
could tell me what I need to do to get my data displayed on a web page (and
possibly saved as a new xml file).
When I browse to webform1.aspx I would like to display the output from my
stored procedure TestXML using the xsl file NW.xsl and saved as a file
OutputDDMMYYYY.xml in the web directory.
Thanks in advance
I have a xsl file called NW.xsl
====================
<?xml version="1.0" encoding="ISO-8859-1"?>
<xsl:stylesheet version="1.0"
xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
<xsl:template match="/">
<html>
<body>
<table border="1">
<xsl:for-each select="ROOT/CU">
<tr>
<td colspan="2"><xsl:value-of select="CU_CN"/></td>
</tr>
<xsl:for-each select="ORD">
<tr>
<td><xsl:value-of select="ORD_ID"/></td>
<td><xsl:value-of select="ORD_DATE"/></td>
</tr>
<xsl:for-each select="OD">
<tr>
<td><xsl:value-of select="OD_UP"/></td>
<td><xsl:value-of select="OD_QTY"/></td>
</tr>
</xsl:for-each>
</xsl:for-each>
</xsl:for-each>
</table>
</body>
</html>
</xsl:template>
</xsl:stylesheet>
====================
I have created a stored procedure called TestXML
==================================
SELECT
dbo.Customers.CompanyName,
dbo.Orders.OrderID,
dbo.Orders.OrderDate,
dbo.[Order Details].UnitPrice,
dbo.[Order Details].Quantity,
dbo.Products.ProductName,
dbo.Products.QuantityPerUnit
FROM
dbo.Customers INNER JOIN
dbo.Orders ON dbo.Customers.CustomerID = dbo.Orders.CustomerID INNER JOIN
dbo.[Order Details] ON dbo.Orders.OrderID = dbo.[Order Details].OrderID
INNER JOIN
dbo.Products ON dbo.[Order Details].ProductID = dbo.Products.ProductID
for xml auto, elements
==================================
On my page webform1.aspx with the following function GetData
that gets called on Form_Load
============================================
Function GetData() As XPathDocument ' XmlReader
Dim SqlConnection1 As System.Data.SqlClient.SqlConnection
Dim SqlCommand1 As System.Data.SqlClient.SqlCommand
SqlConnection1 = New System.Data.SqlClient.SqlConnection
SqlCommand1 = New System.Data.SqlClient.SqlCommand
SqlConnection1.ConnectionString = "user id=WebUser;data
source=TERRY;initial catalog=Northwind"
SqlConnection1.Open()
SqlCommand1.CommandText = "TestXML"
SqlCommand1.CommandType = System.Data.CommandType.StoredProcedure
SqlCommand1.Connection = SqlConnection1
Dim o As XmlReader = SqlCommand1.ExecuteXmlReader
'not sure what to do here
End Function
"Martin Honnen" <ma*******@yahoo.de> wrote in message
news:eb**************@TK2MSFTNGP04.phx.gbl...
Terry Holland wrote:
I have an asp.net (1.1) application that connects to a SQL server 2000
db. I have a stored procedure in my db that out puts data in xml format.
What I need to be able to do is display that xml that I retrieve from my
stored proc and display it in a web page formatted as I want. I have
managed to get my xml to display as I wish if I manually run my stored
proc in QA and copy the xml into notepad and save it as Test.xml. I
reference my xsl file in this xml file and when I view this file in IE it
displays as I want it to. What I need to know is how to do this
programmatically. I am able to get my xml data from the db. I just need
help with transferring that data into a page that I can display.
ASP.NET has an XML control that allows you to apply a transformation, see
<http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpref/html/frlrfSystemWebUIWebControlsXmlClassTopic.asp>
I am not sure however it is well suited to help you with displaying the
query result. You might simply write the code for the transformation
yourself creating an XslTransform instance
<http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpref/html/frlrfSystemXmlXslXslTransformClassTopic.asp>
and using its Transform method.
The input for the Transform method could be an XPathDocument over an
XmlReader you get from executing the query
<http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpref/html/frlrfSystemDataSqlClientSqlCommandClassExecuteXmlR eaderTopic.asp>
--
Martin Honnen --- MVP XML
http://JavaScript.FAQTs.com/
Ive looked at the examples you directed me to but Im still unclear.
I'll give an example of the type of thing Im trying to do and perhaps you
could tell me what I need to do to get my data displayed on a web page (and
possibly saved as a new xml file).
When I browse to webform1.aspx I would like to display the output from my
stored procedure TestXML using the xsl file NW.xsl and saved as a file
OutputDDMMYYYY.xml in the web directory.
Thanks in advance
I have a xsl file called NW.xsl
====================
<?xml version="1.0" encoding="ISO-8859-1"?>
<xsl:stylesheet version="1.0"
xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
<xsl:template match="/">
<html>
<body>
<table border="1">
<xsl:for-each select="ROOT/CU">
<tr>
<td colspan="2"><xsl:value-of select="CU_CN"/></td>
</tr>
<xsl:for-each select="ORD">
<tr>
<td><xsl:value-of select="ORD_ID"/></td>
<td><xsl:value-of select="ORD_DATE"/></td>
</tr>
<xsl:for-each select="OD">
<tr>
<td><xsl:value-of select="OD_UP"/></td>
<td><xsl:value-of select="OD_QTY"/></td>
</tr>
</xsl:for-each>
</xsl:for-each>
</xsl:for-each>
</table>
</body>
</html>
</xsl:template>
</xsl:stylesheet>
====================
I have created a stored procedure called TestXML
==================================
SELECT
dbo.Customers.CompanyName,
dbo.Orders.OrderID,
dbo.Orders.OrderDate,
dbo.[Order Details].UnitPrice,
dbo.[Order Details].Quantity,
dbo.Products.ProductName,
dbo.Products.QuantityPerUnit
FROM
dbo.Customers INNER JOIN
dbo.Orders ON dbo.Customers.CustomerID = dbo.Orders.CustomerID INNER JOIN
dbo.[Order Details] ON dbo.Orders.OrderID = dbo.[Order Details].OrderID
INNER JOIN
dbo.Products ON dbo.[Order Details].ProductID = dbo.Products.ProductID
for xml auto, elements
==================================
On my page webform1.aspx with the following function GetData
that gets called on Form_Load
============================================
Function GetData() As XPathDocument ' XmlReader
Dim SqlConnection1 As System.Data.SqlClient.SqlConnection
Dim SqlCommand1 As System.Data.SqlClient.SqlCommand
SqlConnection1 = New System.Data.SqlClient.SqlConnection
SqlCommand1 = New System.Data.SqlClient.SqlCommand
SqlConnection1.ConnectionString = "user id=WebUser;data
source=TERRY;initial catalog=Northwind"
SqlConnection1.Open()
SqlCommand1.CommandText = "TestXML"
SqlCommand1.CommandType = System.Data.CommandType.StoredProcedure
SqlCommand1.Connection = SqlConnection1
Dim o As XmlReader = SqlCommand1.ExecuteXmlReader
'not sure what to do here
End Function
"Martin Honnen" <ma*******@yahoo.de> wrote in message
news:eb**************@TK2MSFTNGP04.phx.gbl...
Terry Holland wrote:
I have an asp.net (1.1) application that connects to a SQL server 2000
db. I have a stored procedure in my db that out puts data in xml format.
What I need to be able to do is display that xml that I retrieve from my
stored proc and display it in a web page formatted as I want. I have
managed to get my xml to display as I wish if I manually run my stored
proc in QA and copy the xml into notepad and save it as Test.xml. I
reference my xsl file in this xml file and when I view this file in IE it
displays as I want it to. What I need to know is how to do this
programmatically. I am able to get my xml data from the db. I just need
help with transferring that data into a page that I can display.
ASP.NET has an XML control that allows you to apply a transformation, see
<http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpref/html/frlrfSystemWebUIWebControlsXmlClassTopic.asp>
I am not sure however it is well suited to help you with displaying the
query result. You might simply write the code for the transformation
yourself creating an XslTransform instance
<http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpref/html/frlrfSystemXmlXslXslTransformClassTopic.asp>
and using its Transform method.
The input for the Transform method could be an XPathDocument over an
XmlReader you get from executing the query
<http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpref/html/frlrfSystemDataSqlClientSqlCommandClassExecuteXmlR eaderTopic.asp>
--
Martin Honnen --- MVP XML
http://JavaScript.FAQTs.com/
Hi Terry,
As for XSLT transformation, .net framework has provided built-in component
classes to support it. Since you're developing the web application under
..net framework 1.1, you can use the "System.Xml.Xsl.XslTransform" class.
"XslTransform" class has a method named "Transform" which can help
tranforming XML document against a given XSLT template file. And the XML
document or XSLT template file can be supplied in the form of an existing
file on the disk or a stream in the memory (or retrieved from database in
your case...).
Here are some web articles discussing on performing XSLT tranforming
through .net framework classes:
#XSLT Processing in .NET http://www.xml.com/pub/a/2002/08/14/dotnetxslt.html
#XSL Transformation http://www.csharpfriends.com/Article...x?articleID=75
BTW, .net framework 2.0 has provided enhanced "XslCompiledTransform" class
for performing XSLT tranforming:
#Using the XslCompiledTransform Class http://msdn2.microsoft.com/en-us/library/0610k0w4.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.)
Guys
I appreciate your responses but none of the links are giving me the info
that will alow me to do what I want quickly. Are there no examples of doing
what I described in earlier posting ie execute a 'For Xml' stored procedure
(which returns an xml fragment and then displaying this in a web page? I
would have thought that this is a really common scenario but I have not seen
any complete examples yet. If I had more time I would spend time trying to
piece all the information from all links that you guys have provided.
However I dont have the time so unless one of you can point me to a full
example of what I need I will create a crystal report instead
tia
Terry Holland
"Steven Cheng[MSFT]" wrote: Hi Terry,
As for XSLT transformation, .net framework has provided built-in component
classes to support it. Since you're developing the web application under
.net framework 1.1, you can use the "System.Xml.Xsl.XslTransform" class.
"XslTransform" class has a method named "Transform" which can help
tranforming XML document against a given XSLT template file. And the XML
document or XSLT template file can be supplied in the form of an existing
file on the disk or a stream in the memory (or retrieved from database in
your case...).
Here are some web articles discussing on performing XSLT tranforming
through .net framework classes:
#XSLT Processing in .NET
http://www.xml.com/pub/a/2002/08/14/dotnetxslt.html
#XSL Transformation
http://www.csharpfriends.com/Article...x?articleID=75
BTW, .net framework 2.0 has provided enhanced "XslCompiledTransform" class
for performing XSLT tranforming:
#Using the XslCompiledTransform Class
http://msdn2.microsoft.com/en-us/library/0610k0w4.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.)
Dude,
All of the links are giving you information that you need to do what
you want quickly. Sometimes you just have to work things out.
In this situation I do:
// inside my data layer
StringBuilder sb = new StringBuilder();
XmlDocument doc = null;
try
{
cn.Open();
// XmlTextReader only returns the first 8000 characters. Doing this
gives you the full doc regardless of size.
dr = cmd.ExecuteReader(CommandBehaviour.CloseConnection );
while(dr.Read())
{
sb.Append(dr.GetString(0));
}
doc = new XmlDocument();
doc.LoadXml(sb.ToString());
}
finally
{
// cleanup
KillObjects(cn,cmd,dr);
}
return doc;
and then I can transform that XmlDocument with
XslCompiledTransform transform = new XslCompiledTransform();
transform.Load( ... ); // some path from Web.Config
// Get the transformed result
StringWriter sw = new StringWriter();
transform.Transform(source, null, sw);
return sw.ToString();
That gives me a string which I can insert into a Literal control.
I'm 99% positive that I could do it faster than this, but the
performance is good enough, and I knocked it out on a tight schedule.
Look at the various XmlReaders if you want to tweak it, or hope a
passing MVP takes pity on my humble n00bdom.
Terry Holland wrote: Guys
I appreciate your responses but none of the links are giving me the info
that will alow me to do what I want quickly. Are there no examples of doing
what I described in earlier posting ie execute a 'For Xml' stored procedure
(which returns an xml fragment and then displaying this in a web page? I
would have thought that this is a really common scenario but I have not seen
any complete examples yet. If I had more time I would spend time trying to
piece all the information from all links that you guys have provided.
However I dont have the time so unless one of you can point me to a full
example of what I need I will create a crystal report instead
tia
Terry Holland
"Steven Cheng[MSFT]" wrote:
Hi Terry,
As for XSLT transformation, .net framework has provided built-in component
classes to support it. Since you're developing the web application under
.net framework 1.1, you can use the "System.Xml.Xsl.XslTransform" class.
"XslTransform" class has a method named "Transform" which can help
tranforming XML document against a given XSLT template file. And the XML
document or XSLT template file can be supplied in the form of an existing
file on the disk or a stream in the memory (or retrieved from database in
your case...).
Here are some web articles discussing on performing XSLT tranforming
through .net framework classes:
#XSLT Processing in .NET
http://www.xml.com/pub/a/2002/08/14/dotnetxslt.html
#XSL Transformation
http://www.csharpfriends.com/Article...x?articleID=75
BTW, .net framework 2.0 has provided enhanced "XslCompiledTransform" class
for performing XSLT tranforming:
#Using the XslCompiledTransform Class
http://msdn2.microsoft.com/en-us/library/0610k0w4.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.)
Do you know where I could find an example given at http://www.devarticles.com/c/a/ASP/S...Combination/3/ but updated to work for asp.net
Terry
"Steven Cheng[MSFT]" wrote: Hi Terry,
As for XSLT transformation, .net framework has provided built-in component
classes to support it. Since you're developing the web application under
.net framework 1.1, you can use the "System.Xml.Xsl.XslTransform" class.
"XslTransform" class has a method named "Transform" which can help
tranforming XML document against a given XSLT template file. And the XML
document or XSLT template file can be supplied in the form of an existing
file on the disk or a stream in the memory (or retrieved from database in
your case...).
Here are some web articles discussing on performing XSLT tranforming
through .net framework classes:
#XSLT Processing in .NET
http://www.xml.com/pub/a/2002/08/14/dotnetxslt.html
#XSL Transformation
http://www.csharpfriends.com/Article...x?articleID=75
BTW, .net framework 2.0 has provided enhanced "XslCompiledTransform" class
for performing XSLT tranforming:
#Using the XslCompiledTransform Class
http://msdn2.microsoft.com/en-us/library/0610k0w4.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.)
Hey Terry,
I think Flinky has provided the pretty good code example(thought it is
using .net framework 2.0's class). And as for the article you mentioned, it
is using the original ASP+COM approach and we should use .net framework's
XML processing classes in our case. Anyway, I've built the following test
page for your reference (I used the Northwind db's Categories table to make
it simplified):
==========xslt file=============
<?xml version="1.0" encoding="UTF-8" ?>
<xsl:stylesheet version="1.0"
xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
<xsl:template match="/">
<html>
<body>
<table border="1">
<tr>
<td>CategoryID:</td>
<td><xsl:value-of select="/Categories/CategoryID/text()"/></td>
</tr>
<tr>
<td>CategoryName:</td>
<td><xsl:value-of select="/Categories/CategoryName/text()"/></td>
</tr>
<tr>
<td>Description:</td>
<td><xsl:value-of select="/Categories/Description/text()"/></td>
</tr>
</table>
</body>
</html>
</xsl:template>
</xsl:stylesheet>
===================================
==============aspx page codebehind========
public class transform1 : System.Web.UI.Page
{
protected System.Data.SqlClient.SqlConnection conn;
private void Page_Load(object sender, System.EventArgs e)
{
//Get XML data from database
string sqlxml = "select top 1 CategoryID, CategoryName, Description from
Categories FOR XML AUTO , Elements";
conn.Open();
SqlCommand comm = new SqlCommand(sqlxml, conn);
comm.CommandType = System.Data.CommandType.Text;
XmlReader xdr = comm.ExecuteXmlReader();
//load the xml from xmlreader into xmldocument for further processing
XmlDocument doc = new XmlDocument();
doc.Load(xdr);
xdr.Close();
conn.Close();
//create XslTransform instance for transforming
XslTransform xsl = new XslTransform();
xsl.Load(Server.MapPath("./template.xslt"));
//flush the transformed result into current page's response output stream
Response.ClearContent();
xsl.Transform(doc.CreateNavigator(), null, Response.Output,new
XmlUrlResolver());
Response.End();
}
...................
}
=============================
Also, in the above code I simply flush the transformed result directly into
response output, you can choose to flush it into anyother OutputStream you
like(e.g the StreamWriter or a filestream ...)
Hope this also 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.)
"Steven Cheng[MSFT]" <st*****@online.microsoft.com> wrote in message
news:L$**************@TK2MSFTNGXA01.phx.gbl... Hey Terry,
I think Flinky has provided the pretty good code example(thought it is
using .net framework 2.0's class). And as for the article you mentioned,
it
is using the original ASP+COM approach and we should use .net framework's
XML processing classes in our case. Anyway, I've built the following test
page for your reference (I used the Northwind db's Categories table to
make
it simplified):
Which test page?
Hi Terry,
Did you see the xslt file and test page 's codebehind code in my last
reply? That page is quite simple and you just need to put the same
codebehind(page_load code) into your own empty aspx page. Also, as I said I
used the Northwind test database so as to make the test simplified. Anyway,
I'll repaste the test code snippet below in case you didn't see them in the
original thread:
==========xslt file=============
<?xml version="1.0" encoding="UTF-8" ?>
<xsl:stylesheet version="1.0"
xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
<xsl:template match="/">
<html>
<body>
<table border="1">
<tr>
<td>CategoryID:</td>
<td><xsl:value-of select="/Categories/CategoryID/text()"/></td>
</tr>
<tr>
<td>CategoryName:</td>
<td><xsl:value-of select="/Categories/CategoryName/text()"/></td>
</tr>
<tr>
<td>Description:</td>
<td><xsl:value-of select="/Categories/Description/text()"/></td>
</tr>
</table>
</body>
</html>
</xsl:template>
</xsl:stylesheet>
===================================
==============aspx page codebehind========
public class transform1 : System.Web.UI.Page
{
protected System.Data.SqlClient.SqlConnection conn;
private void Page_Load(object sender, System.EventArgs e)
{
//Get XML data from database
string sqlxml = "select top 1 CategoryID, CategoryName, Description from
Categories FOR XML AUTO , Elements";
conn.Open();
SqlCommand comm = new SqlCommand(sqlxml, conn);
comm.CommandType = System.Data.CommandType.Text;
XmlReader xdr = comm.ExecuteXmlReader();
//load the xml from xmlreader into xmldocument for further processing
XmlDocument doc = new XmlDocument();
doc.Load(xdr);
xdr.Close();
conn.Close();
//create XslTransform instance for transforming
XslTransform xsl = new XslTransform();
xsl.Load(Server.MapPath("./template.xslt"));
//flush the transformed result into current page's response output stream
Response.ClearContent();
xsl.Transform(doc.CreateNavigator(), null, Response.Output,new
XmlUrlResolver());
Response.End();
}
..................
}
=============================
Also, in the above code I simply flush the transformed result directly into
response output, you can choose to flush it into anyother OutputStream you
like(e.g the StreamWriter or a filestream ...)
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.)
Thanks Steven - I didn't notice in original post.
Ive managed to get my project working by flushing into a filestream object
and saved this as a html file.
How would I save the output from the .ExecuteXMLReader as a valid well
formed xml file including the reference to my xslt template file? (the output
from .ExecuteXMLReader does not have a root node nor xml version info)
<?xml version="1.0"?>
<?xml-stylesheet type="text/xsl" href="test3.xsl"?>
Thanks for your patience on this. My XML knowledge is very poor. Perhaps
you could direct me to come easy to follow tutorials as well
Terry Holland
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 xslsheet instruction
XmlProcessingInstruction newPI;
string PItext = "type='text/xsl' href='template.xslt'";
newPI = doc.CreateProcessingInstruction("xml-stylesheet", 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.)
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.ConnectionS tring)
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-stylesheet",
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 xslsheet instruction
XmlProcessingInstruction newPI;
string PItext = "type='text/xsl' href='template.xslt'";
newPI = doc.CreateProcessingInstruction("xml-stylesheet", 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.)
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-stylesheet", 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.)
Thanks 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.
How can I return a valid xml document from SQL Server? I am using the For
XML Auto, Elements instruction
SELECT CategoryID, CategoryName, Description
FROM dbo.Categories
for xml auto, elements
and this returns an xml fragment without a root node. I did have a look at
Explicit, but it scared me :-)
Thanks for your response Terry,
Since you mentioned that you're using SQL Server 2000, I'm afraid it would
be quite hard to customize the SQL XML query result in SQL Server 2000.
This is because SQL Server 2000 doesn't support direct manipulation against
T-SQL. I've discussed with some of our SQL engineers and they think you can
consider using store procedure to store the query result in a temp table
and then retrieve the result out, customize it (add root element so as to
make it become a valid xml document) and flush it out to the client. This
approach will add some overhead; however is still a clear solution.
In SQL Server 2005, FOR XML query syntax has built-in support for providing
a root element around the returned xml query result. e.g.
==============
select LocationID, Name from Production.Location FOR XML AUTO, ROOT
('rootElem')
===============
#SQL Server 2005 Books Online -- Basic Syntax of the FOR XML Clause http://msdn2.microsoft.com/en-us/library/ms190922.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.)
Ok thanks.
In that case I will manipulate in my application layer until we migrate to
SQL Server 2005
"Steven Cheng[MSFT]" wrote: Thanks for your response Terry,
Since you mentioned that you're using SQL Server 2000, I'm afraid it would
be quite hard to customize the SQL XML query result in SQL Server 2000.
This is because SQL Server 2000 doesn't support direct manipulation against
T-SQL. I've discussed with some of our SQL engineers and they think you can
consider using store procedure to store the query result in a temp table
and then retrieve the result out, customize it (add root element so as to
make it become a valid xml document) and flush it out to the client. This
approach will add some overhead; however is still a clear solution.
In SQL Server 2005, FOR XML query syntax has built-in support for providing
a root element around the returned xml query result. e.g.
==============
select LocationID, Name from Production.Location FOR XML AUTO, ROOT
('rootElem')
===============
#SQL Server 2005 Books Online -- Basic Syntax of the FOR XML Clause
http://msdn2.microsoft.com/en-us/library/ms190922.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.)
That's fine :-)
If you meet any further question or anything else we can help, please feel
free to post here.
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.) This discussion thread is closed Replies have been disabled for this discussion. Similar topics
3 posts
views
Thread by Hemanth |
last post: by
|
1 post
views
Thread by michi |
last post: by
|
4 posts
views
Thread by dmiller23462 |
last post: by
|
5 posts
views
Thread by David Greenwood |
last post: by
|
6 posts
views
Thread by Roy Chastain |
last post: by
|
4 posts
views
Thread by Jono |
last post: by
|
3 posts
views
Thread by shapper |
last post: by
| | | | | | | | | | | | | | | | | | | | | | | |
