Consuming SQL Server Result Sets as XML with PHP

Tuesday, July 26, 2011

At last month’s SQL Server JumpIn! Camp, one of the SQL Server features that generated some excitement among the participants was SQL Server’s ability to return result sets as XML. I felt a bit foolish for not having investigated this feature, so I’m rectifying that with this post. The SQL Server XML functionality is very rich (just look at the MSDN documentation devoted to it!), but I will provide only an introduction in this post. As you will see, nothing special is required in your PHP code…the magic is all in the SQL queries.

Returning Result Sets as XML

Retuning a result set as XML is as simple as adding FOR XML (followed by one of the following modes: AUTO, RAW, EXPLICIT, or PATH) to the end of a query. So, for example, this query will return XML in the AUTO mode (more on what the different modes do in the Examples section below):

SELECT ProductID, ProductName, UnitPrice FROM Products FOR XML AUTO

The various modes for returning XML (AUTO, RAW, EXPLICIT, and PATH) give you (to varying degrees) control over the format of the returned XML. That is, of course where things get interesting. (As I said, more on that below.) What’s nice is that all the magic is in the SQL query - consuming it with PHP is easy…

Consuming XML Result Sets with PHP

Once your have defined a query like the one above, you can execute it and retrieve the results with the SQL Server Driver for PHP in almost the same way you would retrieve any other result set. There is one “gotcha”: In addition to adding FOR XML {AUTO | RAW | EXPLICIT | PATH} to your query (as above), you must also add the TYPE directive (see the example below). Adding this directive instructs SQL Server to return the result as an XML type. By default, the SQL Server Driver for PHP retrieves XML types as PHP streams. So, in the code example below, you can consume the $xml variable with your favorite PHP stream function.

$server = "serverName\sqlexpress";

$options = array("UID"=>"user", "PWD"=>"password", "Database"=>"Northwind");

$conn = sqlsrv_connect($server, $options);

$sql = "SELECT ProductID, ProductName, UnitPrice 

        FROM Products 

        FOR XML AUTO, TYPE";

$stmt = sqlsrv_query($conn, $sql);

sqlsrv_fetch($stmt);

$xml = sqlsrv_get_field($stmt, 0);

If you would rather retrieve the data as a UTF-8 encoded string, simply change the last line of the example above to this:

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

Now you are set to manipulate the result with your favorite PHP string function.

Notice that I’m consuming the result using the combination of sqlsrv_fetch and sqlsrv_get_field. You can’t use sqlsrv_fetch_array, but doing so wouldn’t make sense anyway.

That’s all there is to retrieving a result set as XML with PHP. But, as I mentioned earlier, controlling the format of the returned XML is the interesting part. Let’s look at some examples that show a bit of what’s possible…

Examples

The examples that follow are intended to give you some idea of how you can format SQL Server result sets as XML, but they really only scratch the surface of what’s possible. (If you want to dig deeper, check out the MSDN documentation.) I’ve tried to pick examples that I think will be of interest to most people – if I’ve missed something that is of interest to you, please let me know and I’ll try to produce a helpful example.

One more note before diving in: The AUTO, RAW, EXPLICIT and PATH modes give you varying degrees of control over the format of the returned XML. The ends of the control spectrum are AUTO (least control, but simplest) and PATH (most control, but complex). I’ll provide some examples using each of these modes and leave the other modes as “exercises for the reader”. There are lots of examples on MSDN here: AUTO, RAW, EXPLICIT, PATH.

Simple Query Example

For starters, let’s try the query in the code example above:

AUTO Mode

SELECT ProductID, ProductName, UnitPrice FROM Products FOR XML AUTO, TYPE

This query returns a result set that looks like this:

<Products ProductID="1" ProductName="Chai" UnitPrice="18.0000"/>

<Products ProductID="2" ProductName="Chang" UnitPrice="19.0000"/>

Notice that element names correspond to the table and attribute names correspond to columns. The same query in PATH mode…

PATH Mode

SELECT ProductID, ProductName, UnitPrice FROM Products FOR XML PATH, TYPE

…returns a structure in which columns correspond to elements (not attributes):

<row>

    <ProductID>1</ProductID>

    <ProductName>Chai</ProductName>

    <UnitPrice>18.0000</UnitPrice>

</row>

<row>

    <ProductID>2</ProductID>

    <ProductName>Chang</ProductName>

    <UnitPrice>19.0000</UnitPrice>

</row>

You can get the same structure as above in AUTO mode by specifying the ELEMENTS option:

AUTO Mode

SELECT ProductID, ProductName, UnitPrice FROM Products FOR XML AUTO, TYPE, ELEMENTS

And, it is possible to get columns returned as attributes in PATH mode (more on that in the Specify Attributes section below).

