FOR XML PATH using node tests in column aliases

I've used SQL to generate countless blobs of xml over the years and one feature that I have come to rely heavily upon is the ability to easily build complex xml structures by using column aliases in conjunction with the FOR XML PATH clause in your query. Not only do they allow for speedy creation, they also make the query very readable and it doesn't take long to be able to visualize the xml structure just by looking at the query itself. In conjunction with this, there are a number of node tests that can also be specified as a column alias to provide even more power when creating your xml structure. For sure, FOR XML EXPLICIT also gives you a lot of control and flexibility to create an xml structure, but it is nowhere near as easy to use as PATH mode.

Before we get into using node test names as aliases, here is a simple query using column aliases and the FOR XML PATH clause to demonstrate the basics. I'm using the AdventureWorks database, but the examples will work on any database, I've also limited the results in most of my queries to cut down on the size of the xml structures.

SELECT TOP 2 create_date AS '@CreatedDate',
SCHEMA_NAME(schema_id) AS 'SchemaName', 
name AS 'Tablename',
is_ms_shipped AS 'Properties/MSShipped',
is_published AS 'Properties/ISPublished'
FROM sys.tables
FOR XML PATH('Table'), ROOT('Tables')

Result:

<Tables>
  <Table CreatedDate="2005-10-14T01:58:34.740">
    <SchemaName>Production</SchemaName>
    <Tablename>ProductProductPhoto</Tablename>
    <Properties>
      <MSShipped>0</MSShipped>
      <ISPublished>0</ISPublished>
    </Properties>
  </Table>
  <Table CreatedDate="2005-10-14T01:58:37.257">
    <SchemaName>Sales</SchemaName>
    <Tablename>StoreContact</Tablename>
    <Properties>
      <MSShipped>0</MSShipped>
      <ISPublished>0</ISPublished>
    </Properties>
  </Table>
</Tables>

This simple query has demonstrated some of the features of using column aliases to create your xml structure. Prefixing your column alias with @ will create an attribute; otherwise it will create an element whilst using the forward slash "/" allows for a hierarchy to be created. This allows for an immense amount of flexibility especially when you start to throw in joins and sub queries into the mix as well.

However, as well as being able to neatly specify the attribute or element name there are a number of node tests that can also be specified in the column alias which give you some more options when creating your xml structure. These are:

comment() - will create an xml comment node
data() - will create a space separated list value
text() - will add the column data as a text node
node() or * - will add the column data xml as is. If column is non-xml, then functions the same as text()
processing-instruction(...) - will add the column as a PI value of the specified name

comment()

Can't say that I personally use this a lot day to day! But using it will create an xml comment node within your xml with the column data forming the comments in the node. To demonstrate with revised query:

SELECT TOP 1 create_date AS '@CreatedDate',
SCHEMA_NAME(schema_id) AS SchemaName, 
name AS 'Tablename',
--Add a comment into the xml using comment()
'These are properties of the table' AS 'Properties/comment()',
is_ms_shipped AS 'Properties/MSShipped',
is_published AS 'Properties/ISPublished'
FROM sys.tables
FOR XML PATH('Table'), ROOT('Tables')

Result with comment node under <Properties>:

<Tables>
  <Table CreatedDate="2005-10-14T01:58:34.740">
    <SchemaName>Production</SchemaName>
    <Tablename>ProductProductPhoto</Tablename>
    <Properties>
      <!--These are properties of the table-->
      <MSShipped>0</MSShipped>
      <ISPublished>0</ISPublished>
    </Properties>
  </Table>
</Tables>

data()

This is quite a useful way of creating a list of values within a single element or attribute. By using a sub-query, each value in the column is separated with a space and returned as a single atomic value.

SELECT TOP 1 create_date AS '@CreatedDate',
SCHEMA_NAME(schema_id) AS SchemaName, 
name AS 'Tablename',
--sub query which will concatenate all of the column names as a space separated string using data()
(
SELECT name AS 'data()' 
FROM sys.columns c 
WHERE c.object_id = t.object_id 
FOR XML PATH('')
) AS 'ColumnNames',
is_ms_shipped AS 'Properties/MSShipped',
is_published AS 'Properties/ISPublished'
FROM sys.tables t
FOR XML PATH('Table'), ROOT('Tables')

Result with new <ColumnNames> node:

<Tables>
  <Table CreatedDate="2005-10-14T01:58:34.740">
    <SchemaName>Production</SchemaName>
    <Tablename>ProductProductPhoto</Tablename>
    <ColumnNames>ProductID ProductPhotoID Primary ModifiedDate</ColumnNames>
    <Properties>
      <MSShipped>0</MSShipped>
      <ISPublished>0</ISPublished>
    </Properties>
  </Table>
