本文介紹了如何使用 XQuery 模擬 STRING_AGG()(分組字符串連接)?的處理方法，對(duì)大家解決問(wèn)題具有一定的參考價(jià)值，需要的朋友們下面隨著小編來(lái)一起學(xué)習(xí)吧！
問(wèn)題描述
限時(shí)送ChatGPT賬號(hào)..
我有 XML 數(shù)據(jù)，需要將其轉(zhuǎn)換為關(guān)系形式.我使用 XQuery 因?yàn)槲也恢赖刂饭?jié)點(diǎn)的數(shù)量.我想用逗號(hào)分隔整個(gè)地址/地址.我想我需要使用 LET 子句，但我仍然收到錯(cuò)誤消息.
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)
我想要的結(jié)果
推薦答案
你在找這個(gè)嗎?
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);
結(jié)果
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
更新多個(gè)地址和相同的數(shù)據(jù)
你可以試試這個(gè)(根據(jù)你的評(píng)論)
UPDATE Multiple addresses and identical data
You can try this (according to your comment)
帶有一秒地址和一個(gè)復(fù)制地址的測(cè)試數(shù)據(jù):
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);
簡(jiǎn)單的想法:
我們使用第一個(gè) XQuery 創(chuàng)建一個(gè)像這樣的簡(jiǎn)單 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>
有了這個(gè)，我們可以使用第二個(gè) XQuery 并將 distinct-values() 放在那里.
With this we can use a second XQuery and place distinct-values() there.
這篇關(guān)于如何使用 XQuery 模擬 STRING_AGG()(分組字符串連接)?的文章就介紹到這了，希望我們推薦的答案對(duì)大家有所幫助，也希望大家多多支持html5模板網(wǎng)！
【網(wǎng)站聲明】本站部分內(nèi)容來(lái)源于互聯(lián)網(wǎng),旨在幫助大家更快的解決問(wèn)題，如果有圖片或者內(nèi)容侵犯了您的權(quán)益，請(qǐng)聯(lián)系我們刪除處理，感謝您的支持！