Help with SQl Server and XML?

Question

I issued a query request to sql server with "FOR XML AUTO, type, elements, root('root')" appended to the end of my query. I issue a da.Fill(ds) [to a datset] which works without any problems. Now that I have the data, I want to transform it into HTML.

So I used:

 Dim XMLString As String = ds.GetXml() 

that converts my data into a string. Here's the satrt of my PROBLEM!!

Then I go:

strHTMLDetails = GetHtml(Server.MapPath("~/TEO.xslt"), XMLString)

which passes the xsl (within my project) and the xmlstring to a sub.

Then my code is as follows:

Dim stream As New MemoryStream(ASCIIEncoding.Default.GetBytes(XMLString)
Dim document As New XPathDocument(stream)
Dim writer As New StringWriter()
transform.Load(xsltPath)
transform.Transform(document, Nothing, writer)     <======  Here's my error:

White space cannot be stripped from input documents that have already been loaded. Provide the input document as an XmlReader instead.

I don't understand what's wrong or how to resolve it. Please help?

Thank you, hlpearl

Answer 1

Does TEO.xslt use xsl:strip-space anywhere? If so that is probably your problem.

You can read why here: http://msdn.microsoft.com/en-us/library/ms163434.aspx

Answer 2

Yes I had "

When I remove it, I don't have the above problem, but I also don't get any transformation.

When I try this in my batch routione which is as follows: Dim XMLString as string = ds.GetXML() Dim xsl1 as new XSLCOmpliledTransform() xsl1.Load(Ctype(ServerMapPath("~/TEO.xslt, String) <= this is my project xslt xsl1.Transform(XMLString, "C:....\testteo.HTML) <= Error

I get a Invalid URI: The Uri scheme is too long.

My dataset dat looks like this: .root..t1..TEO_SEQ_NO.1./TEO_SEQ_NO. this appears to be the correct data. Thanks, Hlpearl