Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

SQL XML to XSL document in VB

Posted on 2010-09-17
3
269 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

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

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 …
If you're writing a .NET application to connect to an Access .mdb database and use pre-existing queries that require parameters, you've come to the right place! Let's say the pre-existing query(qryCust) in Access takes a Date as a parameter and l…
This video shows how to use Hyena, from SystemTools Software, to bulk import 100 user accounts from an external text file. View in 1080p for best video quality.
Email security requires an ever evolving service that stays up to date with counter-evolving threats. The Email Laundry perform Research and Development to ensure their email security service evolves faster than cyber criminals. We apply our Threat…

809 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