如何使用 XQuery 模拟 STRING_AGG()(分组字符串连接

How to use XQuery to simulate STRING_AGG() (grouped string concatenation)?(如何使用 XQuery 模拟 STRING_AGG()(分组字符串连接)?)
本文介绍了如何使用 XQuery 模拟 STRING_AGG()(分组字符串连接)?的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述

我有 XML 数据,需要将其转换为关系形式.我使用 XQuery 因为我不知道地址节点的数量.我想用逗号分隔整个地址/地址.我想我需要使用 LET 子句,但我仍然收到错误消息.

I have XML data which I need to convert to relational form. I use XQuery cause I don't know the number of address nodes. I'd like to get the whole address/adresses separeted by a comma. I guess I need to use LET clause but I'm still receiving an error.

这是我的代码:

declare @xml as xml = '<root>
    <Row>
        <proceeding>
            <signatures>V GU 86/18</signatures>
            <signatures>V GUp 9/19</signatures>
            <signatures>V GUp 8/19</signatures>
        </proceeding>
        <entity>
            <info>
                <cleaned_name>Kate Smith</cleaned_name>
            </info>
            <address>
                <town>London </town>
                <house_number>1 </house_number>
                <flat_number>1</flat_number>
                <street>Downing Street</street>
                <zip_code>00-001</zip_code>
            </address>
        </entity>
        <entity>
            <info>
                <cleaned_name>John Smith</cleaned_name>
            </info>
            <address>
                <town>Washington </town>
                <house_number>1</house_number>
                <flat_number>1</flat_number>
                <street>Pennsylvania Avenue</street>
                <zip_code>00-001</zip_code>
            </address>
        </entity>
    </Row>
</root>'

select 
    isnull(STUFF(a.x.query('for $s in  entity/info/cleaned_name return <x>{concat(",",$s)}</x>').value('.','varchar(max)'),1,1,''),'') as 'Nazwa podmiotu' 
   ,isnull(STUFF(a.x.query('for $s in proceeding/signatures return <x>{concat(",",$s)}</x>').value('.','varchar(max)'),1,1,''),'') as 'Sygnatura'
   --,isnull(STUFF(a.x.query('for $s in entity let $L := $s/entity/address   return <x>{concat(",",Address="{$s/Address}")}</x>').value('.','varchar(max)'),1,1,''),'') 
from @xml.nodes('/root/Row') as a(x)

我想要的结果

推荐答案

你在找这个吗?

select 
 isnull(STUFF(a.x.query('for $s in  entity/info/cleaned_name return <x>{concat(",",$s)}</x>').value('.','varchar(max)'),1,1,''),'') as 'Nazwa podmiotu' 
,isnull(STUFF(a.x.query('for $s in proceeding/signatures return <x>{concat(",",$s)}</x>').value('.','varchar(max)'),1,1,''),'') as 'Sygnatura'
,isnull(STUFF(a.x.query('for $s in entity
                         return <x>
                                {
                                concat(", ",($s/address/zip_code/text())[1]," "
                                           ,($s/address/town/text())[1]," "
                                           ,($s/address/street/text())[1]," "
                                           ,($s/address/house_number/text())[1],"/"
                                           ,($s/address/flat_number/text())[1]
                                          )
                                }
                                </x>').value('.','varchar(max)'),1,2,''),'') 
    from @xml.nodes('/root/Row') as a(x);

结果

Nazwa podmiotu          Sygnatura                           AllAdresses
Kate Smith,John Smith   V GU 86/18,V GUp 9/19,V GUp 8/19    00-001 London  Downing Street 1 /1, 00-001 Washington  Pennsylvania Avenue 1/1

更新多个地址和相同的数据

你可以试试这个(根据你的评论)

UPDATE Multiple addresses and identical data

You can try this (according to your comment)

带有一秒地址和一个复制地址的测试数据:

Your test data with one second address and one copied address:

declare @xml as xml = '<root>
    <Row>
        <proceeding>
            <signatures>V GU 86/18</signatures>
            <signatures>V GUp 9/19</signatures>
            <signatures>V GUp 8/19</signatures>
        </proceeding>
        <entity>
            <info>
                <cleaned_name>Kate Smith</cleaned_name>
            </info>
            <address>
                <town>London </town>
                <house_number>1 </house_number>
                <flat_number>1</flat_number>
                <street>Downing Street</street>
                <zip_code>00-001</zip_code>
            </address>
            <address>
                <town>Yorkshire </town>
                <house_number>1 </house_number>
                <flat_number>1</flat_number>
                <street>Morning Street</street>
                <zip_code>00-999</zip_code>
            </address>
        </entity>
        <entity>
            <info>
                <cleaned_name>John Smith</cleaned_name>
            </info>
            <address>
                <town>Washington </town>
                <house_number>1</house_number>
                <flat_number>1</flat_number>
                <street>Pennsylvania Avenue</street>
                <zip_code>00-001</zip_code>
            </address>
            <address>
                <town>Washington </town>
                <house_number>1</house_number>
                <flat_number>1</flat_number>
                <street>Pennsylvania Avenue</street>
                <zip_code>00-001</zip_code>
            </address>
        </entity>
    </Row>
</root>'

--查询

select 
 isnull(STUFF(a.x.query('for $s in  entity/info/cleaned_name return <x>{concat(",",$s)}</x>').value('.','varchar(max)'),1,1,''),'') as 'Nazwa podmiotu' 
,isnull(STUFF(a.x.query('for $s in proceeding/signatures return <x>{concat(",",$s)}</x>').value('.','varchar(max)'),1,1,''),'') as 'Sygnatura'
,isnull(STUFF(a.x.query('for $s in entity/address
                            return
                            <x>{concat(", ",($s/zip_code/text())[1]," "
                                           ,($s/town/text())[1]," "
                                           ,($s/street/text())[1]," "
                                           ,($s/house_number/text())[1],"/"
                                           ,($s/flat_number/text())[1]
                                       )}</x>')
                   .query('for $a in distinct-values(/x/text()) return $a').value('.','varchar(max)'),1,2,''),'') 
    from @xml.nodes('/root/Row') as a(x);

简单的想法:

我们使用第一个 XQuery 创建一个像这样的简单 XML 片段

We use the first XQuery to create a simple XML fragment like this

<x>, 00-001 London  Downing Street 1 /1</x>
<x>, 00-999 Yorkshire  Morning Street 1 /1</x>
<x>, 00-001 Washington  Pennsylvania Avenue 1/1</x>
<x>, 00-001 Washington  Pennsylvania Avenue 1/1</x>

有了这个,我们可以使用第二个 XQuery 并将 distinct-values() 放在那里.

With this we can use a second XQuery and place distinct-values() there.

这篇关于如何使用 XQuery 模拟 STRING_AGG()(分组字符串连接)?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持编程学习网!

本站部分内容来源互联网,如果有图片或者内容侵犯您的权益请联系我们删除!

相关文档推荐

Execute complex raw SQL query in EF6(在EF6中执行复杂的原始SQL查询)
SSIS: Model design issue causing duplications - can two fact tables be connected?(SSIS:模型设计问题导致重复-两个事实表可以连接吗?)
SQL Server Graph Database - shortest path using multiple edge types(SQL Server图形数据库-使用多种边类型的最短路径)
Invalid column name when using EF Core filtered includes(使用EF核心过滤包括时无效的列名)
How should make faster SQL Server filtering procedure with many parameters(如何让多参数的SQL Server过滤程序更快)
How can I generate an entity–relationship (ER) diagram of a database using Microsoft SQL Server Management Studio?(如何使用Microsoft SQL Server Management Studio生成数据库的实体关系(ER)图?)
6