SQL query to retrieve all instances in nested xml

I have a MS SQL 2005 database, where Report table have XMLReport column with XML structure similar to this:

<my:CART_Marine>
    <my:Area_Summary_Details>
        <my:Areas>
            <my:Area_Group>
                <my:Area_Number>1</my:Area_Number>
                <my:Paint_Application>
                    <my:Paint_Applications>
                        <my:Paint_Application_Group>
                            <my:Coat_Number>1</my:Coat_Number>
                            <my:Base_Batches>
                                <my:Base_Batch_Group>
                                    <my:Base_Batch_No>1234567</my:Base_Batch_No>
                                    <my:Base_Batch_No>5455443</my:Base_Batch_No>
                                    <my:Base_Batch_No>8677667</my:Base_Batch_No>
                                    <my:Base_Batch_No>3455445</my:Base_Batch_No>
                                </my:Base_Batch_Group>
                            </my:Base_Batches>  
                        </my:Paint_Application_Group>
                        <my:Paint_Application_Group>
                            <my:Coat_Number>2</my:Coat_Number>
                            <my:Base_Batches>
                                <my:Base_Batch_Group>
                                    <my:Base_Batch_No>9744566</my:Base_Batch_No>
                                    <my:Base_Batch_No>8755632</my:Base_Batch_No>
                                </my:Base_Batch_Group>
                            </my:Base_Batches>  
                        </my:Paint_Application_Group>
                        <my:Paint_Application_Group>
                            <my:Coat_Number>3</my:Coat_Number>
                            <my:Base_Batches>
                                <my:Base_Batch_Group>
                                    <my:Base_Batch_No>5456783</my:Base_Batch_No>
                                </my:Base_Batch_Group>
                            </my:Base_Batches>  
                        </my:Paint_Application_Group>
                    </my:Paint_Applications>
                </my:Paint_Application>
            </my:Area_Group>
            <my:Area_Group>
                <my:Area_Number>2</my:Area_Number>
                <my:Paint_Application>
                    <my:Paint_Applications>
                        <my:Paint_Application_Group>
                            <my:Coat_Number>1</my:Coat_Number>
                            <my:Base_Batches>
                                <my:Base_Batch_Group>
                                    <my:Base_Batch_No>2312311</my:Base_Batch_No>
                                    <my:Base_Batch_No>2352244</my:Base_Batch_No>
                                    <my:Base_Batch_No>8746773</my:Base_Batch_No>
                                    <my:Base_Batch_No>7363634</my:Base_Batch_No>
                                </my:Base_Batch_Group>
                            </my:Base_Batches>  
                        </my:Paint_Application_Group>
                    </my:Paint_Applications>
                </my:Paint_Application>
            </my:Area_G开发者_如何学Goroup>
            <my:Area_Group>
                <my:Area_Number>3</my:Area_Number>
                <my:Paint_Application>
                    <my:Paint_Applications>
                        <my:Paint_Application_Group>
                            <my:Coat_Number>1</my:Coat_Number>
                            <my:Base_Batches>
                                <my:Base_Batch_Group>
                                    <my:Base_Batch_No>1523552</my:Base_Batch_No>
                                    <my:Base_Batch_No>6164633</my:Base_Batch_No>
                                </my:Base_Batch_Group>
                            </my:Base_Batches>  
                        </my:Paint_Application_Group>
                    </my:Paint_Applications>
                </my:Paint_Application>
            </my:Area_Group>
        </my:Areas>                     
    </my:Area_Summary_Details>                          
</my:CART_Marine>                               

Groups Area_Group, Paint_Application_Group and Base_Batch_Group are repeatable

What I want to achieve is a table with columns:

Area_Number| Coat_Number | Base_Batch_No

where will be Coat_Number only from specified Area_Number and Base_Batch_No will be only from specified Coat_Number.

Based on the example above it should create something like this:

**Area_Number** |**Coat_Number**    |**Base_Batch_Number**

1       |1      |1234567

1       |1      |5455443

1       |1      |8677667

1       |1      |3455445

1       |2      |9744566

1       |2      |8755632

1       |3      |5456783

2       |1      |2312311

2       |1      |2352244

2       |1      |8746773

2       |1      |7363634

3       |1      |1523552

3       |1      |6164633

I tried many ways and I finished with something like this:

select distinct
        r.XMLReport.value('declare namespace my="http://schemas.microsoft.com/office/infopath/2003/myXSD/2009-09-02T08:49:16";(my:Area_Number)[1]','nvarchar(12)')  AS Area_Number,
        s.XMLReport.value('declare namespace my="http://schemas.microsoft.com/office/infopath/2003/myXSD/2009-09-02T08:49:16";(my:Paint_Application/my:Paint_Applications/my:Paint_Application_Group/my:Coat_Number)[1]','nvarchar(12)') AS Coat_Number,
        t.XmlReport.value('declare namespace my="http://schemas.microsoft.com/office/infopath/2003/myXSD/2009-09-02T08:49:16";(my:Paint_Application/my:Paint_Applications/my:Paint_Application_Group/my:Base_Batches/my:Base_Batch_Group/my:Base_Batch_No)[1]','nvarchar(12)') AS Base_Batch_Number
