Solved

SQL XML to XSL document in VB

Posted on 2010-09-17
3
270 Views
Last Modified: 2012-05-10
Hi All,

I am using this sp in sQL to produce an XML document:

SELECT  contract_id , Customer_id
FROM contracts
ORDER BY contract_id
FOR XML AUTO

Using VB.NET 2005 how can i get this result set and transform it into an XSL document using VB?
I haven't written in VB in years so please provide all the code (include declarations, general database connection, etc...)

Thanks a Million
MTD
0
Comment
Question by:MoreThanDoubled
  • 2
3 Comments
 
LVL 59

Accepted Solution

by:
Kevin Cross earned 500 total points
ID: 33701965
MoreThanDoubled,

Since you are already generating the XML string using SQL code, this issue is really just a simple creating of XML in .NET from a string; therefore, one solution is to use System.Xml.XmlDocument and its LoadXml() method to read the string into XML.

http://www.vbdotnetheaven.com/UploadFile/bulentozkir/XMLfileUsingXmlDocument04232005010454AM/XMLfileUsingXmlDocument.aspx

Here is one example of using XslTransform; however, note it is shown with XML and XSL both being files, so to combine with above you could write the XmlDocument generated to a file first.

http://support.microsoft.com/kb/300929/EN-US/

These were just handy articles on code already written, but I will try to work up another example.

Best regards,
Kevin
0
 
LVL 59

Assisted Solution

by:Kevin Cross
Kevin Cross earned 500 total points
ID: 33702647
Here is an example from database read through XML output.
' http://www.connectionstrings.com/
Const strConn As String = "Data Source=localhost;Initial Catalog={{your_database}};User Id={{user}};Password={{password}};"

' Imports System.Text
' Note addition of root element to make a valid XML document
Dim sql As New StringBuilder()
sql.AppendLine("SELECT  contract_id , Customer_id")
sql.AppendLine("FROM Contracts AS Contract")
sql.AppendLine("ORDER BY contract_id")
sql.AppendLine("FOR XML AUTO, ROOT('Contracts')")

' Imports System.Data.SqlClient
Dim conn As New SqlConnection(strConn)
Dim cmd As New SqlCommand(sql.ToString(), conn)

' Open connection first
conn.Open()

' Imports System.Xml
Dim xmlIn As Xml.XmlReader = cmd.ExecuteXmlReader
Dim xmlOut As New Xml.XmlTextWriter("c:\temp\Contracts.xml", Encoding.UTF8)

' Imports System.Xml.Xsl
' XslTransform is deprecated in newer .NET
Dim xsl As New XslCompiledTransform()
xsl.Load("c:\temp\Stylesheet.xsl")
xsl.Transform(xmlIn, xmlOut)

' ...

' Close your connection and cleanup other references
conn.Close()

Open in new window

Stylesheet.xsl
Contracts.xml
0
 

Author Closing Comment

by:MoreThanDoubled
ID: 33753798
Thanks!!
0

Featured Post

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Visual Studios 1 89
VB.Net - String Manipulation Scenario 2 29
Need help converting bitmap to image in VB.Net 8 45
vb.net datagrid point 4 21
Well, all of us have seen the multiple EXCEL.EXE's in task manager that won't die even if you call the .close, .dispose methods. Try this method to kill any excels in memory. You can copy the kill function to create a check function and replace the …
It’s quite interesting for me as I worked with Excel using vb.net for some time. Here are some topics which I know want to share with others whom this might help. First of all if you are working with Excel then you need to Download the Following …
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…
In an interesting question (https://www.experts-exchange.com/questions/29008360/) here at Experts Exchange, a member asked how to split a single image into multiple images. The primary usage for this is to place many photographs on a flatbed scanner…

756 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question