XML from MSSQL

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
phpMSQL
Forum Newbie
Posts: 7
Joined: Mon Jun 04, 2007 9:55 am

XML from MSSQL

Post by phpMSQL »

Is there any reason why only part of an xml export from MSSQL would make it back to my php? In Enteprise Manager it returns a fill xml dataset, but i only receive 2033 chars in php.

Is there any reason why I can return a large blob of text, but I cannot retrun an xml string?
Does PHP somehow think that the dataset is a varchar, and trim it to 2033?
I have tried to trick ou the stor proc to export a text, but all you can do apparently with XML in MsSQL is export it immediately.

I have been wrestling with this for a while.

Any help would save me from going berzerk. This is so much more efficient then taking a record set into PHP and creating an XML document, so I really want it to work.
User avatar
volka
DevNet Evangelist
Posts: 8391
Joined: Tue May 07, 2002 9:48 am
Location: Berlin, ger

Post by volka »

How does the script access the data?
phpMSQL
Forum Newbie
Posts: 7
Joined: Mon Jun 04, 2007 9:55 am

Post by phpMSQL »

it does it through odbc drivers. I am sure it is my code, because I used someoneelses function, and it seemed to help. It is stifling.

when I do it this way

Code: Select all

public function getUserPermissions($userID, $siteID){
		$oDBTB = new dbToolBoxV2(true);
		$oDBTB->sql = "spAdmin_AuthenticationLuPermission_Select $userID, $siteID";
		
		$rs = $oDBTB->executeSQL();
		if($rs != 0){
			$row = odbc_fetch_row($rs);
                                                $this->permXML = odbc_result($row, 1);

			return 0;
		}else{
			return 1;
		}
	}
no matter what I only get 2033 chars. I have tried to set permXML as a DomDocument, but that doesn't work.

Then I found some code that takes a record set and uses DomDocument to create an XML string, and using pretty much the same stuff, I get the full record set. But I do a lot for a one row, one cell rs.

Code: Select all

public function getUserPermissions($userID, $siteID){
	$oDBTB = new dbToolBoxV2(true);
	$oDBTB->sql = "spAdmin_AuthenticationLuPermission_Select $userID, $siteID";
		$rs = $oDBTB->executeSQL();
		if($rs != 0){			
			$oXMLTools = new xmlTools($rs, "1.0");
			$oXMLTools->setRootNodeName("Permissions");
			$oXMLTools->setRecordRootNodeName("permission");
			$this->permXML->loadXML = $oXMLTools->createXML_FromRecordSet();

			return 0;
		}else{
			return 1;
		}
	}

Code: Select all

public function createXML_FromRecordSet(){
		$this->xml = new DomDocument($this->version);//object referance for the new document 
		//Adding our root node
		$root = $this->xml->createElement($this->rootNodeName);
		$root = $this->xml->appendChild($root);
		
		while ( $object = odbc_fetch_row($this->rs) ){
			//Add a node for each row
			$nodeForEachRow = $this->xml->createElement($this->recordNodeName);
			$nodeForEachRow = $root->appendChild($nodeForEachRow);
			
			for($i = 0; $i < $this->numFields; $i++){
				//Add node for each column
				$child = $this->xml->createElement(odbc_field_name($this->rs, $i + 1));
				$child = $nodeForEachRow->appendChild($child);
				//Add values as a text node (inserting values!)
				$value = $this->xml->createTextNode(odbc_result($this->rs, $i + 1));
				$value = $child->appendChild($value);
			}
		}
		$this->xmlString = $this->xml->saveXML();//returns the completed XML document as a string.
		return $this->xmlString;
	}
I am not familiar ith DomDoc, and there isn't much documentation. Just keeps getting more fun.
Post Reply