Add a Root Element

In either AUTO or PATH mode you can add a root element by adding ROOT(‘rootname’) to your query. Here’s what a query in PATH mode looks like:

PATH Mode

SELECT ProductID, ProductName, UnitPrice 

FROM Products 

FOR XML PATH, TYPE, ROOT('Products')

The output in either AUTO or PATH mode simply encapsulates the output we saw above in a root element. Here’s the output for the query above:

<Products>

  <row>

    <ProductID>1</ProductID>

    <ProductName>Chai</ProductName>

    <UnitPrice>18.0000</UnitPrice>

  </row>

  <row>

    <ProductID>2</ProductID>

    <ProductName>Chang</ProductName>

    <UnitPrice>19.0000</UnitPrice>

  </row>

</Products>

But what about that <row> element? How can we change it?

Change Element Names

To replace the <row> elements in the output above we can specify the element name like this:

PATH Mode

SELECT ProductID, ProductName, UnitPrice 

FROM Products 

FOR XML PATH ('Product'), TYPE, ROOT('Products')

The output now looks like this…

<Products>

  <Product>

    <ProductID>1</ProductID>

    <ProductName>Chai</ProductName>

    <UnitPrice>18.0000</UnitPrice>

  </Product>

  <Product>

    <ProductID>2</ProductID>

    <ProductName>Chang</ProductName>

    <UnitPrice>19.0000</UnitPrice>

  </Product>

</Products>

However, note that the same syntax doesn’t work in AUTO mode. In general (and this works in AUTO mode), if you want to change element names, uses aliases. For example, this query…

PATH Mode

SELECT ProductID as PID, ProductName as PN, UnitPrice as UP

FROM Products 

FOR XML PATH ('Product'), TYPE, ROOT('Products')

…produces this output…

<Products>

  <Product>

    <PID>1</PID>

    <PN>Chai</PN>

    <UP>18.0000</UP>

  </Product>

  <Product>

    <PID>2</PID>

    <PN>Chang</PN>

    <UP>19.0000</UP>

  </Product>

</Products>

That is all great, but what if you want some of those children of <Product> to be attributes instead?

Specify Attributes

Suppose that, in the example above, you want ProductID to be an attribute (not a child) of the <Product>element. When you are using PATH mode, this is easy. (As I said earlier, AUTO mode doesn’t give you much flexibility.) Here’s the query:

PATH Mode

SELECT ProductID AS '@pid', ProductName, UnitPrice

FROM Products 

FOR XML PATH ('Product'), TYPE, ROOT('Products')

Note that I specified the alias in quotes and prepended it with an @ symbol. Here’s the output:

<Products>

  <Product pid="1">

    <ProductName>Chai</ProductName>

    <UnitPrice>18.0000</UnitPrice>

  </Product>

  <Product pid="2">

    <ProductName>Chang</ProductName>

    <UnitPrice>19.0000</UnitPrice>

  </Product>

</Products>

If you want more than one element to be specified as an attribute, give it a similar alias in the query. One catch here: If I wanted ProductName to be an attribute instead of ProductID, I’d need to change the order of the elements in my query (i.e. all columns that you want to be specified as attributes must come before those that will be columns).

Add a Namespace

In the examples above, I have not explicitly included a namespace. Fortunately, doing so is straightforward (note the WITH XMLNAMESPACES clause in this query):

PATH Mode

WITH XMLNAMESPACES('a' AS b) SELECT ProductID AS '@pid', ProductName, UnitPrice

FROM Products 

FOR XML PATH ('Product'), TYPE, ROOT('Products')

This is the output:

<Products xmlns:b="a">

  <Product pid="1">

    <ProductName>Chai</ProductName>

    <UnitPrice>18.0000</UnitPrice>

  </Product>

  <Product pid="2">

    <ProductName>Chang</ProductName>

    <UnitPrice>19.0000</UnitPrice>

</Product>

</Products>

The WITH XMLNAMESPACES works in AUTO mode too.

Include an In-Line XSD

It is also possible to include an in-line XSD by adding the XMLSCHEMA option to your query. This option is not supported in PATH mode, so if the restrictions of AUTO mode are to great for your needs AND you want to use this option, I suggest looking at the RAW mode. Here’s what a query in AUTO mode looks like:

AUTO Mode

SELECT ProductID, ProductName, UnitPrice

FROM Products 

FOR XML AUTO, TYPE, ROOT('Products'), XMLSCHEMA

And here’s what the output looks like:

