In my last blog I briefly touched upon storing XML documents in MSSQL. Now let’s discuss creating them instead. Specifically I mean creating them using the FOR XML keywords in MSSQL. While this functionality exists in other database systems such as Oracle and DB2, their implementation is different and warrants a separate blog post.
Most of us are familiar with selecting data using a SQL command and retrieving some sort of result set. With XML being used so frequently for a wide range of applications, it is not uncommon for a programmer to want to build an XML message out of data stored in a database somewhere. Instead of using SQL queries and looping over result sets, it is possible in MSSQL to use the FOR XML declaration to actually return that data as pre-formatted XML.
There are several different ways to control the output of the XML you are trying to get. You can control the mode, as well as specifically declare some items such as the ROOT element. Lets consider a scenario where I have a table that contains two records, and I wish to return all of the data as XML.
CREATE TABLE DEMO (
VALUES (‘field1Text’, ‘field2Text’)
VALUES (‘moreField1Text’, ‘moreField2Text’)
We can select this data and return it in an XML format using this statement.
SELECT * FROM DEMO FOR XML PATH(‘row’), ROOT(‘DEMO_DATA’);
This should return an XML Document that looks like this:
Note the use of the word ‘PATH’ in the SQL statement. This is one of the modes I was talking about. You can also select FOR XML RAW, FOR XML AUTO, and FOR XML EXPLICIT, among others. Each mode has different behavior and slightly different syntax.
This little known feature of some databases can come in very handy for marking up data in XML format. Further reading on MSSQL specific implementation can be found here :