| Use an ADO Stream to apply XSLT to SQL Server 2000 XML output In a previous tip, we showed you how to use ADO's Stream object to retrieve binary XML output generated by SQL Server. Given this XML output, you may think to use VB's MSXML parser to transform the XML document via an XSLT stylesheet. Doing so, however, is really an unnecessary step-not to mention an unnecessary use of system resources. That's because SQLOLEDB provides a Command property, called XSL, that will apply the XSLT stylesheet for you. To use this property, you access a Command object's Properties collection, indicate the XSL property and assign the path to the particular XSLT stylesheet you want to use, like so: cmd.Properties("XSL").Value = App.Path & "\myTransformation.xslt" When you set this property, SQLOLEDB automatically applies the stylesheet to the SQL Server-generated XML. As a result, the Stream's output contains the transformed XML or HTML or XHTML, not the original XML. The following code shows a more complete example. Dim cmd As ADODB.Command Dim strm As ADODB.Stream Const READBYTES = 131072 Set strm = New ADODB.Stream With strm .LineSeparator = adCRLF .Mode = adModeRead .Type = adTypeText .Open End With Set cmd = New ADODB.Command With cmd .ActiveConnection = "YOUR CONNECTION STRING" .CommandText = "sp_Generate_XML" .CommandType = adCmdStoredProc .Properties("Output Stream").Value = strm .Properties("Output Encoding").Value = "UTF-16" .Properties("XML Root").Value = XMLROOT .Properties("XSL").Value = App.Path & "\Transform.xslt" .Execute Options:ExecuteStream End With Debug.Print strm.ReadText(adReadAll) strm.Close Set strm = Nothing Set cmd = Nothing |
Use an ADO Stream to apply XSLT to SQL Server 2000 |
India web developer web development India | Freelance web development ecommerce web developer | Prayagasoft - web designer India, Ecommerce developer india, Ecommerce design