?
Solved

SQL XML to XSL document in VB

Posted on 2010-09-17
3
Medium Priority
?
286 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 60

Accepted Solution

by:
Kevin Cross earned 2000 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 60

Assisted Solution

by:Kevin Cross
Kevin Cross earned 2000 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

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

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

Microsoft Reports are based on a report definition, which is an XML file that describes data and layout for the report, with a different extension. You can create a client-side report definition language (*.rdlc) file with Visual Studio, and build g…
It was really hard time for me to get the understanding of Delegates in C#. I went through many websites and articles but I found them very clumsy. After going through those sites, I noted down the points in a easy way so here I am sharing that unde…
In a question here at Experts Exchange (https://www.experts-exchange.com/questions/29062564/Adobe-acrobat-reader-DC.html), a member asked how to create a signature in Adobe Acrobat Reader DC (the free Reader product, not the paid, full Acrobat produ…
Is your OST file inaccessible, Need to transfer OST file from one computer to another? Want to convert OST file to PST? If the answer to any of the above question is yes, then look no further. With the help of Stellar OST to PST Converter, you can e…

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