I'd like to use SQL Server's XML capabilties from PHP. I'm aware of COM support in PHP, but I cannot figure out how to get XML to stream into PHP.

I'd like to do:

  • Connect to Northwind DB in SQL Server
  • Issue "select * from Customers for xml auto"
  • retrieve XML string

It seems simple but I'm stumped. ADO?

Daniel

    I had the same difficulties some time ago with an ASP page, the samples from MSDN did not work, the MSXML2.DOMDocument would not load the xml returned by the query...

    I finaly came with this code that seems to work (sorry, it's in VBScript, but can be easily translated in PHP...)

    Function execXMLQuery(strRoot,strSQL)

	dim objXML
	set objXML = newXMLDOM

	dim objCmd
	set objCmd = Server.CreateObject("ADODB.Command")
	objCmd.ActiveConnection=objADO
	objCmd.CommandText=strSQL&" FOR XML AUTO"

	dim objStream
	set objStream = Server.CreateObject("ADODB.Stream")
	objStream.Open
	objCmd.Properties("Output Stream")=objStream
	objCmd.Execute ,,&H400

	if strRoot="" then
		objXML.loadXML(objStream.ReadText(-1))
	else
		objXML.loadXML("<"&strRoot&">"&objStream.ReadText(-1)&"</"&strRoot&">")
	end if
	set objStream = Nothing
	set objCmd = Nothing
	set execXMLQuery = objXML

End Function

    (objADO is a global variable, a "ADODB.Connection" connected to the database)

    strRoot is the name of the root xml node you want... because SQL is really dumb: if your query fetch multiple rows, the xml returns as multiple root nodes (!!!)... you need to add a dummy root <FOO>..</FOO> to be able to load it...

    got no time to check with a PHP version, hope that helps anyway...

      Write a Reply...