Return XML to response.write from SQL XML type
Hi,
I am trying to retrive some XML from my SQL server 2005 database. The XML is in a table called "myxml" and it is being stored a native XML. The Field type is "XML". The VB code returns nothing when I select SELECT xml. How ever if i SELECT NAME it displays the correct name from the name field. how do I return the XML to be viewed as XML throught the response.write?
Thanks!
Here is my Table definition:
|
Column Name |
Data Type | Allow Nulls |
| Id | Int | |
| xml | xml | Yes |
| NAME | Nvarchar(50) | yes |
My XML that is stored in the XML field:
<ROOT> <CHAPTER> <TITLE>This is a test</TITLE> </CHAPTER> </ROOT>
Here is my VB code:
Dim myconnection As SqlConnection Dim mycommand As SqlCommand myconnection = New SqlConnection() myconnection.ConnectionString = _ ConfigurationManager.ConnectionStrings("myxmlConnectionString2").ConnectionString Dim strSQL As String = "SELECT [xml] FROM myxml WHERE id = 76" ' create SQL command instance mycommand = New SqlCommand(strSQL, myconnection) Try ' open database connection myconnection.Open() 'execute T-SQL command Dim dr = mycommand.ExecuteReader() While dr.read() Response.Write("<p>" & dr(0).ToString() & "</p>") End While Response.Write("GOT IT") Catch ex As Exception Response.Write(ex.Message) Response.Write(strSQL) Response.End() End Try ' Close connection myconnection.Close()
If I was helpful, please mark "answered" so I can get credit. Thanks!
|
0 |
|
Bonekrusher
7/23/2007 11:13:59 AM
asp.net.sql-datasource
29906 articles. 
0 followers.
1 Replies
1010 Views
I figured it out. Here is the correct code:
Dim sqlconn As New Data.SqlClient.SqlConnection Dim sqlcmd As New Data.SqlClient.SqlCommand Dim xmldoc As New XmlDocument sqlconn.ConnectionString = _ ConfigurationManager.ConnectionStrings("myxmlConnectionString2").ConnectionString sqlconn.Open() sqlcmd.Connection = sqlconn sqlcmd.CommandText = "SELECT [xml] FROM myxml WHERE id = 81" Dim myXR = sqlcmd.ExecuteXmlReader() Dim sb As New StringBuilder() While myXR.Read() sb.Append(myXR.ReadOuterXml()) End While Dim finXML As String = sb.ToString()
If I was helpful, please mark "answered" so I can get credit. Thanks!
|
|
0 |
|
Bonekrusher
7/23/2007 4:15:36 PM
Similar Artilces:
Select XML Data type (xml) from sql server 2005 and then save to a XML file on local machine?? Help !
Hi All I have a problem i am selecting a row of data from a database, within that row is a column in which i build up a xml in a stored procedure, it creates an xml and stores it in the column name xmldata which is of xml datatype. This works great My problem is how do i now create a xml file on my local machine with that data, below is what i have so far :- Dim conn1 As New SqlConnection()conn1.ConnectionString = (ConfigurationManager.ConnectionStrings("ConnectionString").ConnectionString) conn1.Open()Dim DBCmd As New SqlCommand Dim DataRecieved As SqlDataReaderDBCmd...
Getting standard SQL datatypes from XML, i.e. XML->SQL
Hi all, I'm wanting to get standard SQL data types from an XML document in ASE. Looking at the documentation in 'XML Services in Adaptive Server Enterprise', it all seems to be concerned with transforming SQL into XML, or manipluating XML. e.g. how do you get a query like :- select filename, xmlextract("//book/title" , content) from xmlsfsTab to return a 'SQL' result set so the title column does NOT containt XML fragments, i.e. it looks like:- filename title ---------------- -------------------------------- bookstore.1.xml ...
Save SQL XML field type to a xml file
Hi, I have a table which holds raw xml in a field with type XML. How do I export that XML field to an XML file? Thanks Bones If I was helpful, please mark "answered" so I can get credit. Thanks! You can do by using CLR based stored procedure. Here is a link. http://www.sqldbatips.com/showarticle.asp?ID=23 Farooq Kaiser, MCTS, MCPPlease Mark As Answer if my answer helped you.http://www.Fairnet.com Thanks for the help but unfortunitly this is for an other version on .NET. When I try to compile the .DLL I get the errors for "File.WriteAll" and...
Read XML data from SQL Server XML data type field
Hi,does anyone actually use the XML data type for SQL Server database columns? I can't really find any help for my problems.In my database there is a table with personal data such as name, telephone and so one. One of the columns is of type XML, because I need to store multilingual data in there, something like:<career> <careertext lang="en">My career</careertext> <careertext lang="de">Meine Karriere</careertext></career>I'm using a Stored Procedure to retrieve all data from the database and in my data access object th...
Retreive xml data from sql 2005 xml data type column
hi everyone, I am in a messy situation and dieing to get rescued.I have a database with an untyped xml datatype column named xmlfile. The xml inside is invoice of customer & looks like this.<User><INVOICE><BILL Time="May 18 2008 12:47AM">0.1869</BILL><BILL Time="May 18 2008 1:00AM">22.25</BILL><BILL Time="May 18 2008 1:00AM">22.25</BILL></INVOICE></User>I wanted to retrieve the <BILL></BILL> Elements and put it through asp:datagrid or asp:repeater . I w...
How to write to Data to SQL Server XML data type
Hello, I have worked with SQL Server 2000 but now we have a requirement where I need to write values from an asp.net form into an XML type in SQL Server 2005.I have never used XML as a type in SQL Server 2005. How do we write xml into xml type. For example the structure of XML is something like:<application><applicationID = "value"></applicationID><customerName="value></customerName></application>I have to write this kind of XML into the XML type and later retrieve these XML values and populate the form again.Kindly suggest. Thanks a l...
XML Manipulation (Read, Write, Update, Searching XML file like we do in SQL query)
Hi ,I want to make a ASP.NET application which contains XML file as the data container(I am not using any Database like MS SQL Server or MS Access.)I want to enter new data in the xml file through ASP.NET.I also want to search the existing data in xml file (like we search data using select clause in SQL and The XML file is going to contain 1000s of tupples).I want to update a particular (tupple) fields of that XML file using ASP.NET.(Actually I don't want to use data grid for this). Regards,Himanshu Hi himanshuweb2 , Edit XML files using a GridViewhttp://www.codeproj...
Passing an XML variable argument to a SQL Server 2005 Stored Procedure with an XML data type
Is it possible to pass an XML variable to a SQL Server 2005 Stored Procedure with the XML data type as it's receiving argument? I can get the SQL Server Procedure to work perfectly with XML passed to it from another Stored Procedure, but as soon as I define a Stored Procedure from within a datawindow to pass parameters to it, I get "Cannot create DataWindow" and "XML parse error occurred on line number 1, near the XML text "0"". Essentially, I want to use the XML string, built by data keyed by the client within a datawindow, to be loaded to a temp t...
SQL Sever 2005 write XML data stored in a Table out to a XML file on my local machine
Hello All Just wondered if someone could help me with a bit of T-SQL, i have a application in ASP.NET/VB that allows the user to update a message board by clicking a button "update" this in turn triggers my Stored Procedure for inserting this data into a table, which works great. It inserts the data into its respective fields and also takes The Title, Line 1, Line 2 and so on and creates a XML file (Using FOR XML) which is stored in the same Table under a column call XML_Data. Which again works great. My problem now is how do i output this XML_Data to an actual XML fi...
Sql and XML returning
I have a problem. I use SQL Server 2000 and FOR XML RAW to return data from a view. The query is: SELECT *FROM dbo.VP_WATATTEDANCEDTLWHERE (EMPLOYEEID = '46109') FOR XML RAW Now if I run this directly against my sql query analyzer it brings back all 14 rows that I need. When I use .Net Query analyzer it brings back 2 rows that say <binary>. If I export those to a file they end up being 2 xml rows and thats it! I have code that uses this but I need all 14 lines to generate the ...
Error occuring wen trying to write to xml file from sql datasource
Hi all I am having a spot of difficulty with the following code, it gives me a InvalidOperation unhandled by user code error: Imports System.Data.Sql Imports System.Data.SqlClient Imports System.Xml.XmlWriter Imports System.XmlPartial Public Class RSStest Inherits System.Web.UI.PageProtected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load Dim cn As SqlConnection = New SqlConnection(ConfigurationManager.ConnectionStrings("ConnectionString").ToString)Dim cm As New SqlCommand ' set the conenction and query string cm.Connection = cn c...
XML or SQL Datasource
I have web forms that include a number of drop down lists many of which are re-used through the app. Would it be more efficient - resources-wise - to use an XML datasource to populate the lists and then simply insert the text attribute into a record - or should I do a select against a table in the database and use an ID as the attribute that I insert into the record that is being saved? For example - a drop down has values Small, Medium, Large. If I use an XML file these attributes are stored as text and when selected in the app, the text value (Small, Medium, Large) is inserted into the [si...
SQL XML
In SQL Server 2000, is it possible to create an xml document in memory within a stored procedure (say, with FOR XML AUTO)? The resulting xml from such a call is not a string, so you can't use it as the input parameter for sp_xml_preparedocument. Any ideas?...
SQL For XML to XML Document Question???
Would someone please give me a checklist, direct me to a url, or maybe some directions on how to do the following? I am trying to pull data from a SQL Server using a for xml statement and place the information properly into an xml document. I can write the information to a document without a problem. Do I need to do something special in the sql statement other than place for xml auto at the end? The problem is that I am not sure what I need to do to structure the xml properly coming out of the database for the xml file. On top of the structure problems, in my xml file, it is not html en...
Hi All I have a problem i am selecting a row of data from a database, within that row is a column in which i build up a xml in a stored procedure, it creates an xml and stores it in the column name xmldata which is of xml datatype. This works great My problem is how do i now create a xml file on my local machine with that data, below is what i have so far :- Dim conn1 As New SqlConnection()conn1.ConnectionString = (ConfigurationManager.ConnectionStrings("ConnectionString").ConnectionString) conn1.Open()Dim DBCmd As New SqlCommand Dim DataRecieved As SqlDataReaderDBCmd...
Getting standard SQL datatypes from XML, i.e. XML->SQL
Hi all, I'm wanting to get standard SQL data types from an XML document in ASE. Looking at the documentation in 'XML Services in Adaptive Server Enterprise', it all seems to be concerned with transforming SQL into XML, or manipluating XML. e.g. how do you get a query like :- select filename, xmlextract("//book/title" , content) from xmlsfsTab to return a 'SQL' result set so the title column does NOT containt XML fragments, i.e. it looks like:- filename title ---------------- -------------------------------- bookstore.1.xml ...
Save SQL XML field type to a xml file
Hi, I have a table which holds raw xml in a field with type XML. How do I export that XML field to an XML file? Thanks Bones If I was helpful, please mark "answered" so I can get credit. Thanks! You can do by using CLR based stored procedure. Here is a link. http://www.sqldbatips.com/showarticle.asp?ID=23 Farooq Kaiser, MCTS, MCPPlease Mark As Answer if my answer helped you.http://www.Fairnet.com Thanks for the help but unfortunitly this is for an other version on .NET. When I try to compile the .DLL I get the errors for "File.WriteAll" and...
Read XML data from SQL Server XML data type field
Hi,does anyone actually use the XML data type for SQL Server database columns? I can't really find any help for my problems.In my database there is a table with personal data such as name, telephone and so one. One of the columns is of type XML, because I need to store multilingual data in there, something like:<career> <careertext lang="en">My career</careertext> <careertext lang="de">Meine Karriere</careertext></career>I'm using a Stored Procedure to retrieve all data from the database and in my data access object th...
Retreive xml data from sql 2005 xml data type column
hi everyone, I am in a messy situation and dieing to get rescued.I have a database with an untyped xml datatype column named xmlfile. The xml inside is invoice of customer & looks like this.<User><INVOICE><BILL Time="May 18 2008 12:47AM">0.1869</BILL><BILL Time="May 18 2008 1:00AM">22.25</BILL><BILL Time="May 18 2008 1:00AM">22.25</BILL></INVOICE></User>I wanted to retrieve the <BILL></BILL> Elements and put it through asp:datagrid or asp:repeater . I w...
How to write to Data to SQL Server XML data type
Hello, I have worked with SQL Server 2000 but now we have a requirement where I need to write values from an asp.net form into an XML type in SQL Server 2005.I have never used XML as a type in SQL Server 2005. How do we write xml into xml type. For example the structure of XML is something like:<application><applicationID = "value"></applicationID><customerName="value></customerName></application>I have to write this kind of XML into the XML type and later retrieve these XML values and populate the form again.Kindly suggest. Thanks a l...
XML Manipulation (Read, Write, Update, Searching XML file like we do in SQL query)
Hi ,I want to make a ASP.NET application which contains XML file as the data container(I am not using any Database like MS SQL Server or MS Access.)I want to enter new data in the xml file through ASP.NET.I also want to search the existing data in xml file (like we search data using select clause in SQL and The XML file is going to contain 1000s of tupples).I want to update a particular (tupple) fields of that XML file using ASP.NET.(Actually I don't want to use data grid for this). Regards,Himanshu Hi himanshuweb2 , Edit XML files using a GridViewhttp://www.codeproj...
Passing an XML variable argument to a SQL Server 2005 Stored Procedure with an XML data type
Is it possible to pass an XML variable to a SQL Server 2005 Stored Procedure with the XML data type as it's receiving argument? I can get the SQL Server Procedure to work perfectly with XML passed to it from another Stored Procedure, but as soon as I define a Stored Procedure from within a datawindow to pass parameters to it, I get "Cannot create DataWindow" and "XML parse error occurred on line number 1, near the XML text "0"". Essentially, I want to use the XML string, built by data keyed by the client within a datawindow, to be loaded to a temp t...
SQL Sever 2005 write XML data stored in a Table out to a XML file on my local machine
Hello All Just wondered if someone could help me with a bit of T-SQL, i have a application in ASP.NET/VB that allows the user to update a message board by clicking a button "update" this in turn triggers my Stored Procedure for inserting this data into a table, which works great. It inserts the data into its respective fields and also takes The Title, Line 1, Line 2 and so on and creates a XML file (Using FOR XML) which is stored in the same Table under a column call XML_Data. Which again works great. My problem now is how do i output this XML_Data to an actual XML fi...
Sql and XML returning
I have a problem. I use SQL Server 2000 and FOR XML RAW to return data from a view. The query is: SELECT *FROM dbo.VP_WATATTEDANCEDTLWHERE (EMPLOYEEID = '46109') FOR XML RAW Now if I run this directly against my sql query analyzer it brings back all 14 rows that I need. When I use .Net Query analyzer it brings back 2 rows that say <binary>. If I export those to a file they end up being 2 xml rows and thats it! I have code that uses this but I need all 14 lines to generate the ...
Error occuring wen trying to write to xml file from sql datasource
Hi all I am having a spot of difficulty with the following code, it gives me a InvalidOperation unhandled by user code error: Imports System.Data.Sql Imports System.Data.SqlClient Imports System.Xml.XmlWriter Imports System.XmlPartial Public Class RSStest Inherits System.Web.UI.PageProtected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load Dim cn As SqlConnection = New SqlConnection(ConfigurationManager.ConnectionStrings("ConnectionString").ToString)Dim cm As New SqlCommand ' set the conenction and query string cm.Connection = cn c...
XML or SQL Datasource
I have web forms that include a number of drop down lists many of which are re-used through the app. Would it be more efficient - resources-wise - to use an XML datasource to populate the lists and then simply insert the text attribute into a record - or should I do a select against a table in the database and use an ID as the attribute that I insert into the record that is being saved? For example - a drop down has values Small, Medium, Large. If I use an XML file these attributes are stored as text and when selected in the app, the text value (Small, Medium, Large) is inserted into the [si...
SQL XML
In SQL Server 2000, is it possible to create an xml document in memory within a stored procedure (say, with FOR XML AUTO)? The resulting xml from such a call is not a string, so you can't use it as the input parameter for sp_xml_preparedocument. Any ideas?...
SQL For XML to XML Document Question???
Would someone please give me a checklist, direct me to a url, or maybe some directions on how to do the following? I am trying to pull data from a SQL Server using a for xml statement and place the information properly into an xml document. I can write the information to a document without a problem. Do I need to do something special in the sql statement other than place for xml auto at the end? The problem is that I am not sure what I need to do to structure the xml properly coming out of the database for the xml file. On top of the structure problems, in my xml file, it is not html en...
Web resources about - Return XML to response.write from SQL XML type - asp.net.sql-datasource
Wikipedia:Abuse response - Wikipedia, the free encyclopediaFor a list of current Abuse Response volunteers, or for information on how you can help with the Abuse Response project, please see the volunteers ...
George Pell’s Melbourne Response child sexual abuse programTHE victims of child sexual abuse within the Catholic Church just want to be heard.
NSW hospitals: Ambulance response times slow as overtime bill increasesAn Auditor-General's report on NSW hospitals shows that ambulance times have slowed.
Climate aid is a poor response to global challengesMoney being earmarked for climate aid would improve the lives of more people if spent on health and education programs.
Trashed by Trump, Amazon CEO has out-of-this-world response
Jeff Bezos, who also owns commercial space flight company, responds to GOP candidate's Twitter barrage
Jeff Bezos, who also owns commercial space flight company, responds to GOP candidate's Twitter barrage
Chipotle grilled for crisis response
Crisis PR professionals have some harsh words for Chipotle for its handling of outbreaks of E. coli and norovirus.
Crisis PR professionals have some harsh words for Chipotle for its handling of outbreaks of E. coli and norovirus.
Defending Our Homeland: The New Threat And Response Paradigm
Second installment of a 10 part series: Enough of mourning, revulsion, and horror; let’s confront these predators, and win.
Second installment of a 10 part series: Enough of mourning, revulsion, and horror; let’s confront these predators, and win.
Where's the official Republican Party response to Trump's Muslim ban plan?
... "Heil Donald Trump - THE ULTIMATE SAVIOR." That's what Andrew Anglin, publisher of the neo-Nazi website Daily Stormer, had to say in response ...
... "Heil Donald Trump - THE ULTIMATE SAVIOR." That's what Andrew Anglin, publisher of the neo-Nazi website Daily Stormer, had to say in response ...
Adriana Lima Shuts Down Justin Bieber Hookup Rumors With Epic Response
Adriana Lima Shuts Down Justin Bieber Hookup Rumors With Epic Response
Adriana Lima Shuts Down Justin Bieber Hookup Rumors With Epic Response
The Latest: White House candidates clash over US terrorism responseHere's the latest news on the U.S. presidential candidates' remarks on how to respond and prevent attacks like the one that occurred last week ...
Resources last updated: 12/9/2015 6:58:22 PM