Reports created with SQL Server, XML, and XSLT 2

[suicidaltendencies]

I'd like to pull data from my SQL Server into a DataSet and retrieve the XML data from it. Then use XSLT to transform the XML. Does any have any clues on this exact situation?

Thanks,
Harold

 

[Joulius]

Here's a sample how you can retrieve data from SQL database and save it to a XML file thru a dataset object.

Dim oCon As SqlConnection = New SqlConnection("... your connection string ...")
Dim daCmd As SqlDataAdapter = New SqlDataAdapter("... your select command ...", oCon)
Dim dsTest As DataSet = New DataSet
Dim theError As String
Try
 oCon.Open()
 daCmd.Fill(dsTest, "Test")
 Dim doc As XMLDataDocument = New XMLDataDocument(dsTest)
 Dim writer As XMLTextWriter = New XMLTextWriter("C:\inetpub\[URL unfurl="true"]wwwroot\MyDocs\test.xml",[/URL] nothing)
 writer.Formatting = Formatting.Indented
 doc.Save(writer)
 writer.Close()
Catch ex As Exception
 theError=ex.Message()
Finally
 oCon.Close()
End Try

I hope this helps!

 

[suicidaltendencies]

Is there a way of doing this more dynamic. Instead of filling an xml file then running a xslt on it, can you get xml from the dataset and run a xslt on it from there?


SQL Server -> Get Data -> Fill DataSet -> Get XML -> Transform XML with XSLT????????

Thanks,
Harold

 

[theoxyde]

Assuming you have an XSLT control (Xml webform control) on you page called Xml1, add the following code to your Page_Load event:

Xml1.DocumentContent = dsTest.GetXml();

Just don't define a document source for it at design time.

 

[theoxyde]

That also assumes you have the dataset filled as well.