Monday, July 27, 2009

How to get deep nested elements in SQL Queries using FOR XML PATH

Recently in one of the forums I came across this question.

How can we have deep nested elements in the xml when we are using FOR XML PATH in a SQL Query.

For example the following query

SELECT 'value' AS 'orderdatabase/allorders/customer/information/orderaddress/shippingaddress/address1/home/address1/blahblahblahblahblahblahblahblah'

will give an error indicating that the identifier is too long and it should not exceed 128 characters. But the output expected from the query is:


In most of the scenarios in our real world code this might be required. We can resolve this with a workaround using sub queries which will return an xml which is subset of the parent xml. Following is the sample query to resolve the above issue:

SELECT '' 'orderdatabase/allorders/customer/information/orderaddress',
SELECT 'value' 'shippingaddress/address1/home/address1/blahblahblahblahblahblahblahblah'
FOR XML PATH(''), type

) AS 'orderdatabase/allorders/customer/information/orderaddress'
FOR XML PATH(''),root('root')

We can refer any of the sql table columns using {sql:column("column name")}.

This is a simple tip/work around for long nested xml's from a query.

1 comment:

Anonymous said...

it is not working in case of null is putting only alias name.