Pages

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'
FOR XML PATH('')


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:

<root>
 <orderdatabase>
  <allorders>
   <customer>
    <information>
     <orderaddress>
      <shippingaddress>
       <address1>
        <home>
         <address1>
          <blahblahblahblahblahblahblahblah>value</blahblahblahblahblahblahblahblah>
         </address1>
        </home>
       </address1>
      </shippingaddress>
     </orderaddress>
    </information>
   </customer>
  </allorders>
 </orderdatabase>
</root>

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 values.it is putting only alias name.