xml - 如何在不知道其数量的情况下动态获取所有元素
问题描述
我有一个可能有几个签名元素的 xml。我不知道每个根的签名元素的数量，但我需要按此元素分组，以便为每个根元素获得一行。
您认为使用价值方法可行吗？我已经阅读（Getting nth Element from last in a xml in Sql Server）如何建立元素的数量，但不允许在值方法中使用范围。
这是我的代码：
declare @xml as xml ='<root>
<Row>
<proceeding>
<signatures>XII GU 609/18</signatures>
<signatures>XII GUp 392/18</signatures>
<commissioner_name></commissioner_name>
<court_department>XII Wydział Gospodarczy</court_department>
<administrator_function>syndyk</administrator_function>
<court_name>Sąd Rejonowy w Gliwicach</court_name>
<administrator_name></administrator_name>
<commissioner_deputy_name />
<administrator_licence_number>920</administrator_licence_number>
</proceeding>
<msig_entry>
<chapter>0</chapter>
<issue_date>2019-11-15</issue_date>
<url>http://www.imsig.pl/pozycja/2019/221/58729</url>
<section>3</section>
<sequential_number />
<signature>BMSiG-58674/2019</signature>
<position_number>58729</position_number>
<yearly_number>221</yearly_number>
</msig_entry>
<id>BMSiG-58674/2019</id>
<order>
<order_date />
<expiration_period>14</expiration_period>
<expiration_date>2019-11-29</expiration_date>
</order>
</Row>
</root>'
select
isnull(a.x.value('(entity/info/cleaned_name)[1]','varchar(max)'),'') as 'Nazwa podmiotu'
,isnull(a.x.value('(entity/info/first_name)[1]','varchar(max)'),'') as 'Imię'
,isnull(a.x.value('(entity/info/last_name)[1]','varchar(max)'),'') as 'Nazwisko'
,isnull(a.x.value('(entity/info/legal_form)[1]','varchar(max)'),'') as 'Forma prawna'
,isnull(a.x.value('(entity/info/ownership_type)[1]','varchar(max)'),'') as 'Forma własności'
,isnull(a.x.value('(entity/info/commencement_date)[1]','date'),'') as 'Data rozpoczęcia działalności'
,isnull(a.x.value('(entity/address/zip_code)[1]','varchar(255)'),'')+' '+isnull(a.x.value('(entity/address/town)[1]','varchar(255)'),'')
+' '+isnull(a.x.value('(entity/address/street)[1]','varchar(255)'),'')+' '+ISNULL(a.x.value('(entity/address/house_number)[1]','varchar(255)'),'')
+case when ISNULL(a.x.value('(entity/address/flat_number)[1]','varchar(255)'),'')<>'' then +'/'+a.x.value('(entity/address/flat_number)[1]','varchar(255)')
else '' end as Adres
,isnull(a.x.value('(proceeding/court_name)[1]','varchar(max)'),'') as 'Nazwa sądu'
,isnull(a.x.value('(proceeding/court_department)[1]','varchar(max)'),'') as 'Nazwa wydziału'
,isnull(a.x.value('(proceeding/signatures)[1]','varchar(max)'),'') as 'Sygnatura' --grupowanie!
,isnull(a.x.value('(proceeding/commissioner_name)[1]','varchar(max)'),'') as 'Sędzia komisarz'
,isnull(a.x.value('(proceeding/commissioner_deputy_name)[1]','varchar(max)'),'') as 'Zastępca sędziego komisarza'
,isnull(a.x.value('(proceeding/administrator_name)[1]','varchar(max)'),'') as 'Nadzorca w postępowaniu'
,isnull(a.x.value('(proceeding/administrator_function)[1]','varchar(max)'),'') as 'Funkcja nadzorcy'
,isnull(a.x.value('(order/order_date)[1]','date'),'') as 'Data wydania postanowienia'
,isnull(a.x.value('(order/expiration_period)[1]','varchar(20)'),'') as 'Okres do działania (w dniach)'
,isnull(a.x.value('(order/expiration_date)[1]','date'),'') as 'Koniec okresu do działania'
,isnull(a.x.value('(msig_entry/chapter)[1]','varchar(max)'),'') as 'Rozdział MSiG'
,isnull(a.x.value('(msig_entry/section)[1]','varchar(max)'),'') as 'Sekcja MSiG'
,isnull(a.x.value('(msig_entry/signature)[1]','varchar(max)'),'') as 'Sygnatura ogłoszenia'
,isnull(a.x.value('(msig_entry/issue_date)[1]','date'),'') as 'Data publikacji ogłoszenia w MSiG'
,isnull(a.x.value('(content/text)[1]','varchar(max)'),'') as 'Treść ogłoszenia'
from @xml.nodes('/root/Row') as a(x)
我想要的结果：
解决方案
请尝试以下方法。
SQL
DECLARE @xml AS XML
= '<root>
<Row>
<proceeding>
<signatures>XII GU 609/18</signatures>
<signatures>XII GUp 392/18</signatures>
<commissioner_name></commissioner_name>
<court_department>XII Wydział Gospodarczy</court_department>
<administrator_function>syndyk</administrator_function>
<court_name>Sąd Rejonowy w Gliwicach</court_name>
<administrator_name></administrator_name>
<commissioner_deputy_name />
<administrator_licence_number>920</administrator_licence_number>
</proceeding>
<msig_entry>
<chapter>0</chapter>
<issue_date>2019-11-15</issue_date>
<url>http://www.imsig.pl/pozycja/2019/221/58729</url>
<section>3</section>
<sequential_number />
<signature>BMSiG-58674/2019</signature>
<position_number>58729</position_number>
<yearly_number>221</yearly_number>
</msig_entry>
<entity>
<info>
<first_name></first_name>
<last_name></last_name>
<legal_form>osoby fizyczne</legal_form>
<commencement_date>1954-12-09</commencement_date>
<cleaned_name></cleaned_name>
<primary_business />
<ownership_type>własność</ownership_type>
</info>
<address>
<town>Jastrzębie-Zdrój</town>
<gmina>Jastrzębie-Zdrój</gmina>
<house_number />
<flat_number />
<state>śląskie</state>
<street />
<post_office>Jastrzębie-Zdrój</post_office>
<powiat>Jastrzębie-Zdrój</powiat>
<zip_code />
</address>
</entity>
<id>BMSiG-58674/2019</id>
<order>
<order_date />
<expiration_period>14</expiration_period>
<expiration_date>2019-11-29</expiration_date>
</order>
</Row>
</root>';
DECLARE @separator CHAR(1) = ',';
SELECT ISNULL(x.value('(entity/info/cleaned_name)[1]', 'varchar(max)'), '') AS 'Nazwa podmiotu',
ISNULL(x.value('(entity/info/first_name)[1]', 'varchar(max)'), '') AS 'Imię',
ISNULL(x.value('(entity/info/last_name)[1]', 'varchar(max)'), '') AS 'Nazwisko',
ISNULL(x.value('(entity/info/legal_form)[1]', 'varchar(max)'), '') AS 'Forma prawna',
ISNULL(x.value('(entity/info/ownership_type)[1]', 'varchar(max)'), '') AS 'Forma własności',
ISNULL(x.value('(entity/info/commencement_date)[1]', 'date'), '') AS 'Data rozpoczęcia działalności',
ISNULL(x.value('(entity/address/zip_code)[1]', 'varchar(255)'), '') + ' '
+ ISNULL(x.value('(entity/address/town)[1]', 'varchar(255)'), '') + ' '
+ ISNULL(x.value('(entity/address/street)[1]', 'varchar(255)'), '') + ' '
+ ISNULL(x.value('(entity/address/house_number)[1]', 'varchar(255)'), '')
+ CASE
WHEN ISNULL(a.x.value('(entity/address/flat_number)[1]', 'varchar(255)'), '') <> '' THEN
+'/' + a.x.value('(entity/address/flat_number)[1]', 'varchar(255)')
ELSE
''
END AS Adres,
ISNULL(x.value('(proceeding/court_name)[1]', 'varchar(max)'), '') AS 'Nazwa sądu',
ISNULL(x.value('(proceeding/court_department)[1]', 'varchar(max)'), '') AS 'Nazwa wydziału',
-- grupowanie by Sygnatura
(SELECT STUFF((SELECT @separator + CAST([Sygnatura] AS VARCHAR(30)) AS [text()]
FROM (
SELECT ISNULL(c.value('(./text())[1]', 'VARCHAR(MAX)'), '') AS [Sygnatura]
FROM @xml.nodes('/root/Row/proceeding/signatures') AS t(c)
) AS rs
FOR XML PATH('')), 1, 1, NULL)) AS [Sygnatura_List],
ISNULL(x.value('(proceeding/commissioner_name)[1]', 'varchar(max)'), '') AS 'Sędzia komisarz',
ISNULL(x.value('(proceeding/commissioner_deputy_name)[1]', 'varchar(max)'), '') AS 'Zastępca sędziego komisarza',
ISNULL(x.value('(proceeding/administrator_name)[1]', 'varchar(max)'), '') AS 'Nadzorca w postępowaniu',
ISNULL(x.value('(proceeding/administrator_function)[1]', 'varchar(max)'), '') AS 'Funkcja nadzorcy',
ISNULL(x.value('(order/order_date)[1]', 'date'), '') AS 'Data wydania postanowienia',
ISNULL(x.value('(order/expiration_period)[1]', 'varchar(20)'), '') AS 'Okres do działania (w dniach)',
ISNULL(x.value('(order/expiration_date)[1]', 'date'), '') AS 'Koniec okresu do działania',
ISNULL(x.value('(msig_entry/chapter)[1]', 'varchar(max)'), '') AS 'Rozdział MSiG',
ISNULL(x.value('(msig_entry/section)[1]', 'varchar(max)'), '') AS 'Sekcja MSiG',
ISNULL(x.value('(msig_entry/signature)[1]', 'varchar(max)'), '') AS 'Sygnatura ogłoszenia',
ISNULL(x.value('(msig_entry/issue_date)[1]', 'date'), '') AS 'Data publikacji ogłoszenia w MSiG',
ISNULL(x.value('(content/text)[1]', 'varchar(max)'), '') AS 'Treść ogłoszenia'
FROM @xml.nodes('/root/Row') AS a(x);
输出（为清楚起见缩写）
+--------------------------+-------------------------+------------------------------+
| Nazwa sądu | Nazwa wydziału | Sygnatura_List |
+--------------------------+-------------------------+------------------------------+
| Sad Rejonowy w Gliwicach | XII Wydzial Gospodarczy | XII GU 609/18,XII GUp 392/18 |
+--------------------------+-------------------------+------------------------------+
推荐阅读
- html - 需要将 css 应用于
标签
- javascript - 读取特定的 JSON 数据
- mysql - 如何获取每个来源的当前余额，然后从所有来源的总和中获取它的百分比
- c++ - 如何通过单次运行将多个输入样本提供给 C++ 中的张量流模型
- c++ - SSH 集群上的 MPI 编译问题
- android - 在 Tablayout tabLayout.addOnTabSelectedListener(new TabLayout.OnTabSelectedListener() 方法不起作用
- azure - 在 eu.luis.ai 上创建的 LUIS 应用未显示在我的 Azure 门户上
- android - 检测拨出电话何时开始播放回铃音
- r - 如何在 for 循环中的 Knitr markdown 中绘制 dygraph？knitr_child 不工作
- javascript - ASP.NET：使用右键单击触发用jQuery