My source data in a Sql Server table has XML structured data. For each of the source rows I need to query the following sample xml structure in a way that I get all <A> elements in a separate row in the result. The number of A elements is unknown. In addition to that I need to have some of the elements below <A> (let's say bb and dd) as separate columns in the result.

    <root>
    	<A>
    		<bb>bb1</bb>
    		<cc>cc1</cc>
    		<dd>dd1</dd>
    	</A>
    	<A>
    		<bb>bb2</bb>
    		<cc>cc2</cc>
    		<dd>dd2</dd>
    	</A>
    	<A>
    		<bb>bb3</bb>
    		<cc>cc3</cc>
    		<dd>dd3</dd>
    	</A>
    </root>

So the result in this sample should look like the following:

>Row#1: | bb1 | dd1 

>Row#2: | bb2 | dd2 

>Row#3: | bb3 | dd3

This shows that the final result in a way has to "expand" depending of the number of <A> elements in the source. Is this possible?

I experiemented with sth. like this, but it looks loke not the right way:

    SELECT Convert(xml,myXmlCol).value('(/root/A[1]/bb)[1]', 'int') as MyResult FROM SourceTable