本文介紹了如何使用 XQuery 模擬 STRING_AGG()(分組字符串連接)?的處理方法，對大家解決問題具有一定的參考價值，需要的朋友們下面隨著小編來一起學習吧！
問題描述
限時送ChatGPT賬號..
我有 XML 數(shù)據(jù)，需要將其轉換為關系形式.我使用 XQuery 因為我不知道地址節(jié)點的數(shù)量.我想用逗號分隔整個地址/地址.我想我需要使用 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
更新多個地址和相同的數(shù)據(jù)
你可以試試這個(根據(jù)你的評論)
UPDATE Multiple addresses and identical data
You can try this (according to your comment)
帶有一秒地址和一個復制地址的測試數(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);
簡單的想法:
我們使用第一個 XQuery 創(chuàng)建一個像這樣的簡單 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()(分組字符串連接)?的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！
【網站聲明】本站部分內容來源于互聯(lián)網,旨在幫助大家更快的解決問題，如果有圖片或者內容侵犯了您的權益，請聯(lián)系我們刪除處理，感謝您的支持！