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 a calculated value of all A sub elements. For example the SUM of bb and product of cc * dd over all A elements. The number of A elements in the structure is unknown.

<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>

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

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