CODEWITHSUNDEEP
phpxmlsql-server-2008
Mark
Mark

Reputation: 65

How to read XML data from SQL Server into PHP

I am trying to convert a number of SQL Server FOR XML queries and XSL Templates, from a legacy ASP application to PHP.
Basically the app assembles an XML document from several queries then uses the XSL template to output HTML.

I have being reading this blog post:

I am using sqlsrv_query() followed by sqlsrv_fetch() and sqlsrv_get_field() to get the data. The result set is returned by default as an XML stream.

I can't figure out how to read the stream into a DOMDocument object. I guess I can convert the output to a string, then parse the string into the object, but how do you read the XML stream directly?

Upvotes: 2

Views: 3349

Answers (1)

hakre
hakre

Reputation: 197732

If you are fetching the data by hinting the PHP-data-type as shown in the blog-post you've linked:

$xml = sqlsrv_get_field($stmt, 0, SQLSRV_PHPTYPE_STRING('UTF-8'));

Then the variable $xml contains an UTF-8 encoded string. Strings in PHP are always binary and UTF-8 is the default encoding of an XML document. This is the same in PHP: DOMDocument in PHP expects all input strings to be UTF-8 encoded (as outlined in the IntroductionDocs).

All you need to do is to load the XML as stringDocs:

$doc   =  new DOMDocument();
$result = $doc->loadXML($xml);

You can then verify if this works by checking the return value and outputting the XML as string from the document:

var_dump($result);          # should be (boolean) TRUE
$doc->save('php://output'); # should be your XML verbatim

this should do it, let me know if you use a different way to fetch from the database. Because then this might work differently, e.g. if you are getting a PHP StreamDocs you might want to use stream_get_contents()Docs to convert the stream into a string first. You can not load streams with DOMDocument in PHP, this is why you need to convert it first to a string.

See as well:

Upvotes: 3

Related Questions