当前位置： 文江博客话题详情

用于检索嵌套 xml 中所有实例的 SQL 查询

发布于 2024-11-01 10:25:31 字数 8471 浏览 1 评论 0原文

我有一个 MS SQL 2005 数据库，其中 Report 表具有 XMLReport 列，其 XML 结构类似于：

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

组 Area_Group、Paint_Application_Group< /strong> 和 Base_Batch_Group 是可重复的

我想要实现的是一个包含列的表格：

Area_Number| Coat_Number | Base_Batch_No

，其中 Coat_Number 仅来自指定的 Area_Number，而 Base_Batch_No 将仅来自指定的 Coat_Number强>。

根据上面的例子，它应该创建这样的东西：

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

我尝试了很多方法，最后得到了这样的东西：

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

这正在生成一个如下表（我从我的脑海中创建了这个表，所以它不能显示所有行，但是您会明白这个想法）

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

因此仅包含第一个 Coat_Number，并且仅包含第一个 Coat_Number 中的第一个 Base_Batch_Number。

我无法弄清楚此查询如何迭代 Paint_Application_Group 和 Base_Batch_Group 组的所有实例。

请帮忙，我已经战斗了三天了......

Krzysztof Deneka

原文

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

如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

扫码二维码加入Web技术交流群

发布评论

需要 登录 才能够评论， 你可以免费 注册 一个本站的账号。

评论（1）

晨曦÷微暖 2024-11-08 10:25:31

为了简单起见，我删除了命名空间，但想法如下：

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)

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)
回复 原文
~没有更多了~

关于作者

方觉久

暂无简介

0 文章
0 评论
23 人气
发私信

相关话题

更多

热门标签

操作系统 程序设计 IT运维 Linux系统管理 JavaScript 服务器应用 solaris C/C++ PHP Shell BSD Vue.js aix Oracle Python HTML 系统管理 HTML5 CSS 前端
更多

推荐作者

游缘惊梦

文章 0 评论 0

小兔几

文章 0 评论 0

Glik

文章 0 评论 0

生生漫

文章 0 评论 0

Luxian

文章 0 评论 0

Champion-Ming

文章 0 评论 0

更多

友情链接

文江博客
    我们使用 Cookies 和其他技术来定制您的体验包括您的登录状态等。通过阅读我们的 隐私政策 了解更多相关信息。 单击 接受 或继续使用网站，即表示您同意使用 Cookies 和您的相关数据。
    原文