<Products>

  <xsd:schema xmlns:schema="urn:schemas-microsoft-com:sql:SqlRowSet1" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:sqltypes="http://schemas.microsoft.com/sqlserver/2004/sqltypes" targetNamespace="urn:schemas-microsoft-com:sql:SqlRowSet1" elementFormDefault="qualified">

  <xsd:import namespace="http://schemas.microsoft.com/sqlserver/2004/sqltypes" schemaLocation="http://schemas.microsoft.com/sqlserver/2004/sqltypes/sqltypes.xsd"/>

  <xsd:element name="Products">

    <xsd:complexType>

     <xsd:attribute name="ProductID" type="sqltypes:int" use="required"/>

     <xsd:attribute name="ProductName" use="required">

      <xsd:simpleType>

       <xsd:restriction base="sqltypes:nvarchar" sqltypes:localeId="1033" sqltypes:sqlCompareOptions="IgnoreCase IgnoreKanaType IgnoreWidth" sqltypes:sqlSortId="52">

        <xsd:maxLength value="40"/>

       </xsd:restriction>

     </xsd:simpleType>

    </xsd:attribute>

    <xsd:attribute name="UnitPrice" type="sqltypes:money"/>

   </xsd:complexType>

  </xsd:element>

</xsd:schema>

<Products xmlns="urn:schemas-microsoft-com:sql:SqlRowSet1" ProductID="1" ProductName="Chai" UnitPrice="18.0000"/>

<Products xmlns="urn:schemas-microsoft-com:sql:SqlRowSet1" ProductID="2" ProductName="Chang" UnitPrice="19.0000"/>

</Products>

Note how column data are included as restrictions. Also note that you can include your own schema URL by including XMLSCHEMA(‘your_URL’) in the query.

How are NULL Columns Handled?

Lastly (with simple queries), let’s look at what happens when a row value is NULL. By default, an attribute or element will be omitted from the XML output if its value is NULL. However, if you want NULL values to appear in the XML output, just include ELEMENTS XSINIL option in your query (note that I’m changing my example query so I could get a NULL-valued column):

PATH Mode

SELECT CompanyName, Region

FROM Customers 

WHERE CustomerID = 'ALFKI'

FOR XML PATH ('Customer'), TYPE, ELEMENTS XSINIL, ROOT('Customers')

Here’s the result:

<Customers xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">

  <Customer>

   <CompanyName>Alfreds Futterkiste</CompanyName>

   <Region xsi:nil="true"/>

  </Customer>

</Customers>

A More Complex Query Example

All of the notes about simple queries (above) apply to more complex queries, but I think it’s worth a quick look at a slightly more complex query to see how the structure of the returned XML is affected.

Consider the following query:

AUTO Mode

SELECT p.ProductName, od.ProductID, c.CategoryName

FROM Products p 

INNER JOIN [Order Details] od 

ON p.ProductID = od.ProductID 

INNER JOIN Categories c 

ON c.CategoryID = p.CategoryID 

GROUP BY od.ProductID, p.ProductName, c.CategoryName 

FOR XML AUTO, TYPE, ROOT('Products')

The key thing to note is that the returned XML has nested elements for each table in the query (with data as attributes):

<Products>

  <p ProductName="Chai">

    <od ProductID="1">

      <c CategoryName="Beverages"/>

    </od>

  </p>

  <p ProductName="Chang">

    <od ProductID="2">

      <c CategoryName="Beverages"/>

    </od>

  </p>

</Products>

The same query in PATH mode…

PATH Mode

SELECT p.ProductName, od.ProductID, c.CategoryName

FROM Products p 

INNER JOIN [Order Details] od 

ON p.ProductID = od.ProductID 

INNER JOIN Categories c 

ON c.CategoryID = p.CategoryID 

GROUP BY od.ProductID, p.ProductName, c.CategoryName 

FOR XML PATH ('Product'), TYPE, ROOT('Products')

…returns an element for each column in the query:

<Products>

  <Product>

    <ProductName>Chai</ProductName>

    <ProductID>1</ProductID>

    <CategoryName>Beverages</CategoryName>

  </Product>

  <Product>

    <ProductName>Chang</ProductName>

    <ProductID>2</ProductID>

    <CategoryName>Beverages</CategoryName>

  </Product>

</Products>

As I mentioned earlier, all of the rules that apply to a simple query apply to more complex queries. To get XML in the format you want, I suggest reading the MSDN documentation, experiment with queries/results (as above), and/or post a comment below with details of what you are trying to achieve (I’ll see if I can help).

That’s it…hope this is as interesting to others as it was to SQL Server JumpIn Camp participants.

Thanks.

-Brian

Share this on Twitter

Returning Result Sets as XML

Consuming XML Result Sets with PHP

Examples

Simple Query Example

Add a Root Element

Change Element Names

Specify Attributes

Add a Namespace

Include an In-Line XSD

How are NULL Columns Handled?

A More Complex Query Example

No Comments