Re: Viewing xml returned from stored procedures

Tech-Archive recommends: Speed Up your PC by fixing your registry

Is there a way to stream the data directly to a file rather than caching in
the XML document first? The data volumes could be quite large.

--
McGeeky
http://mcgeeky.blogspot.com


"Graeme Malcolm" <graemem_cm@xxxxxxxxxxx> wrote in message
news:upvZCFBTFHA.2124@xxxxxxxxxxxxxxxxxxxxxxx
> Here's a really quick and dirty VB script that retrieves the data from the
> sproc, saves it as a file, and displays it in IE. I'm sure there are more
> elegant solutions out there, but this might get you started.
>
> Just enter the stored procedure name as <db_name>.<owner>.<sproc_name>
> (e.g.
> northwind.dbo.getproductsxml)
>
> Const DBGUID_SQL = "{C8B522D7-5CF3-11CE-ADE5-00AA0044773D}"
> Const adExecuteStream = 1024
> Const adReadAll = -1
> Const adCmdStoredProc = 4
>
> GetData
> DisplayResults
>
> Function GetData()
> Dim conn
> Set conn = CreateObject("ADODB.Connection")
> conn.Provider = "SQLXMLOLEDB.3.0"
> conn.ConnectionString = "DATA PROVIDER=SQLOLEDB;" & _
> "SERVER=(local);DATABASE=northwind;INTEGRATED SECURITY=sspi;"
> conn.Open
> Dim cmd
> Set cmd = CreateObject("ADODB.Command")
> Set cmd.ActiveConnection = conn
> 'Set the dialect
> cmd.Dialect = DBGUID_SQL
> Dim xmlDoc
> Set xmlDoc= CreateObject("MSXML.DOMDocument")
>
> cmd.Properties("xml root") = "QueryResults"
> cmd.Properties("Output Stream") = xmlDoc
> 'Specify the command
> cmd.CommandText = InputBox ("Enter Stored procedure Name")
> cmd.CommandType = adCmdStoredProc
> 'Execute the command returning a stream
> cmd.Execute, , adExecuteStream
> xmlDoc.Save "results.xml"
> End Function
>
> Function GetAppPath()
> Dim strScriptPath
> Dim strScriptName
>
> strScriptPath=WScript.ScriptFullName
> strScriptName=WScript.ScriptName
> GetAppPath = Left(strScriptPath, Len(strScriptPath)-Len(strScriptName))
> End Function
>
> Function DisplayResults()
> Dim wshshell
> set Wshshell= WScript.createobject("wscript.shell")
> Wshshell.run "iexplore.exe File://" & GetAppPath & "results.xml"
> End Function
>
> --
> ----
> Graeme Malcolm
> Principal Technologist
> Content Master Ltd.
> www.contentmaster.com
>
>
> "McGeeky" <anon@xxxxxxxx> wrote in message
> news:%23%23KNEw9SFHA.2840@xxxxxxxxxxxxxxxxxxxxxxx
> Does anyone know of a utility that makes the viewing of xml data returned
> by
> stored procedures easier? When using query analyzer the xml data is
> returned
> as a single row. Its very hard to see the data!
>
> What would be nice is a command line tool that can run a query and format
> the xml that is returned. Or better still, a gui equivalent.
>
> Ideas?
>
> --
> McGeeky
> http://mcgeeky.blogspot.com
>
>
>
>


.

Relevant Pages

  • Re: For XML Problem with IIS6 and W2k3
    ... Can you run the FOR XML query directly on the database? ... > dim adoConn ... > dialect for the command stream to be a SQL query. ...
    (microsoft.public.sqlserver.xml)
  • Re: Xml error: Namespace Manager or Xslt context needed
    ... "XML newbie: Urgent pls help!" ... ' Instantiate an XmlDocument object to avoid NullReference ... Dim xmlDoc As XmlDocument ... Dim strm As Stream = HttpWResponse.GetResponseStream ...
    (microsoft.public.dotnet.xml)
  • RE: using myXslDoc.Transform
    ... and xml file ... Dim strXML As String = SqlHelper.ExecuteScalar(cn, ... ' get xsl file from database and load xsl file ...
    (microsoft.public.dotnet.xml)
  • RE: ASP.Net app cannot read XML with Anonymous Authentication disabled
    ... ASP.Net app cannot read XML with Anonymous Authentication ... | Dim ProjReader As XmlReader = Nothing ... | Dim ProjURL As String = Session ... | 'Get XML Data for Projector and load in Projector Dropdown ...
    (microsoft.public.dotnet.framework.aspnet.security)
  • RE: using myXslDoc.Transform
    ... 'get xml file from the database and load xml file ... Dim strXML As String = SqlHelper.ExecuteScalar(cn, ... the transform can be applied as usual. ...
    (microsoft.public.dotnet.xml)