Solved

SQL XML to XSL document in VB

Posted on 2010-09-17
3
259 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
Comment Utility
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
Comment Utility
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
Comment Utility
Thanks!!
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

A while ago, I was working on a Windows Forms application and I needed a special label control with reflection (glass) effect to show some titles in a stylish way. I've always enjoyed working with graphics, but it's never too clever to re-invent …
1.0 - Introduction Converting Visual Basic 6.0 (VB6) to Visual Basic 2008+ (VB.NET). If ever there was a subject full of murkiness and bad decisions, it is this one!   The first problem seems to be that people considering this task of converting…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're looking for how to monitor bandwidth using netflow or packet s…

743 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now