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

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 experimented with something 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

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

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