Solved

SQL XML to XSL document in VB

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

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

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

Suggested Solutions

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 …
Parsing a CSV file is a task that we are confronted with regularly, and although there are a vast number of means to do this, as a newbie, the field can be confusing and the tools can seem complex. A simple solution to parsing a customized CSV fi…
This Micro Tutorial will give you a basic overview how to record your screen with Microsoft Expression Encoder. This program is still free and open for the public to download. This will be demonstrated using Microsoft Expression Encoder 4.
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…

803 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