I found the VB aspx script and it creates an xml and schema .. it does exactically what I want .. however .. I want the routine to just return the schema and xml and not echo anything to the screen .. just return the value or string of the schema and xml
.. here is the code :
' Create a new command object that uses our connection
' and set the text of the command to be executed
myCommand = New SqlDataAdapter("select vnumber,license,agency from vehicles order by vnumber;", _
myConnection)
' Create a new dataset
myDataSet = New DataSet()
' This line changes the name of the outer container
' tags in the resulting xml file.
myDataSet.DataSetName = "something"
' Use the command to fill our DataSet
myCommand.Fill(myDataSet, "vehicles")
' We've got our data... now save it to the output file:
' File paths
Dim strXsdPath As String = Server.MapPath("db_xml.xsd")
Dim strXmlPath As String = Server.MapPath("db_xml.xml")
' Write out schema to .xsd file and data to .xml file
' myDataSet.WriteXmlSchema(strXsdPath)
' myDataSet.WriteXml(strXmlPath, XmlWriteMode.IgnoreSchema)
' Could also write both schema and data to a single file
myDataSet.WriteXml(strXmlPath, XmlWriteMode.WriteSchema)
' Or ignore the schema and just write data to .xml file
'myDataSet.WriteXml(strXmlPath)
End Sub
</script>
<html>
<head>
<title>ASP.NET ADO.NET to XML Sample</title>
</head>
<body>
<p>
XML file written...
click <a href="db_xml.xml">here</a> to view the .xml file.
</p>
<p>
XSD file written...
click <a href="db_xml.xsd">here</a> to view the .xsd file.
</p>
</body>
</html>
Instead or writing the file out .. how can I just return the value :
' Could also write both schema and data to a single file
myDataSet.WriteXml(strXmlPath, XmlWriteMode.WriteSchema)
What I am trying to do is to return a database schema and xml to an Web Ajax framework to populate a grid .. It appears that XML Webservices .. endpoints and 'soap' is going away in SQL after version 2008 .. here is the article from Microsoft :
<div>Native XML Web Services: Deprecated in SQL Server 2008</div> <div id=ecxmainSection> <div id=ecxmainBody>
<div>This feature will be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature.
Native XML Web Services (SOAP/HTTP endpoints) is deprecated; Native XML Web Services will be removed from a future version of SQL Server. Plan to convert your existing SOAP/HTTP endpoints
to use Windows Communications Foundation (WCF) or ASP.NET. Avoid using SOAP/HTTP endpoints in new development work.</div> <div> </div> <div>I am trying to come up with a way of extracting data via xml from a Sql Server for populating a web
development project I am working on .. outside of VS 2010.</div> <div> </div> <div>Hope that helps explain what I am trying to acomplish.</div> <div> </div> <div>Rick Lipkin</div> <div>r1.1955@live.com</div></div></div>
Marked as answer by Vince Xu - MSFT on Sep 07, 2010 02:40 AM
lipkinrm
0 Points
2 Posts
Sql2XML in aspx
Aug 26, 2010 08:57 PM|LINK
To All
I found the VB aspx script and it creates an xml and schema .. it does exactically what I want .. however .. I want the routine to just return the schema and xml and not echo anything to the screen .. just return the value or string of the schema and xml .. here is the code :
<%@ Page Language="VB" %>
<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.SqlClient" %>
<%@ Import Namespace="System.Xml" %>
<script language="VB" runat="server">
Sub Page_Load(sender as Object, e as EventArgs)
Dim myConnection As SqlConnection
Dim myCommand As SqlDataAdapter
Dim myDataSet As DataSet
' Create connection and set connection string
myConnection = New SqlConnection( "Data Source=RICKLIPKIN-PC;" & _
"Initial Catalog=vehicle;" & _
"User Id=vehicleuser;" & _
"Password=lipkinrm" )
' Create a new command object that uses our connection
' and set the text of the command to be executed
myCommand = New SqlDataAdapter("select vnumber,license,agency from vehicles order by vnumber;", _
myConnection)
' Create a new dataset
myDataSet = New DataSet()
' This line changes the name of the outer container
' tags in the resulting xml file.
myDataSet.DataSetName = "something"
' Use the command to fill our DataSet
myCommand.Fill(myDataSet, "vehicles")
' We've got our data... now save it to the output file:
' File paths
Dim strXsdPath As String = Server.MapPath("db_xml.xsd")
Dim strXmlPath As String = Server.MapPath("db_xml.xml")
' Write out schema to .xsd file and data to .xml file
' myDataSet.WriteXmlSchema(strXsdPath)
' myDataSet.WriteXml(strXmlPath, XmlWriteMode.IgnoreSchema)
' Could also write both schema and data to a single file
myDataSet.WriteXml(strXmlPath, XmlWriteMode.WriteSchema)
' Or ignore the schema and just write data to .xml file
'myDataSet.WriteXml(strXmlPath)
End Sub
</script>
<html>
<head>
<title>ASP.NET ADO.NET to XML Sample</title>
</head>
<body>
<p>
XML file written...
click <a href="db_xml.xml">here</a> to view the .xml file.
</p>
<p>
XSD file written...
click <a href="db_xml.xsd">here</a> to view the .xsd file.
</p>
</body>
</html>
Instead or writing the file out .. how can I just return the value :
' Could also write both schema and data to a single file
myDataSet.WriteXml(strXmlPath, XmlWriteMode.WriteSchema)
Thanks
Rick Lipkin
r1.1955@live.com
luappy13
Participant
1874 Points
408 Posts
Re: Sql2XML in aspx
Aug 26, 2010 10:00 PM|LINK
Well you need to show us the method WriteXml and WriteXmlSchema (by the looks of it they are extension methods and will have a signature like:
public static WriteXml(this DataSet data, blah, blah)
lipkinrm
0 Points
2 Posts
Re: Sql2XML in aspx
Aug 27, 2010 03:38 PM|LINK
WriteXml is part of the .net 4 framework :
http://msdn.microsoft.com/en-us/library/system.data.dataset.writexml.aspx
WriteXML is also part of the .net 4 framework :
http://msdn.microsoft.com/en-us/library/system.data.dataset.writexmlschema.aspx
What I am trying to do is to return a database schema and xml to an Web Ajax framework to populate a grid .. It appears that XML Webservices .. endpoints and 'soap' is going away in SQL after version 2008 .. here is the article from Microsoft :
<div>Native XML Web Services: Deprecated in SQL Server 2008</div> <div id=ecxmainSection> <div id=ecxmainBody>http://technet.microsoft.com/en-us/library/cc280436.aspx
<div>This feature will be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature.
Native XML Web Services (SOAP/HTTP endpoints) is deprecated; Native XML Web Services will be removed from a future version of SQL Server. Plan to convert your existing SOAP/HTTP endpoints to use Windows Communications Foundation (WCF) or ASP.NET. Avoid using SOAP/HTTP endpoints in new development work.</div> <div> </div> <div>I am trying to come up with a way of extracting data via xml from a Sql Server for populating a web development project I am working on .. outside of VS 2010.</div> <div> </div> <div>Hope that helps explain what I am trying to acomplish.</div> <div> </div> <div>Rick Lipkin</div> <div>r1.1955@live.com</div></div></div>