</Tables>

It won't take long either to change the above query to return a comma separated list of values instead of returning a space separated list of values.

text()

This will add a text node into the xml structure which is essentially the column data without an explicit element/attribute wrapping the data. I'm not a big fan nor like to recommend mixing data with xml elements, but at the end of the day it is still valid xml and there are occasions where this is needed:

SELECT TOP 1 create_date AS '@CreatedDate',
--Add the object_id column as a text node using text(),
object_id AS 'text()',
SCHEMA_NAME(schema_id) AS SchemaName, 
name AS 'Tablename',
is_ms_shipped AS 'Properties/MSShipped',
is_published AS 'Properties/ISPublished'
FROM sys.tables
FOR XML PATH('Table'), ROOT('Tables')

Result with 18099105 added as a text node:

<Tables>
  <Table CreatedDate="2005-10-14T01:58:34.740">
    18099105
    <SchemaName>Production</SchemaName>
    <Tablename>ProductProductPhoto</Tablename>
    <Properties>
      <MSShipped>0</MSShipped>
      <ISPublished>0</ISPublished>
    </Properties>
  </Table>
</Tables>

The value "18099105" is the value of the element <Table> and also <Table> has further child nodes.

node() or *

If the column data is of a non-xml type, then this will function exactly the same as the node test name text(). However, if the column data is of an xml type, it will insert the entire xml tree of the column into the final xml structure. This isn't possible with text() and if you was to try it, SQL would escape the xml tree being inserted.

SELECT TOP 1 create_date AS '@CreatedDate',
--sub query which will retrieve all column names as an xml tree
(
SELECT name AS 'Name'
FROM sys.columns c 
WHERE c.object_id = t.object_id 
FOR XML PATH(''), ROOT('ColumnNames'), TYPE
) AS 'node()',
SCHEMA_NAME(schema_id) AS SchemaName, 
name AS 'Tablename',
is_ms_shipped AS 'Properties/MSShipped',
is_published AS 'Properties/ISPublished'
FROM sys.tables t
FOR XML PATH('Table'), ROOT('Tables')

Result with <ColumnNames> from the sub-query added to xml:

<Tables>
  <Table CreatedDate="2005-10-14T01:58:34.740">
    <ColumnNames>
      <Name>ProductID</Name>
      <Name>ProductPhotoID</Name>
      <Name>Primary</Name>
      <Name>ModifiedDate</Name>
    </ColumnNames>
    <SchemaName>Production</SchemaName>
    <Tablename>ProductProductPhoto</Tablename>
    <Properties>
      <MSShipped>0</MSShipped>
      <ISPublished>0</ISPublished>
    </Properties>
  </Table>
</Tables>

In fact, if the sub-query returns an xml-type column, then you don't actually need to specify node() or * in the column alias as SQL will by default insert the sub-query xml tree into the main xml tree. Take out the "AS 'node()'" bit in the example query and see that it will return an identical xml tree.

processing-instruction(...)

Finally using this node test name as part of the column alias will add a processing instruction to the xml. The string value provided in the processing-instruction test will form the name of the instruction and the data in the column will be the value.

SELECT TOP 1 create_date AS '@CreatedDate',
--add the object_id as a processing instruction into the xml
object_id AS 'processing-instruction(ObjectID)',
SCHEMA_NAME(schema_id) AS SchemaName, 
name AS 'Tablename',
is_ms_shipped AS 'Properties/MSShipped',
is_published AS 'Properties/ISPublished'
FROM sys.tables
FOR XML PATH('Table'), ROOT('Tables')

Result with <?ObjectID> node added:

<Tables>
  <Table CreatedDate="2005-10-14T01:58:34.740">
    <?ObjectID 18099105?>
    <SchemaName>Production</SchemaName>
    <Tablename>ProductProductPhoto</Tablename>
    <Properties>
      <MSShipped>0</MSShipped>
      <ISPublished>0</ISPublished>
    </Properties>
  </Table>
</Tables>

So there you go. A number of XPath node tests that can be used within your column aliases when generating xml using the FOR XML PATH clause.

Enjoy!

FOR XML PATH using node tests in column aliases

comment()

data()

text()

node() or *

processing-instruction(...)

Rate

Share

Share

Rate

FOR XML PATH using node tests in column aliases

comment()

data()

text()

node() or *

processing-instruction(...)

Rate

Share

Share

Rate

Related content

Removing HTML formatting tag - BR - from XML using XQuery

Using XQuery to remove duplicate values or duplicate nodes from an xml instance

Shredding a simple xml structure to a single row flat table

Introduction to XML DML (Part 3) - Using the modify() method: delete

XQuery for the Non-Expert – @Variable Use