from Report  
        cross apply Report.XMLReport.nodes('declare namespace my="http://schemas.microsoft.com/office/infopath/2003/myXSD/2009-09-02T08:49:16";(/my:CART_Marine/my:Area_Summary_Details/my:Areas/my:Area_Group)') AS s(XMLReport)
        cross apply  Report.XMLReport.nodes('declare namespace my="http://schemas.microsoft.com/office/infopath/2003/myXSD/2009-09-02T08:49:16";(/my:CART_Marine/my:Area_Summary_Details/my:Areas/my:Area_Group)') AS r(XMLReport)
        cross apply  Report.XMLReport.nodes('declare namespace my="http://schemas.microsoft.com/office/infopath/2003/myXSD/2009-09-02T08:49:16";(/my:CART_Marine/my:Area_Summary_Details/my:Areas/my:Area_Group)') AS t(XMLReport)

where
        r.XMLReport.value('declare namespace my="http://schemas.microsoft.com/office/infopath/2003/myXSD/2009-09-02T08:49:16";(my:Area_Number)[1]','nvarchar(12)')= s.XMLReport.value('declare namespace my="http://schemas.microsoft.com/office/infopath/2003/myXSD/2009-09-02T08:49:16";(my:Area_Number)[1]','nvarchar(12)')
    AND
        s.XMLReport.value('declare namespace my="http://schemas.microsoft.com/office/infopath/2003/myXSD/2009-09-02T08:49:16";(my:Paint_Application/my:Paint_Applications/my:Paint_Application_Group/my:Coat_Number)[1]','nvarchar(12)')=   t.XMLReport.value('declare namespace my="http://schemas.microsoft.com/office/infopath/2003/myXSD/2009-09-02T08:49:16";(my:Paint_Application/my:Paint_Applications/my:Paint_Application_Group/my:Coat_Number)[1]','nvarchar(12)')

This is producing a table as below (I created this table from my mind, so it could'n show all rows, but you will catch the idea)

**Area_Number** |**Coat_Number**    |**Base_Batch_Number**

1           |1              |1234567

1           |1              |5455443

1           |1              |8677667

1           |1              |3455445

2           |1              |2312311

3           |1              |1523552

So only first Coat_Number is included and only first Base_Batch_Number in first Coat_Number is included.

I am not able to figure out how this query could iterate through all instances of Paint_Application_Group and Base_Batch_Group groups.

Please help, I am fightenig for 3 days now...

Krzysztof Deneka

I removed the namespaces for the sake of simplicity, but here's the idea:

DECLARE @report XML = N'
<CART_Marine>
    <Area_Summary_Details>
        <Areas>
            <Area_Group>
                <Area_Number>1</Area_Number>
                <Paint_Application>
                    <Paint_Applications>
                        <Paint_Application_Group>
                            <Coat_Number>1</Coat_Number>
                            <Base_Batches>
                                <Base_Batch_Group>
                                    <Base_Batch_No>1234567</Base_Batch_No>
                                    <Base_Batch_No>5455443</Base_Batch_No>
                                    <Base_Batch_No>8677667</Base_Batch_No>
                                    <Base_Batch_No>3455445</Base_Batch_No>
                                </Base_Batch_Group>
                            </Base_Batches>  
                        </Paint_Application_Group>
                        <Paint_Application_Group>
                            <Coat_Number>2</Coat_Number>
                            <Base_Batches>
                                <Base_Batch_Group>
                                    <Base_Batch_No>9744566</Base_Batch_No>
                                    <Base_Batch_No>8755632</Base_Batch_No>
                                </Base_Batch_Group>
                            </Base_Batches>  
                        </Paint_Application_Group>
                        <Paint_Application_Group>
                            <Coat_Number>3</Coat_Number>
                            <Base_Batches>
                                <Base_Batch_Group>
                                    <Base_Batch_No>5456783</Base_Batch_No>
                                </Base_Batch_Group>
                            </Base_Batches>  
                        </Paint_Application_Group>
                    </Paint_Applications>
                </Paint_Application>
            </Area_Group>
            <Area_Group>
                <Area_Number>2</Area_Number>
                <Paint_Application>
                    <Paint_Applications>
                        <Paint_Application_Group>
                            <Coat_Number>1</Coat_Number>
                            <Base_Batches>
                                <Base_Batch_Group>
                                    <Base_Batch_No>2312311</Base_Batch_No>
                                    <Base_Batch_No>2352244</Base_Batch_No>
                                    <Base_Batch_No>8746773</Base_Batch_No>
                                    <Base_Batch_No>7363634</Base_Batch_No>
                                </Base_Batch_Group>
                            </Base_Batches>  
                        </Paint_Application_Group>
                    </Paint_Applications>
                </Paint_Application>
            </Area_Group>
            <Area_Group>
                <Area_Number>3</Area_Number>
                <Paint_Application>
                    <Paint_Applications>
                        <Paint_Application_Group>
                            <Coat_Number>1</Coat_Number>
                            <Base_Batches>
                                <Base_Batch_Group>
                                    <Base_Batch_No>1523552</Base_Batch_No>
                                    <Base_Batch_No>6164633</Base_Batch_No>
                                </Base_Batch_Group>
                            </Base_Batches>  
                        </Paint_Application_Group>
                    </Paint_Applications>
                </Paint_Application>
            </Area_Group>
        </Areas>                     
    </Area_Summary_Details>                          
</CART_Marine>'

SELECT  batch.value('../../../../../../Area_Number[1]', 'INTEGER'),
        batch.value('../../../Coat_Number[1]', 'INTEGER'),
        batch.value('.', 'INTEGER')
FROM    @report.nodes('//Base_Batch_No') r(batch)